Excel VBA bug accessing HelpFile property from macro-disabled instance?

Question

I think I've stumbled upon a bug in Excel - I'd really like to verify it with someone else though.

The bug occurs when reading the Workbook.VBProject.HelpFile property when the workbook has been opened with the opening application's .AutomationSecurity property set to ForceDisable. In that case this string property returns a (probably) malformed Unicode string, which VBA in turn displays with question marks. Running StrConv(..., vbUnicode) on it makes it readable again, but it sometimes looses the last character this way; this might indicate that the unicode string is indeed malformed or such, and that VBA therefore tries to convert it first and fails.

Steps to reproduce this behaviour:

• Create a new Excel workbook
• Go to it's VBA project (Alt-F11)
• Add a new code module and add some code to it (like e.g. Dim a As Long)
• Enter the project's properties (menu Tools... properties)
• Enter "description" as Project description and "abc.hlp" as Help file name
• Save the workbook as a .xlsb or .xlsm
• Close the workbook
• Create a new Excel workbook
• Go to it's VBA project (Alt-F11)
• Add a fresh new code module
• Paste the code below in it
• Adjust the path on the 1st line so it points to the file you created above
• Run the Test routine

The code to use:

Const csFilePath As String = "<path to your test workbook>"

Sub TestSecurity(testType As String, secondExcel As Application, security As MsoAutomationSecurity)
  Dim theWorkbook As Workbook
  secondExcel.AutomationSecurity = security
  Set theWorkbook = secondExcel.Workbooks.Open(csFilePath)
  Call MsgBox(testType & " - helpfile: " & theWorkbook.VBProject.HelpFile)
  Call MsgBox(testType & " - helpfile converted: " & StrConv(theWorkbook.VBProject.HelpFile, vbUnicode))
  Call MsgBox(testType & " - description: " & theWorkbook.VBProject.Description)
  Call theWorkbook.Close(False)
End Sub

Sub Test()
  Dim secondExcel As Excel.Application
  Set secondExcel = New Excel.Application
  Dim oldSecurity As MsoAutomationSecurity
  oldSecurity = secondExcel.AutomationSecurity

  Call TestSecurity("enabled macros", secondExcel, msoAutomationSecurityLow)
  Call TestSecurity("disabled macros", secondExcel, msoAutomationSecurityForceDisable)

  secondExcel.AutomationSecurity = oldSecurity
  Call secondExcel.Quit
  Set secondExcel = Nothing
End Sub

Conclusion when working from Excel 2010:

• .Description is always readable, no matter what (so it's not like all string properties behave this way)
• xlsb and xlsm files result in an unreadable .HelpFile only when macros are disabled
• xls files result in an unreadable .HelpFile in all cases (!)

It might be even weirder than that, since I swear I once even saw the questionmarks-version pop up in the VBE GUI when looking at such a project's properties, though I'm unable to reproduce that now.

I realize this is an edge case if ever there was one (except for the .xls treatment though), so it might just have been overlooked by Microsoft's QA department, but for my current project I have to get this working properly and consistently across Excel versions and workbook formats...

Could anyone else test this as well to verify my Excel installation isn't hosed? Preferably also with another Excel version, to see if that makes a difference?

Hopefully this won't get to be a tumbleweed like some of my other posts here :) Maybe "Tumbleweed generator" might be a nice badge to add...

UPDATE

I've expanded the list of properties to test just to see what else I could find, and of all the VBProject's properties (BuildFileName, Description, Filename, HelpContextID, HelpFile, Mode, Name, Protection and Type) only .HelpFile has this problem of being mangled when macros are off.

UPDATE 2

Porting the sample code to Word 2010 and running that exhibits exactly the same behaviour - the .HelpFile property is malformed when macros are disabled. Seems like the code responsible for this is Office-wide, probably in a shared VBA library module (as was to be expected TBH).

UPDATE 3

Just tested it on Excel 2007 and 2003, and both contain this bug as well. I haven't got an Excel XP installation to test it out on, but I can safely say that this issue already has a long history :)

Answer 1

I've messed with the underlying binary representation of the strings in question, and found out that the .HelpFile string property indeed returns a malformed string.

The BSTR representation (underwater binary representation for VB(A) strings) returned by the .HelpFile property lists the string size in the 4 bytes in front of the string, but the following content is filled with the ASCII representation and not the Unicode (UTF16) representation as VBA expects.

Parsing the content of the BSTR returned and deciding for ourselves which format is most likely used fixes this issue in some circumstances. Another issue is unfortunately at play here as well: it only works for even-length strings... Odd-length strings get their last character chopped off, their BSTR size is reported one short, and the ASCII representation just doesn't include the last character either... In that case, the string cannot be recovered fully.

The following code is the example code in the question augmented with this fix. The same usage instructions apply to it as for the original sample code. The RecoverString function performs the needed magic to, well, recover the string ;) DumpMem returns a 50-byte memory dump of the string you pass to it; use this one to see how the memory is layed out exactly for the passed-in string.

Const csFilePath As String = "<path to your test workbook>"

Private Declare Sub CopyMemoryByte Lib "kernel32" Alias "RtlMoveMemory" (ByRef Destination As Byte, ByVal Source As Long, ByVal Length As Integer)
Private Declare Sub CopyMemoryWord Lib "kernel32" Alias "RtlMoveMemory" (ByRef Destination As Integer, ByVal Source As Long, ByVal Length As Integer)
Private Declare Sub CopyMemoryDWord Lib "kernel32" Alias "RtlMoveMemory" (ByRef Destination As Long, ByVal Source As Long, ByVal Length As Integer)

Function DumpMem(text As String) As String
  Dim textAddress As LongPtr
  textAddress = StrPtr(text)
  Dim dump As String
  Dim offset As Long
  For offset = -4 To 50
    Dim nextByte As Byte
    Call CopyMemoryByte(nextByte, textAddress + offset, 1)
    dump = dump & Right("00" & Hex(nextByte), 2) & " "
  Next
  DumpMem = dump
End Function

Function RecoverString(text As String) As String
  Dim textAddress As LongPtr
  textAddress = StrPtr(text)
  If textAddress <> 0 Then
    Dim textSize As Long
    Call CopyMemoryDWord(textSize, textAddress - 4, 4)
    Dim recovered As String
    Dim foundNulls As Boolean
    foundNulls = False
    Dim offset As Long
    For offset = 0 To textSize - 1
      Dim nextByte As Byte
      Call CopyMemoryByte(nextByte, textAddress + offset, 1)
      recovered = recovered & Chr(CLng(nextByte) + IIf(nextByte < 0, &H80, 0))
      If nextByte = 0 Then
        foundNulls = True
      End If
    Next
    Dim isNotUnicode As Boolean
    isNotUnicode = isNotUnicode Mod 2 = 1
    If foundNulls And Not isNotUnicode Then
      recovered = ""
      For offset = 0 To textSize - 1 Step 2
        Dim nextWord As Integer
        Call CopyMemoryWord(nextWord, textAddress + offset, 2)
        recovered = recovered & ChrW(CLng(nextWord) + IIf(nextWord < 0, &H8000, 0))
      Next
    End If
  End If
  RecoverString = recovered
End Function

Sub TestSecurity(testType As String, secondExcel As Application, security As MsoAutomationSecurity)
  Dim theWorkbook As Workbook
  secondExcel.AutomationSecurity = security
  Set theWorkbook = secondExcel.Workbooks.Open(csFilePath)
  Call MsgBox(testType & " - helpfile: " & theWorkbook.VBProject.HelpFile & " - " & RecoverString(theWorkbook.VBProject.HelpFile))
  Call MsgBox(testType & " - description: " & theWorkbook.VBProject.Description & " - " & RecoverString(theWorkbook.VBProject.Description))
  Call theWorkbook.Close(False)
End Sub

Sub Test()
  Dim secondExcel As Excel.Application
  Set secondExcel = New Excel.Application
  Dim oldSecurity As MsoAutomationSecurity
  oldSecurity = secondExcel.AutomationSecurity

  Call TestSecurity("disabled macros", secondExcel, msoAutomationSecurityForceDisable)
  Call TestSecurity("enabled macros", secondExcel, msoAutomationSecurityLow)

  secondExcel.AutomationSecurity = oldSecurity
  Call secondExcel.Quit
  Set secondExcel = Nothing
End Sub