Getting value in VBscript from VBA Excel Macro Function

Question

Fairly new to VBscript and VBA... hoping for some help and that it's an easy answer...

I'm calling a Macro / Function in Excel VBA from VBscript. The call to the Function should return a number. Using VBA debug in Excel, the function appears to work properly (in this example, it displays a value of 1), but when I call the Macro / Function and attempt to echo the value in VBscript it shows as "Empty".

How can I get the value from VBA back to VBscript?
Thanks for your help

Example of VBscript code:

Set excelOBJ = CreateObject("Excel.Application")
Set workbookOBJ = excelOBJ.Workbooks.Open("C:\variable.xlsm")

excelOBJ.Application.Visible = True
excelOBJ.DisplayAlerts = False

REM mostly for testing purposes
    Dim returnValue
    returnValue = 10

    Wscript.Echo "'returnValue' value before call to macro function = " & returnValue
    Wscript.Echo "'returnValue' TypeName before call to macro function = " & TypeName(returnValue)

returnValue = excelOBJ.Run("ThisWorkbook.getNum")

    Wscript.Echo "'returnValue' value after call to macro function = " & returnValue
    Wscript.Echo "'returnValue' TypeName after call to macro function = " & TypeName(returnValue)

excelOBJ.quit

Example of VBA in Excel:

Public Function getNum()
    getNum = 1
    Debug.Print "getNum value = " & getNum
End Function

Output:

'returnValue' value before call to macro function = 10
'returnValue' TypeName before call to macro function = Integer

REM Inside Excel VBA editor
    getNum value = 1

'returnValue' value after call to macro function = 
'returnValue' TypeName after call to macro function = Empty

Thanks, @PatricK. I tried changing the code as you described, but received the error "Cannot run the macro ''variable.xlsm'!.getNum'. The macro may not be available in this workbook or all macros may be disabled." Code: 800A03EC. after the first 2 Wscript.Echo windows. I verified that macros were enabled in the Excel spreadsheet, but still encounter the error — Nick K
– Nick K, Commented May 30, 2018 at 13:29
Is your macro in the Worksheet or in a Module? You might need to reference the Sheet name explicitly if the macro is not in a module. — dbmitch
– dbmitch, Commented May 30, 2018 at 14:43

dbmitch · Accepted Answer · 2018-05-30 14:57:21Z

5

I'd recommend moving your code to a module if not there already.

This code should be changed

returnValue = excelOBJ.Run("ThisWorkbook.getNum")

If code is in a worksheet, this might work assuming your Worksheet is "Sheet1"

returnValue = excelOBJ.Run("Sheet1.getNum")

Otherwise if it's in a Module, simply use module name

returnValue = excelOBJ.Run("Module1.getNum")

If it starts running with this change but you're not getting anything returned you can change your function to pass return value parameter ByRef and check it that way

answered May 30, 2018 at 14:57

dbmitch

5,3964 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick K Over a year ago

Thank you @dbmitch - I was hoping for a simple solution and this seems to be it. Before, the code was in "VBA Project (Variable.xlsm) > Microsoft Excel Objects > This Workbook". After creating a Module ("VBA Project (Variable.xlsm) > Modules > Module1"), placing the code in the Module, and changing the "Run" argument to "Module1.getNum", it runs properly. The value 1 and TypeName of Integer are returned and displayed. Thank you!

Collectives™ on Stack Overflow

Getting value in VBscript from VBA Excel Macro Function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related