Link to home
Start Free TrialLog in
Avatar of marrick13

asked on

Problem with VBA API code on 64-bit machine

I have a Word VBA application that one of the EE experts helped me with when I was still using my old 32-bit PC with Win 7, Now I'm on a 64-bit one running Windows 10, and have already changed the approrpriate Longs to "LongPtr" and the occurrences of "Len(cc)" to "LenB(cc)". But now, when I click the Choose Line Color (or Fill Color) I get a type mismatch error on the "RvalL = cc.rgbResult Mod 256" statement in the "Private Sub cmdLineColor_Click()" (and "Private Sub cmdFillColor_Click())" module, but only on my new 64-bit machine, not the old 32-bit one.

I don't know what else needs to be changed to get this to work on a 64-bit machine - can someone help? I don't know VBA well enough to be able to resolve this on my own.

Code is in the attachment.

Insert Shapes Application-New (64-bit).docm
Avatar of Bill Prew
Bill Prew

That's quite a bit of code to sift through, can you describe a repeatable set of steps that reproduce the error, that would be helpful in trying to troubleshoot where changes might be needed.

I always ask, why not simply install the 32-bit version of Office and be done with the issue altogether?
Avatar of marrick13


Running the macro opens the Insert Shapes for Word userform, Choose "rectangle" from the list and then click either "Choose Fill Color" or "Choose Line Color" - both will produce the error. Before I changed the Longs and Len(cc), I got no error but nothing happened when clicking either of those buttons. Bout both worked fine in 32-bit.

I wasn't aware I could download 32-bit Office but I would rather stay with 64-bit and not risk further such problems in the future. Isn't 64-bit better, anyway? The only code I run that seems to have a problem on 64-bit is that API code for userforms, and I'd rather is be compatible for any machine. And if I change to Office 32-bit, and I want to run those macros on a 64-bit machine that is not my own, I wouldn't be able to.
Isn't 64-bit better, anyway?
Have you been limited by 32-bit?  64-bit enables more RAM access amongst other things, thus you can handle more data, larger excel workbooks,... So if that was an issue before, perhaps 64-bit will be an improvement, but for the vast majority of users if offers nothing (well headaches because they have to fix all their code, activex controls, ...).

And if I change to Office 32-bit, and I want to run those macros on a 64-bit machine that is not my own, I wouldn't be able to.                                  
No. In such a case you would need to set your code up using Conditional Compiler Directives and then you could run your code in both.  You may like to briefly review

Okay, I see that now.

Actually at this point, I'd like to see the 32 bit version, before you made any changes to it.  

Sure, I've attached a test version from Jan 2020 that has the 32-bit code.
Insert Shapes App Test for Word (Jan 16 2020).docm
Okay, thanks, I'll take a close look tomorrow...

Thanks! No hurry, though.
Well, simply changing Long data types to LongPtr isn't a good approach.
You need to indentify wich data are pointers and wich are not (see APIs documentation for that).

Plus, my static code analysis tool (Rubberduck) show over 450 issues (obscure variable names / dead code / missing forms / undefined variables / uninitialized variables ect ....), maybe you should work on fixing that first.

Finally, nowaday, taking advantage of the ribbon might be a better solution that those forms.
I got the idea of changing the Long data types to LongPtr from online sources. Of course, I don't understand the code well enough to be able to identify which of those need to be changed, which is one reason I turned to EE. Also, none of those 450 issues was a problem in 32-bit, so are you saying that now some of them are? If so, I may as well forget about 64-bit and just get the 32-bit Office.
As I  mentioned, I'll take a look at the things that will need to be adjusted for 64 bit and let's see what that reveals.  There are always changes / opportunities for improvement when we revisit old code, but in this case that could just introduce more changes and things that have to be tested...

Okay, I backed out some of the changes you had made, and I think I have the corrected code in place now.  It runs here for me under 64-bit Windows with 64-bit Word (2019).  I can't test the same code on 32-bit Office, I don't have that installed.

A couple of notes:
  • All the changes were in the code for the frmInsertShape and frmInsertShapeHelp modules.
  • I commented out these API definitions since they didn't seem to be used anyplace: ShowWindow, GetSystemMenu, GetMenuItemCount, RemoveMenu.
  • I also had to comment out the line shown below since frmMacroMgr doesn't exist.  This looked like new code that was added from the 32-bit version you sent me so I'm not sure what the intent was here.

Private Sub lblRetMacroMgr_Click()
Unload frmInsertShape
' frmMacroMgr.Show (vbModeless)
End Sub

Open in new window

Here is the Word file with the updated VBS code in it, give it a go there...



This works - thanks! It also works in my old 32-bit environment. I see you added the conditional "#If VBA7 Then' , something I'd thought about doing when I first started making those macros 64-bit compliant but found what I ended up doing seemed to work, with this macro being the only known exception.

I did notice that the uncommented "Private Declare PtrSafe Function" statements following the occurrences of "#Else" are all in red, which means that if the #Else is ever true, that will produce an error. Do I need to be concerned about them?

The macro manger is another application that was intended to run this one-I did not have a current enough version without the changes I made so I sent you an older test file that had that code in it.
Avatar of Bill Prew
Bill Prew

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glad that helped, thanks for the feedback, good luck!

Thanks again - you were very helpful!
 Also, none of those 450 issues was a problem in 32-bit, so are you saying that now some of them are?
Nope, a source code with a lot of issues (even if it run) is an indicator of poor quality, and should be improved for better readability and maintainability.
Agreed, but I am not a developer and usually have to cobble together code from other authors to get the functionality I seek. I always do declare variables and make the effort to removed unused ones, but I'm not at the level of the professional who would do all those things you recommend. I use tools like a hammer and a screwdriver, but I'm not a carpenter...