search

rubberduck-vba / Rubberduck

Every programmer needs a rubberduck. COM add-in for the VBA & VB6 IDE (VBE).
https://rubberduckvba.com
GNU General Public License v3.0
1.92k stars 301 forks source link

Identify things only available in 64bit, like LongLong... #5841

Open jim-oflaherty-jr-qalocate-com opened 3 years ago

jim-oflaherty-jr-qalocate-com commented 3 years ago

What It would be nice to see 64bit VBA only things, like LongLong, highlighted with an inspection. Actually, it would be great to show anything that is 32bit VBA only things (if any exist).

Why The intention is to call out that using the feature will result in support complications later if the code finds its way into the "wrong" environment.

Example This code should trigger the inspection:

Private Const SECOND_AS_MILLIS As LongLong = 1000^

QuickFixes Should Rubberduck offer one or more quickfix(es) for this inspection?

I am too new to VBA to understand how to structure any mitigations. I suspect that just calling out the 32bit-vs-64bit exclusivity is value enough. And my Googling has not turned up how I can use a 64bit value in 32bit VBA. If there is a simple way to do that, then somehow that would likely be related to the quickfix.

Resources Each inspection needs a number of resource strings - please provide a suggestion here:

retailcoder commented 3 years ago

I'd be inclined to implement this inspection by flagging bitness-specific code that isn't conditionally compiled, however doing that would be particularly difficult because parse trees only include the "live" code (for very valid reasons), so flagging all bitness-specific code seems a fair compromise.

The inspection should be flagging:

jim-oflaherty-jr-qalocate-com commented 3 years ago

...flagging all bitness-specific code seems a fair compromise.

I agree with the compromise. And then if there is some sort of meta-AST pattern-based enhancement that can elevate this later to capture the conditional compilation, it can be changed then.

Is there anything else like LongLong for 64bit? And while it probably ought to be a separate inspection, what about VBA between versions of Office itself? Again, I am only 3 months old in deep diving the VBA software engineering experience (my background is over two decades in Java/Scala, OOP, FP, and OOP+FP).

The inspection should be flagging:

  • Any use of the LongLong integer data type (fix to Double?), only available in 64-bit hosts

I don't know how to fix the LongLong problem yet. If a Double is 64bits, and there isn't an Integer (or Long) style solution. Is there a well-known library that enables dealing with integer bit sizes greater than 32 which works in VBA 32bit? I'm considering building one from scratch along the lines of BitInt from Java. I have several use cases that require a minimum of a 64bit integer. And give my Googling, it appears that there is some need for it in the market.

  • Any .ocx ActiveX controls in use (right?), only available in 32-bit hosts.

This is fantastic as I had no idea! Are there any other limits similar to this in moving from 32bit to 64bit? If so, then this would definitely be a HUGE help to people like me.

For me, the real challenge is discovering there are really two very similar VBAs; 32bit and 64bit. And there are even more Excel "versions", 2007, 2013, 2019, 365, etc. And all of them are still at significant use levels in my target clients. This forms a 2 x 4 matrix of possible special case domains. And I haven't found an effective resource to summarize the deltas such that I know what to depend upon and what to avoid. It appears that it requires acquiring tribal knowledge by experiencing failure and implicit gleaning of undesirable effects from all sorts of hackery captured in StackOverflow entries and VBA code forums.

For example, I leveraged the hell out of the LET command in my formulas. It was my client who let me know it didn't work in her Excel 2016. It turns out that it was introduced in Excel 365. ARGH! That one incompatibility was hours of redesign and re-implementation work. I'm now in constant fear of hitting another one of these limits as I continue developing my Excel+VBA solution.

jim-oflaherty-jr-qalocate-com commented 3 years ago

Is there a well-known library that enables dealing with integer bit sizes greater than 32 which works in VBA 32bit?

I just found this one based on String as opposed to an object internally using an Array of Long. My intuition says using String would be at least an order of magnitude slower than using an Array of Long. Any thoughts on this would be appreciated. https://cosxoverx.livejournal.com/47220.html

Greedquest commented 3 years ago

@jim-oflaherty-jr-qalocate-com You can save a spreadsheet in compatibility mode to target any version of excel and flag potential formulae incompatibilities (as well as other things like Tables or formatting that is not compatible). Search Excel functions (alphabetical) for an up-to-date list of formulae and which Excel version they were introduced.

VBA has 2 important versions; VBA6 (and earlier - not to be confused with VB6) can be found on old systems, and VBA7 which is the latest and final VBA version and which supports 64 bit code. #IF VBA7 = 1 & #IF VBA6 = 1 can be used to distinguish.

Regarding that BigInt library - strings in VBA are pretty bare-metal; implemented as Pascal-strings which are contiguous char arrays with a few bytes to encode their length. They are slow to manipulate as they are always copied but I think if you are careful to modify in-place you can get good performance (the example you linked looks a bit inefficient). IIRC python's int implementation uses a 32-bit integer as its unit building block for big-ints so an array of Longs is probably what I would go for in VBA.

@retailcoder another use case that's tripped me up since migrating to 64 bit office is referenced libraries that don't exist - e.g. Private Declare PtrSafe Function InterlockedIncrement Lib "kernel32" (ByRef Addend As Long) As Long that function is no longer exported by kernel32 in 64 bit Windows - I've also found certain CreateObject() calls fail. These are quite clear error messages at runtime, but identifying such hazards when programming cross-compatible VBA in 32-bit hosts would be hugely useful. Basically if RD can tell me "If you typed this in 64-bit Excel it wouldn't ever work" so I don't have to test myself.

jim-oflaherty-jr-qalocate-com commented 3 years ago

You can save a spreadsheet in compatibility mode to target any version of excel and flag potential formulae incompatibilities (as well as other things like Tables or formatting that is not compatible)

I didn't follow your guidance. In the "Save As" dialog, I found only two file types of ".xls". Am I looking in the wrong place? And I didn't see how to distinguish between 32bit and 64bit, either. My gut is that there is some internalized tribal knowledge where your experience has encoded the 2x4 matrix I described.

... Search Excel functions (alphabetical) for an up-to-date list of formulae and which Excel version they were introduced.

Tysvm for the link. That is far more helpful than my current error-prone way. It is a VERY long list. It sure would be nice if they had some sort of filter/toggle mechanism at the top so I can narrow the domain down to what I need to focus on. If I had time, I would write a quick screen scraper and turn that into an Excel spreadsheet with EXACTLY that functionality, LOL.

VBA has 2 important versions; VBA6 (and earlier - not to be confused with VB6) can be found on old systems, and VBA7 which is the latest and final VBA version and which supports 64 bit code. #IF VBA7 = 1 & #IF VBA6 = 1 can be used to distinguish.

So, is there a 64-bit version of VBA6? And is there a 32-bit version of VBA7?

Regarding that BigInt library - ... slow to manipulate ... IIRC python's int implementation uses a 32-bit integer as its unit building block for big-ints so an array of Longs is probably what I would go for in VBA.

Yeah. I will look at the chars in place mutation approach. I tend to agree with you, though, about Python's approach. Java's implementation also uses an internal array of Long (which in Java is 64-bit). That's the direction I will most likely go. Tysvm for your feedback.

Greedquest commented 3 years ago

@jim-oflaherty-jr-qalocate-com Sorry I was thinking of the compatibility checker. As far as I know 64-bit and 32-bit Excel/Office are basically the same (here's all I could find), you might want to search on Stack Overflow for info on that (a GitHub issue isn't the best place to discuss:).

For VBA, I believe VBA(<=6) are 32 bit only (16 bit even for earliest ones!, although I don't expect you'll encounter this in the wild). Meanwhile VBA7 can be run in a 32 or 64 bit host like Excel and has some special features to allow you to write code which works in both.

jim-oflaherty-jr-qalocate-com commented 3 years ago

@Greedquest Tysvm, again, for all your assistance.

You can save a spreadsheet in compatibility mode to target any version of excel and flag potential formulae incompatibilities ... Search Excel functions (alphabetical) for an up-to-date list of formulae and which Excel version they were introduced.

I finally had enough frustration trying to figure out Office/Excel versions, 32-vs-64 bitness, and what function were available where, I spent the time to create a Google Sheet capturing everything as I currently understand. It enables me to quickly narrow my focus/view while not requiring I internalize a huge quantity of tribal knowledge.

If you have a moment to scan it, I would appreciate it. And if you think it is correct enough and valuable, I will post it in the various forums as there must be others like me who would like to know and have access to this interactive kind of way to see across the legacy of Office/Excel+VBA.

And, Yes, I know it is ironic that I am using Google Sheets to show Excel characteristics. However, I don't have easy access nor experience using Excel for the Web. I'd be happy to assist anyone who might want to copy the content and make it open and available via Excel on the Web.