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!