Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Excel FUNCTION MACROS Programmatically from VB
Hi Lenny,
Here's an example of how this works: ------------- In Book1.xls ------------- Public Function MyFunc(ByVal lArg As Long, ByVal szArg As String) As String MyFunc = "Arguments Passed:" & vbLf & _ "lArg = " & CStr(lArg) & vbLf & _ "szArg = " & szArg End Function ---------------- In VB6 Project ---------------- ''' You must set a reference to the Excel object library to run this. Sub Main() Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim szResult As String Set xlApp = New Excel.Application Set xlBook = xlApp.Workbooks.Open("E:\Book1.xls") szResult = xlApp.Run("Book1.xls!MyFunc", 10, "Some String") MsgBox szResult xlBook.Close False Set xlBook = Nothing xlApp.Quit Set xlApp = Nothing End Sub -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Leonard Jonas" wrote in message ... I have found a lot of literature online about calling excel 2000 macros programmatically from VB6:(xlApp.Run "Personal.xls!MyMacro", Arg1, Arg2) but I haven't been able to find any information about calling FUNCTION MACROS which return a value. The above syntax (result = xlApp.Run"Personal.xls!MyFunctionMacro", Arg1, Arg2) doesn't work when I want to call a function macro and have a value returned to VB. I also tried to get around this (with no luck) by passing arguments ByRef to an Excel macro and updating them within the macro. This works when the sub is called from within VBA, but if I'm in VB, the variable does not reflect the changes that take place in VBA. For example: (In Excel -- Personal.xls) Sub ChangeX(x ByRef as integer) x = 50 End Sub (In VB) Sub MyProgram() dim x as integer x = 0 xlApp.Run "Personal.xls!ChangeX" x MsgBox x 'here x is still 0 :( End Sub If you know how to make either of these techniques work, please let me know Thanks! Lenny *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CALLING MACROS FROM A NEW WORKSHEET | Excel Discussion (Misc queries) | |||
Calling macros in diff worksheet | Excel Discussion (Misc queries) | |||
calling macros from worksheet to another | Excel Discussion (Misc queries) | |||
Disable macros on a programmatically opening .xls file | Excel Discussion (Misc queries) | |||
Calling macros in another workbook | Excel Worksheet Functions |