Accessing Excel User Function from Word

[BillCPA]

From Word VBA, I'm opening and processing some stuff in an Excel Spreadsheet.
Is there a way to access a User (not Worksheet) Function in the spreadsheet
from Word VBA?

 

[Helmut Weber]

Hi Bill,

From Word VBA, I'm opening and processing some stuff in an Excel Spreadsheet.
Is there a way to access a User (not Worksheet) Function in the spreadsheet
from Word VBA?

like that:
In Word:

Sub Test5a()
Dim oExc As Object
Set oExc = GetObject(, "Excel.Application")
With oExc
MsgBox .Run("MyTest", 3.412)
End With
End Sub

In Excel in the VBA-project, in modules, module1:

Public Function MyTest(s As String) As Single
MyTest = CSng(s)
End Function

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[BillCPA]

That did it. I do have a couple of questions, tho.

I have been using Set wbxl = CreateObject("Excel.Application") for other
processing, and it has worked fine. In fact, I tried running your code using
CreateObject first (which gave me an error) before I changed it to GetObject.
What is the difference – why does GetObject work but CreateObject does not
in this instance?

And how would I access an Excel WorksheetFunction from Word code? I tried

With wbxl
zz = .Application.WorksheetFunction.Match(SrchWord(xx), CellSel, 0)
End With

but it says it cannot access the Match function.

Does it need to be done a different way?

 

[Helmut Weber]

Hi BillCPA,

That did it.

I have been using Set wbxl = CreateObject("Excel.Application") for other
processing, and it has worked fine. In fact, I tried running your code using
CreateObject first (which gave me an error) before I changed it to GetObject.
What is the difference – why does GetObject work but CreateObject does not
in this instance?

I don't know.

This workes for me:
Sub Makro1()
Dim answer As String
Dim oExl As Object
Set oExl = CreateObject("Excel.application")
oExl.Workbooks.Open "c:\test\excel\book1.xls"
Dim Myrange As Object
Set Myrange = oExl.Worksheets("Sheet1").Range("A1:E3")
With oExl
answer = oExl.WorksheetFunction.Min(Myrange)
MsgBox answer
End With
End Sub

As well, this one worked:

Sub Makro1()
Dim answer As String
Dim oExl As Object
Set oExl = GetObject(, "Excel.application")
Dim Myrange As Object
Set Myrange = oExl.Worksheets("Sheet1").Range("A1:E3")
With oExl
answer = oExl.WorksheetFunction.Min(Myrange)
MsgBox answer
End With
End Sub

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP