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).
code1 2 3 4 5 6 7 8 9 10 11 12 13 | Declare Function GetCommandLineA Lib "Kernel32" () As String
Sub ReadCmdLine()
Dim strCmdLine As 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".
code1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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
Dim strCmdLine As String
pCmdLine = GetCommandLineA
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).
code1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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
Dim strCmdLine As String
pCmdLine = GetCommandLineA
strCmdLine = String[DollarSign](300, vbNullChar)
lstrcpynA strCmdLine , pCmdLine, Len(strCmdLine )
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.