Read Command Line Parameters from VBA

Recently, I needed to read a command line parameter within a VBA code (it's for an Iconics SCADA application, but I developed it with Excel VBA which is essentially the same). It doesn't sound like a big deal but there were a few not-so-obvious tricks that I thought were worth sharing.

I started with the simplest solution using a "GetCommandLineA" Windows API function from kernel32.dll. It was supposed to return a pointer to the command line string, so I assumed that its return type would be string (sounds logical to me).

' The return type of the GetCommandLineA is long
' But it is supposed to be a pointer to the string. 
' Assumming that all VBA strings are passed by pointers
' Just declare it's return type to be a string
Declare Function GetCommandLineA Lib "Kernel32" () As String
 
Sub ReadCmdLine()

   Dim strCmdLine As String ' Command line string   
   ' Read command line parameters into the string
   strCmdLine = GetCommandLineA

End Sub

Surprise - it caused exception and crashed the application. Hm, apparently VBA handles a pointer to the string differently than GetCommandLineA. Not wanting to spend time figuring out why, I decided to copy the string to VBA string using another kernel32.dll function "lstrcpynA".

' Declare the return type to be a pointer (long)
Declare Function GetCommandLineA Lib "Kernel32" () As Long
Declare Function lstrcpynA Lib "kernel32" ( _ 
   ByVal pDestination As String, ByVal pSource As Long, _
   ByVal iMaxLength As Integer) As Long
 
Sub ReadCmdLine()

   Dim pCmdLine as long     ' Pointer to the string
   Dim strCmdLine As String ' Command line string     

   pCmdLine = GetCommandLineA
   ' Copy from the pointer to VBA-Style string 
   ' 300 characters for command line seems to be enough
   lstrcpynA strCmdLine , pCmdLine, 300	   

End Sub

This worked better by no longer crashing the application, but the strCmdLine was always empty. After short Google search I found this Microsoft article. Apparently lstrcpynA function (as other DLL functions returning strings) can't change the size of the VBA-style string. In order to reserve the space for that return data, we need to fill the string with a bunch of zeros (vbNullChar).

Declare Function GetCommandLineA Lib "Kernel32" () As Long
Declare Function lstrcpynA Lib "kernel32" ( _
   ByVal pDestination As String, ByVal pSource As Long, _
   ByVal iMaxLength As Integer) As Long
 
Sub ReadCmdLine()

   Dim pCmdLine as long     ' Pointer to the string
   Dim strCmdLine As String ' Command line string   

   ' Get the pointer to the command line string
   pCmdLine = GetCommandLineA

   ' Fill the string with zeros
   ' 300 characters for command line seems to be enough
   strCmdLine = String[DollarSign](300, vbNullChar)

   ' Copy from the pointer to VBA-style string
   lstrcpynA strCmdLine , pCmdLine, Len(strCmdLine )

   ' At this point we got the string
   ' But rest of it filled with 0 characters.
   strCmdLine = Left(strCmdLine , InStr(1, strCmdLine , _
      vbNullChar) - 1)
   	   
End Sub

This code finally worked as expected, returning command line arguments.

Hopefully, this article is useful in saving someone 15 minutes of frustration.

Learn more about DMC's software and web development services.