Passing arguments from command line in excel 2010

Question

I have found several snippet of code out there that allows me to pass arguments into excel from command line.

The code below is placed in a new module called parameters:

Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)

Public CmdLineToStr() As String
'
' Returns the command line in the call to Excel
'
Dim Buffer() As Byte
Dim StrLen As Long
Dim CmdPtr As Long

CmdPtr = GetCommandLineW()
If CmdPtr > 0 Then
  StrLen = lstrlenW(CmdPtr) * 2
  If StrLen > 0 Then
    ReDim Buffer(0 To (StrLen - 1)) As Byte
    CopyMemory Buffer(0), ByVal CmdPtr, StrLen
    CmdLineToStr = Buffer
  End If
End If

End Sub

and then in thisworkbook i call this code

Sub workBook_open()
    MsgBox Parameters.CmdLineToStr
End Sub

It fails with the GetCommandLine function, so is the error due to problems with linking the dll library or is this due to the fact that i have some macros stored in personal.xlsb?

I call the excel sheet from the command line with this line: C:\Users\kim\Desktop>start excel Parameters.xlsm /e/nmbnmbmnb

and i get this error :

Outside procedure

Answer 1

The corrected, tested procedure: (The version above had two mistakes.)

Option Explicit

Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
    (MyDest As Any, MySource As Any, ByVal MySize As Long)

Public Function CmdLineToStr() As String
'Returns the command line used to open Excel
    Dim Buffer() As Byte, StrLen As Long, CmdPtr As Long
    CmdPtr = GetCommandLine()
    If CmdPtr > 0 Then
      StrLen = lstrlenW(CmdPtr) * 2
      If StrLen > 0 Then
        ReDim Buffer(0 To (StrLen - 1)) As Byte
        CopyMemory Buffer(0), ByVal CmdPtr, StrLen
        CmdLineToStr = Buffer
      End If
    End If
End Function

...place the code in a new, regular module, then call CmdLineToStr to get the command line that was used to open Excel, such as:

excel.exe /x "C:\Users\someone\Desktop\myTest.xlsm"

Answer 2

Change Public CmdLineToStr() As String

to Public Function CmdLineToStr() As String

Public CmdLineToStr() As String is not a procedure, you need to put either Sub or Function so it is a procedure. Hence the error message "Invalid outside procedure" because you were precisely outside a procedure.