Command()

Simon Goodman

New member
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?
 
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:
Hopefully, the ideas will overcome your situation; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy
 
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.
 
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:
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

Command function and Workbook_Open event.png

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:
Code:
$excel = New-Object -comobject Excel.Application
$FilePath = "C:\Users\User\Downloads\Excel FileName.xlsm"
$workbook = $excel.Workbooks.Open($FilePath)

Output of running PowerShell code.gif

I hope you have found the idea helpful.

Regards
Lutfor Rahman Shimanto
ExcelDemy
 
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.
 

Online statistics

Members online
0
Guests online
37
Total visitors
37

Forum statistics

Threads
337
Messages
1,470
Members
624
Latest member
duytoi
Top