Command()

[Simon Goodman]

I am trying to pass a parameter to an Excel VBA macro, to be accessed by the first statement in the auto-open macro of the workbook. Whatever happens I always get a null result from the function Command(). No error is recorded. How can I correct this or investigate further?

 

[Lutfor Rahman Shimanto]

I am trying to pass a parameter to an Excel VBA macro, to be accessed by the first statement in the auto-open macro of the workbook. Whatever happens I always get a null result from the function Command(). No error is recorded. How can I correct this or investigate further?

Hello Simon Goodman

Thanks for sharing your questions. There are a couple of reasons why you might be getting a null value from your Command() function despite no errors being recorded.

In VBA, variables declared within a Sub or Function are local to that procedure. So, it won't be available if you set the parameter in a separate macro and try to access it in the auto-open macro.

Declare the variable holding the parameter as a global variable (outside any Sub or Function). Pass the parameter as an argument to the Command() function itself. Use a public variable declared within a module accessible by both macros.

VBA offers two ways to pass arguments: ByVal (default) and ByRef. If the Command function returns a value, ensure it's declared with ByVal to return a copy to the auto-open macro.

To learn more, you can follow these articles:

• How to Make VBA Function with Arguments in Excel
• Excel VBA to Call Sub with Parameters
• Excel VBA Function to Return Multiple Values

Hopefully, the ideas will overcome your situation; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

 

[Simon Goodman]

I don't think I explained this situation clearly enough. The command function is in the auto-open macro, as contained in ThisWorkbook section of the VBAProject for Excel workbook I am opening. I have tried opening this workbook using a Powershell script, another workbook using the Shell function, and the command line itself. In all cases, Command() returns a null result, whether the command itself contains a parameter or not. I am expecting the result to at least show "Excel FileName xlsm", or whatever was used to start the Excel process.

 

[Lutfor Rahman Shimanto]

I don't think I explained this situation clearly enough. The command function is in the auto-open macro, as contained in ThisWorkbook section of the VBAProject for Excel workbook I am opening. I have tried opening this workbook using a Powershell script, another workbook using the Shell function, and the command line itself. In all cases, Command() returns a null result, whether the command itself contains a parameter or not. I am expecting the result to at least show "Excel FileName xlsm", or whatever was used to start the Excel process.

Hello Simon Goodman

Thank you very much for explaining further. After reading your explanation, I understand you have a user-defined function named Command, which is used in an Excel VBA Workbook_Open event. You have tried opening the workbook using various methods, including a PowerShell script, another workbook using the Shell function, and the command line. Still, the Command() function does not always return the expected result.

To demonstrate the situation, I have created an Excel file named "Excel FileName.xlsm". Within this Excel file, I have written an Excel VBA Workbook_Open event and used a user-defined function named Command.

Excel VBA Code:

Private Sub Workbook_Open()
    Dim userInput As String
    
    userInput = InputBox("Please enter a value:")
    MsgBox Command(userInput)
End Sub

Function Command(inputValue As String) As Integer
    Command = 2041
End Function

​

Later, I successfully opened the Excel file using Windows PowerShell ISE. Consequently, the Workbook_Open was triggered, and the Command function was used, returning the intended value.

Code within PowerShell ISE:

$excel = New-Object -comobject Excel.Application
$FilePath = "C:\Users\User\Downloads\Excel FileName.xlsm"
$workbook = $excel.Workbooks.Open($FilePath)

​

I hope you have found the idea helpful.

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[Simon Goodman]

Thank you for your helpful suggestions, Lutfor. I'm not in work until Wednesday, so will try when I'm there. I'm in PDT time zone.

 

[Lutfor Rahman Shimanto]

Thank you for your helpful suggestions, Lutfor. I'm not in work until Wednesday, so will try when I'm there. I'm in PDT time zone.

Dear Simon Goodman. You are most welcome.

 

[Simon Goodman]

I thought Command() was an Excel command? I certainly haven't defined it. If this is the case, I need a simple way of passing a string value from one workbook to another, where the 2nd workbook is running as a separate invocation of Excel.