58

So here's the bug: In a 64-Bit VBA host (for example Access 365 64-bit or Excel 2016 64-bit) create a class module SomeClass:

' this needs to be here to trigger the bug: 
Private Sub Class_Terminate()
End Sub

and then some module Test:

Function ReturnFalse(o As Object) As Boolean
    ReturnFalse = False
End Function

Sub Test()
    Debug.Print ReturnFalse(New SomeClass)
    If ReturnFalse(New SomeClass) Then
        Debug.Print True
    Else
        Debug.Print False
    End If    
End Sub

Now, if you are using a 32-bit VBA host and run "Test" in the immediate window, the the expected result shows up:

False
False

However, if you are using a 64-bit VBA host, then this comes out:

False
True

Except, when you remove or rename the Class_Terminate() sub, in which case the correct output appears.

I've tracked the bug down to this minimal example. Apparently, the problem seems to be, that using a temporary object (new SomeClass here) breaks the evaluation of the IF condition somehow, making it appear that the value of the condition is True not matter what.

OK, that's a serious bug, because the 64 bit compiler is bonkers and all IF are in trouble.

All IF? what about WHILE?

While ReturnFalse(New SomeClass)
 Debug.Print "Oh no!"
Wend

Yes, WHILE is also in trouble, because this prints "Oh No!" in a loop.

That's a lot of trouble and I could reproduce it everywhere:

  • Microsoft® Access® für Microsoft 365 MSO (16.0.14026.20294) 64-Bit
  • Microsoft Access 2016 MSO (16.0.9029.2167) 64-Bit
  • Microsoft Access 2013 (15.0.4420.1017) MSO (15.0.4420.1017) 64-Bit

..And also in Excel of course.

Summary: I could find this bug in all versions of Office I have, starting from 2013 and it is probably at least 8 years old.

Ok, did this bug affect other people before? Yes:

This post last year:

VBA takes wrong branch at If-statement - severe compiler bug?

This post in excel.uservoice.com (which apparently is Microsoft's user suggestion box or something) from October 2018:

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/35735881-fix-inlined-member-calls-on-user-objects-on-64-bi

Allright, so let's file a bug report.

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac-mso_mac2016/how-do-i-report-vba-bugs/bb4e5dea-9996-4232-9b5b-7dd57f76736c

If, after testing with others, the code fails and it really shouldn't, you can report the issue directly to Microsoft using the Smile button in Excel.

What?

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2016/excel-2016-vba-bug/b799dfc2-7cef-417d-8a41-96661a360c43

  1. Open Excel > File > Feedback > Send a Frown
  2. Through Uservoice - Click the following link to see the feedback of others and to provide feedback - https://excel.uservoice.com/

This is not a suggestion for a new icon color scheme. It is an 8 year old bug, which makes Access apps and Excel sheets with macros compute wrong answers (and also it blocks a migration to Office 64 here, because we can not get our code out).

Now here are my question:

UPDATE: x-posted to

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_2019/invalid-code-by-vba-64-bit-compiler/b91f984a-194c-4453-b8c5-02881afaf83b

UPDATE 2:

I had the chance to try the code on a Office 365 for Mac installation (where Win64 is defined as true) and the bug does not show up there. So it's a PC thing right now.

UPDATE 3:

Post made it to HN and The Register:

https://www.theregister.com/2021/08/19/64_bit_microsoft_vba_bug/ https://news.ycombinator.com/item?id=28188251

UPDATE 4:

Just checked today (2021-11-15) against Office 365 and the bug ist gone now! Looks like somebody payed attention. I can't figure out however, which of the gazillion cumulative updates I received this year did the trick and don't know yet, if the other Office versions are fixed too.

Nordic Mainframe
  • 28,058
  • 10
  • 66
  • 83
  • 4
    Possibly related: https://stackoverflow.com/q/63848617/11683 – GSerg Jun 18 '21 at 11:28
  • Yes, I've seen this too. Also a good one, overwrites stack memory and such. – Nordic Mainframe Jun 18 '21 at 11:36
  • 10
    Honestly, there are plenty of bugs in Office programs and there's no reliable way to get them fixed. I've run into several ([Hard crash when sorting a form bound to a recordset using parameters](https://stackoverflow.com/q/48886278/7296893), [hard crash when an object has a reference to a closed form and this object gets removed by the garbage collector](https://stackoverflow.com/q/53011097/7296893), [@@identity being used instead of scope_identity causing problems](https://stackoverflow.com/q/48644195/7296893)). There's no straightforward way to get them fixed. – Erik A Jun 18 '21 at 11:47
  • @GSerg not even close. – SSlinky Jun 18 '21 at 11:57
  • 2
    I voted up your new uservoice by 3 points. Everyone should do so with mine also (the one you posted above from 2018)! – AHeyne Jun 18 '21 at 14:06
  • does forcing evaluation like debug.print (ReturnFalse(New SomeClass) ) work? I wonder if this is debug.print wanting a string or type conversion? Or does debug.print not matter here? – Albert D. Kallal Jun 18 '21 at 18:01
  • 1
    Does not tmatter. ReturnFalse does not work, if it is called in a While oder IF statement. For example, assigning the result to a variable and using this in the IF or WHILE works. – Nordic Mainframe Jun 18 '21 at 18:17
  • 6
    [Uservoice is dead](https://support.microsoft.com/en-us/topic/uservoice-pages-430e1a78-e016-472a-a10f-dc2a3df3450a) – HackSlash Aug 03 '21 at 16:57
  • 4
    Congrats, this post made it to [The Register](https://www.theregister.com/2021/08/19/64_bit_microsoft_vba_bug/)! – Mathieu Guindon Aug 19 '21 at 12:40
  • 2
    @NordicMainframe Just checked it and in Office 2019 Version 2110 Build 14527.20276 it is fixed too! So it seems they fixed all versions. – Pᴇʜ Nov 15 '21 at 14:07

2 Answers2

30
Sub Test()
    Debug.Print ReturnFalse(New SomeClass)

    If ReturnFalse(New SomeClass) Then
        Debug.Print True
    Else
        Debug.Print False
    End If
    
    If True = ReturnFalse(New SomeClass) Then
        Debug.Print True
    Else
        Debug.Print False
    End If
End Sub

Returns

False
True
False

So If True = ReturnFalse(New SomeClass) Then fixes it

And for the loop this fixes it too

Do While True = ReturnFalse(New SomeClass)
    Debug.Print "Oh no!"
    Exit Do
Loop

Highly recommended to comment every usage of the workaround above so nobody removes that True = in the future (eg. because he develops in 32 bit and does not even run into the issue).

' Don't remove `True =` due to a compiler bug in x64 the condition would always be true. 
' See https://stackoverflow.com/questions/68034271/how-can-i-get-this-8-year-old-vba-64-bit-compiler-bug-fixed
If True = ReturnFalse(New SomeClass) Then

Even If ReturnFalse(New SomeClass) And False = True Then would be True with this bug.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 11
    That's hideous. It's good to have a workaround but if I saw that in code somewhere I would immediately remove the `True = ` part, not knowing that I have just introduced a bug. That's a dev landmine. – HackSlash Aug 03 '21 at 16:52
  • 3
    @HackSlash True, a comment line on each usage of `True =` with a note to the bug is highly recommended. • Hence a workaround is probably the closest you get to solve this until Microsoft fixes the bug internally. – Pᴇʜ Aug 04 '21 at 09:09
  • 2
    Problem is, should I invest in a development platform that has no defined process for users to report bugs, get bugs fixed or even get a list of open bugs to work around. – Nordic Mainframe Aug 05 '21 at 11:22
  • 3
    @NordicMainframe Well you could ask yourself that since the very beginning of VBA. MS never put much love into it, it was always messy, sloppy and inaccurate, development is stuck since years (they only do what is really necessary to keep it alive). Maybe have a look into [Office-js](https://github.com/OfficeDev/office-js-docs-reference) wich might be the successor of VBA some time (or not). • Even if I hate VBA there might be no alternative sometimes and it depends on what you are developing. But well this is a completely off-topic discussion. I guess MS won't fix this in reasonable time. – Pᴇʜ Aug 05 '21 at 11:47
  • 2
    I am awarding you 1000 Internet points for recommending that every usage of the workaround is commented. Otherwise, it's even possible that the author of the code will forget 6 months later and remove it. – Drew Aug 15 '21 at 14:11
  • 1
    So, pretty much like my answer here: [https://stackoverflow.com/a/65259128/8488913](https://stackoverflow.com/a/65259128/8488913) although I used ```And``` and ```Or``` instead of equal. – Cristian Buse Aug 19 '21 at 14:28
  • 1
    Which BTW is linked in the OP's question too so I really don't see why this answer is here as is does not address the OP's question (this is not a fix, is a workaround) and moreover is duplicating stuff that was already linked in the question. – Cristian Buse Aug 19 '21 at 14:37
  • 1
    @CristianBuse Sorry, I haven's seen your answer (would have marked it as duplicate then). Asking here for a fix is pretty useless because only Microsoft can fix that compiler bug. So until Microsoft fixed it all anwsers can only be workarounds (obviously). – Pᴇʜ Aug 20 '21 at 06:43
1

You can't do much more than what you have done already, unless you want to reach out to the tech devs directly/individually and risk getting on the annoying list, which would not necessarily help the chances of getting it actually fixed.

Uservoice is headed for the scrap heap but it does still get noticed and used by the product dev team, including right now in 2021.

VBA is not the current focus for programmability development, so I do not have a large amount of confidence that this will make it to the priority queue.

ed2
  • 1,457
  • 1
  • 9
  • 26
  • 6
    “VBA is not the current focus for programmability development” - it should be, for Microsoft’s sake: VBA is probably the only thing keeping paying business customers still wedded to MS Office. But VBA’s _staleness_ puts the _cool kids_ off from learning and instead they do Python or JS, which can’t do what VBA can do - so then they switch to Google Sheets and Firebase instead of Excel and Access. – Dai Aug 15 '21 at 14:22
  • I wasn't saying it shouldn't be the focus, just observing that it isn't. In fact, I agree with many of your points. Hopefully when OfficeScripts comes to desktop Excel it will bring VBA integration, to assist transition from the corporate user base (which I suspect is under-represented in the telemetry). Meanwhile the web-based Excel is improving rapidly but still very far behind desktop Excel, so as you say, the advantage over inferior substitutes is far thinner than with VBA-enabled desktop Excel. VBA has changed very little in two decades, which in some ways is a very useful feature itself! – ed2 Aug 15 '21 at 23:38