couple of questions and half answers

I’ve had a few email questions recently, so here they are with my ‘answers’. Please comment if you have anything to add or correct. I generally point people to Excel-l, but I’m struggling to find time to participate at the mo. 

denibart asks:

Is it posible to copy from worksheet to codemodul data…

case….

Let say this is on xls sheet

Sub Export()
    
Dim myRecord As Range
Dim sOut As String
Open "c:\source.txt" For Output As #1
For Each myRecord In Range("A2:A10")
With myRecord
sOut = sOut & myRecord.Text
Print #1, Mid(sOut, 1)
sOut = Empty
End With
Next myRecord
Close #1
MsgBox "File exported to: c:\Test.txt", vbOKOnly
    
End Sub

And I want to copy this with vbamacros on modExport..can this be done

So when I start excel workbook_open ….copy range(“a1:a18”) to modExport, is
this possible.

____________________________________________

If I understand right you want to copy the sheet with the code?

If so just right click the worksheets tab and copy, that will take any code in the worksheet class module.

If you want to pass the address you want to export to the txt file then change your export routine to take a string or range parameter as in

sub Export(theRangeToExport as range)

…

 then change this line:

For Each myRecord In Range(“A2:A10”)

to:

For Each myRecord In theRangeToExport

Then call it:

export ActiveSheet.range(“A2:A10”)

hth_____________________________________________

John asks:

Hi,

    I recently posted a query on the Ozgrid.com forum about “How to access XLL add-in functions from VBA code module”. I was wondering if you could clear up some resulting questions about accessing worksheet and add-in functions.

Why when accessing functions such as AVERAGE or MAX, I use the prefix “Application.WorksheetFunction” to access it?

Why when accessing Add-in functions(not sure if this is true for all add-ins or just my particular one), I use the prefix “Application.Run” to access it?

Why is it possible to access the function NOW() directly?

I have gone through the steps to ensure the Add-in is registered using “Application.RegisterXLL”. and displaying the registered functions using “Application.RegisteredFunctions”.

I am curious to find out if it is possible to access all function directly without the prefix(ie Answer = Function(Arg1,Arg2,…)).

Kind regards

John

1. AVERAGE and MAX were redefined into the WorksheetFunction object in xl97 or 2k prior to that (and probably still for compatibility) you just went application.max … I suspect they did it because the app object was getting too big and unwieldy.

2. xll addin functions are just registered with native Excel same as xlm macros. VBA has no knowledge of these. App.run is how you drop out of VBA into native Excel. Personally I always use declares to access xll stuff, but I couldn’t say which approach is better. I would never call a VBA function using app.run though as it breaks the VB call stack and errors don’t bubble up right.

Using declares will get you your ‘answer = func(….). ‘ style. Func will be whatever you declare your xll function will be known as in VBA as in:

Declare Function theVBAName Lib “thexll.XLL” Alias “theXLLName” (ByVal int as long) As Long

for your excel ones you could use
with app.worksheetfunction
  answer = .max()...
end with
which is only one dot off what you wanted.
3. There are 2 now()’s the Excel one and the VB one. In VBA you always call the VB one, you don’t get access to the intrinsic Excel one as there is no point now() is part of the VB language. I’m sure you could get at it if you wanted to, but I have no idea why you would.

hth

Please chip in with your own thoughts, especially if I’ve steered them wrong

cheers

Simon

Advertisements