VBA migration from 32 to 64 bit

User avatar
SammyB
StarLounger
Posts: 93
Joined: 04 Mar 2010, 16:32

VBA migration from 32 to 64 bit

Post by SammyB »

I have the misfortune of having to get a new computer. Microsoft says I need to use Office 365 64 bit. Easy for them to say. :groan:

Anyway, I'm migrating Excel macros in Office 365 from 32 to 64 bit. Sure seems like some large-brained person would have already made a check list, but I cannot find one. Here's what I have learned:

1) You need to add PtrSafe to your Declare statements. If you thought that you had already made provision for both 32 & 64 with a #if VBA7 meta-statement, you will have to comment-out the 32-bit section.

2) I could not copy Personal.xlsb to the 64 bit system, so I exported all of the modules & created the 64 bit Personal.xlsb from scratch

3) If you have customized your toolbars, you can export them (bottom of the customize dialog) & import them to the new Excel, but any buttons that call your macros (in Personal.xlsb) will have to be recreated, but at least you have a guide from the old buttons.

4) If you used SortedList from mscorlib, you are a big loser. I cannot get it to work at all: gives an Automation error when you try to create a SortedList. Best option is replace it with a Scripting.Dictionary. Chip Pearson has lovely code for you to use to sort the Dictionary.

5) Item that I am working on now and the reason for my post. Buttons created programmatically do not work.

Details for number 5. I often need for the user to perform a search in other software, export it to csv & then continue with my macros. So I create a button with

Code: Select all

Private Sub PlaceButton(ws As Worksheet, sPrompt As String, sAction As String)
    ws.Activate
    Dim b As Button, r As Range
    Set r = ws.Range("V7:Z9")
    Set b = ws.Buttons.Add(r.Left, r.Top, r.Width, r.Height)
    With b
        .Caption = sPrompt
        .OnAction = sAction
    End With
End Sub
The calling routine just puts the macro name as the second parameter. When you run it, the button is created, but nothing happens when you are ready to press the button. I have to manually run the macro that the button should have run.

User avatar
SammyB
StarLounger
Posts: 93
Joined: 04 Mar 2010, 16:32

Re: VBA migration from 32 to 64 bit

Post by SammyB »

Microsoft does have some guidelines:
https://docs.microsoft.com/en-us/office ... -of-office
But, after reading it, I'm even more scared: "Existing 32-bit ActiveX controls, are not compatible with the 64-bit versions of Office....contact the vendor for an updated version." :scratch: Microsoft, you ARE the vendor for ActiveX controls.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA migration from 32 to 64 bit

Post by HansV »

You can still uninstall the 64-bit version and replace it with the 32-bit version. The 64-bit version doesn't offer many advantages in real life, in my opinion, and many disadvantages.
I had the 64-bit version for a while, but decided to go back to the 32-bit version, even though I had solved the VBA problems.Some of the add-ins I like to use don't work on 64-bit.

(See Declaring API functions in 64 bit Office for more info on using Windows API functions)
Best wishes,
Hans

User avatar
SammyB
StarLounger
Posts: 93
Joined: 04 Mar 2010, 16:32

Re: VBA migration from 32 to 64 bit

Post by SammyB »

I have solved Item 5, Button push OnAction. It doesn't seem to check if the macro is in PERSONAL.XLSB. This code works:

Code: Select all

Private Sub PlaceButton(ws As Worksheet, sPrompt As String, sAction As String)
    ws.Activate
    Dim b As Button, r As Range
    Set r = ws.Range("V7:Z9")
    Set b = ws.Buttons.Add(r.Left, r.Top, r.Width, r.Height)
    With b
        .Caption = sPrompt
        .OnAction = "PERSONAL.XLSB!" & sAction
    End With
End Sub

User avatar
SammyB
StarLounger
Posts: 93
Joined: 04 Mar 2010, 16:32

Re: VBA migration from 32 to 64 bit

Post by SammyB »

Re: go back to 32-bit

I may have to do that. I think some of my users on still on 32 bit Office. It will be interesting to see what happens when they try to use a workbook that I have created.

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: VBA migration from 32 to 64 bit

Post by ErikJan »

In scope and I believe I had posted this question here somewhere before, but can't find it so I'm repeating...

To make sure I'm compatible with Office 32- AND 64-bit (and assuming Excel is v2010 or later), is this sufficient?

Code: Select all

Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As LongPtr

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: VBA migration from 32 to 64 bit

Post by rory »

The return value for GetUserName is still a Long in 64bit, not a LongPtr, but otherwise yes that's OK - if you are sure they will have 2010 or newer.
Regards,
Rory

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: VBA migration from 32 to 64 bit

Post by rory »

SammyB wrote:
30 Dec 2020, 18:55
If you thought that you had already made provision for both 32 & 64 with a #if VBA7 meta-statement, you will have to comment-out the 32-bit section.
Unless you meant something more than you actually wrote, that is not true.
Regards,
Rory

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: VBA migration from 32 to 64 bit

Post by ErikJan »

rory wrote:
04 Jan 2021, 15:50
The return value for GetUserName is still a Long in 64bit, not a LongPtr, but otherwise yes that's OK - if you are sure they will have 2010 or newer.
OK, so this is correct for 32 and 64 bit then (just added PtrSafe), thanks!

Code: Select all

Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA migration from 32 to 64 bit

Post by Doc.AElstein »

SammyB wrote:
31 Dec 2020, 03:43
I have solved Item 5, Button push OnAction. It doesn't seem to check if the macro is in PERSONAL.XLSB. This code works:
......
.OnAction = "PERSONAL.XLSB!" & sAction
.......
Hello SammyB
So are we saying that this problem item 5 was not directly to do with the 32 64 issue?
My guess would be that you were just lucky previously that Excel took the right guess at where to find the macro that you were referring to. ( Possibly Excel will often guess correctly where your macro is if it is one in your PERSONAL.XLSB ***)

It’s probably best to always give the full macro reference.....

I have never ever used the .OnAction, and also, although I had heard of the PERSONAL.XLSB thing , I had not had much to do with that thing either…

But I took a guess that the correct referencing syntax might be something similar to things like the syntax in Application.OnTime or Application.Run
My initial investigation suggests that is the case. That being the case, it might help avoid other unexpected problems to try to fully reference the macro.
It took me a couple of years to finally figure out the tricky syntax in the full reference for a macro. So I thought it would be useful for me here to post the example for this Thread for future reference.

The following macros below is all in the uploaded workbook, API3264Button.xls. It is doing something similar to your macro. It makes a couple of buttons. I am just trying to demo for you with the macros the full syntax for a macro got at by the .On Action
http://i.imgur.com/onDA5lH.jpg

Code: Select all

 Sub SamTest() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=35920
Rem First Call of PlaceButton to standard module here
' Call PlaceButton(ActiveSheet.Range("A7:E9"), "SayHello", "'F:\Excel0202015Jan2016\OffenFragensForums\eileenslounge\API3264Button.xls'!'Modul1.Samction ""Hello""'")
1 Call PlaceButton(ActiveSheet.Range("A7:E9"), "SayHello", "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!'Modul1.Samction    ""Hello Sammy"" & vBCr & VblF        ,    1  ,   ""from "" & tHisWOrkbOOk.nAmE  '")

Rem Second Call of PlaceButton to  Personal thing
' where is my  Personal thing.....  FoundPersonal XLSB.JPG :   http://i.imgur.com/1b6bPNN.jpg       C:\Users\Elston\AppData\Roaming\Microsoft\Excel\XLSTART
' you you will need to modify this following bit accordingly to get to your PERSONAL.XLSB and the macro in it. ( so you need to change the path at the start and also change the module name  )
2 Call PlaceButton(ActiveSheet.Range("A11:E13"), "SayHello2", "'C:\Users\ElstOn\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB'!'Modul1.Samction2   ""Hello Sammy"" & VBcr & vbLF ,         2  ,     ""from PERSONAL.XLSB""             '")
End Sub
'
Private Sub PlaceButton(r As Range, sPrompt As String, sAction As String)
Dim b As Button
 Set b = r.Parent.Buttons.Add(r.Left, r.Top, r.Width, r.Height)
    With b
     .Caption = sPrompt
     .OnAction = sAction
    End With
End Sub
'  This macro can be referenced by simply   Samction    But for demo purposes I use the full reference in  Call 1    'F:\Excel0202015Jan2016\OffenFragensForums\eileenslounge\API3264Button.xls'!'Modul1.Samction ""Hello""'
Sub Samction(ByVal Word1 As String, ByVal Nmber As Long, ByVal Word2 As String)
 MsgBox Prompt:="Call " & Nmber & ".   " & Word1 & " " & Word2
End Sub
You will need to do a few things before you run Sub SamTest()
_ First in your PERSONAL.XLSB you will need to put a macro like this in a module
http://i.imgur.com/MTtWoMU.jpg

Code: Select all

 Sub Samction2(ByVal Word1 As String, ByVal Nmber As Long, ByVal Word2 As String)
 MsgBox Prompt:="Call " & Nmber & ".  " & Word1 & " " & Word2
End Sub
_ You will then need to make a note of the code module name, and also try to find and make a note of the full path to your PERSONAL.XLSB. It took me a while to find my PERSONAL.XLSB . In fact I couldn’t find it anywhere until after I had saved the above macro ( Sub Samction2 ) and restarted my Excel. I then finally found it here: C:\Users\Elston\AppData\Roaming\Microsoft\Excel\XLSTART
(***Incidentally, that path looks similar to a default one that I have often seen Excel guess for me. So that might explain why you have a good chance sometimes of Excel guessing correctly where your PERSONAL.XLSB is: That sort of path may be a typical default path that Excel uses. ).
Once you have done all that, then, you will need to modify this following bit accordingly to get to your PERSONAL.XLSB and the macro in it. ( So you need to change the path at the start and also change the module name )
C:\Users\ElstOn\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB'!'Modul1.Samction2
( make sure you check in the complete final code line that you have two pairs ( so 4 in total ) of the single quotes '
It must have a form like pseudo 'yyyyyyyy'!'xxxxxxxx' )


It took me a couple of years to finally figure out the tricky syntax in the full reference for a macro. That’s why I thought it would be helpful to post the example for this Thread for future reference.



Alan

_.___________________________________-

Share ‘PERSONAL.XLSB’ : https://app.box.com/s/hzbsw05wq5mw4mefmphg1ju83i3gizgd
Share ‘API3264Button.xls’ : https://app.box.com/s/z6otirzalf2ktj3kqy97sdy11md3wiqb
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA migration from 32 to 64 bit

Post by Doc.AElstein »

P.S.
SammyB wrote:
30 Dec 2020, 18:55
5) Item 5 ......
...
............ the button is created, but nothing happens when you are ready to press the button. I have to manually run the macro that the button should have run.
I am not quite sure what you mean by … when you are ready to press the button…
If you press the button and Excel can’t find the macro , then I would of expected an error message telling you that it can’t find the macro .

For example if I change my second Call such that it just refers to the macro as

Code: Select all

 "'Samction2   ""Hello Sammy"" & VBcr & vbLF ,         2  ,     ""from PERSONAL.XLSB""             '"
Then, on pressing the second button, I will get the error that it can’t find the macro: http://i.imgur.com/TQ4Nt9s.jpg

Similarly I get a similar error, if I refer to the macro as

Code: Select all

 "'Modul1.Samction2   ""Hello Sammy"" & VBcr & vbLF ,         2  ,     ""from PERSONAL.XLSB""             '"
on pressing the second button, I will get the error that it can’t find the macro: http://i.imgur.com/zsHTj1A.jpg

You can see from those error messages that Excel is guessing the wrong workbook


If I simply add the name of my personal workbook like this

Code: Select all

 "PERSONAL.XLSB!'Modul1.Samction2   ""Hello Sammy"" & VBcr & vbLF ,         2  ,     ""from PERSONAL.XLSB""             '"
or even if I do the referencing like this

Code: Select all

 "PERSONAL.XLSB!'Samction2   ""Hello Sammy"" & VBcr & vbLF ,         2  ,     ""from PERSONAL.XLSB""             '"
then I get no error , and the macro works, and so does the second button when I press it. That is similar to what you found.
I suppose that is telling us that Excel guesses correctly where PERSONAL.XLSB is. ( Or maybe it goes searching and finds it ). But I personally would not rely on that: I personally would give the full reference. Just a personal preference. ....…. a personal reference personal preference :evilgrin: :smile:
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
SammyB
StarLounger
Posts: 93
Joined: 04 Mar 2010, 16:32

Re: VBA migration from 32 to 64 bit

Post by SammyB »

Thanks for all that help. When the end-of-year, end-of-month, & tax season rush are over, I will look at your responses. I somehow fixed the button push issue & it works fine, but I don't remember how I fixed it & the code still looks the same, but if it is still an issue, I will add the referencing, thanks Doc!

There is an issue 6: range.end(xlDown) does not work correctly, but there are other ways to do it, so it is also fixed.