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
which is only one dot off what you wanted.
with app.worksheetfunction
answer = .max()...
end with
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
Sunday, 17th June, 2007 at 9:38 pm |
Hi Simon,
I sympthaise with the ‘struggling to find time’ comment (I know, what am I doing here). As a response to the first question, here’s a link to Chip Pearson’s site who has some sample code of manipulating Excel VBE including adding code.
http://www.cpearson.com/excel/vbe.htm
Cheers – Marcus
7:35 AEST, Now: 6°C Max: 11°C