Forum Discussion
Solved
Porting an Excel VBA application from Windows to Mac
I have Office 365 on a PC running Windows 11 and on a Mac Mini running Sonoma. I develop on Widows. After testing thoroughly, I copy the .xlsm file over to Mac for testing there. I have been doing this for several years, and have gone through many rounds of testing on both Windows and Mac. But something seems to have changed recently.
Today, I ran the application on Mac in the usual way by double clicking the .xlsm file that I just brought over from the PC. The application halted with an Overflow error. In the Debugger, this was the problem line:
Dim i as Integer
i = 77
OK, that shouldn't overflow, should it? I was able to execute the line in the debugger without raising the overflow error. But when I let the program to run, it halted with an overflow at some other trivial assignment. Same story: single stepping in the debugger does not raise the error.
After trying several random things, I decided to launch Excel in a different way. I opened Finder, went to Applications, and double clicked Excel.app. In the Open window, I chose my .xlsm file. Now the application runs faultlessly with no runtime errors. To make sure it wasn't a fluke, I ran the test several times this way and it worked every time.
My question is this: On a Mac, what's the difference between (1) double-clicking an .xlsm file and (2) running Excel.app and then selecting the .xlsm file in the Open window?
- The only tool I know of only works in 32 bit Office. You can clean a project manually like this:
- Save-as the file as xlsx (yes, the VBA code is lost, this is on purpose)
- Close and reopen the newly saved file and the original one with the code
- Drag all modules, userform and class modules from the xlsm to the xlsx
- Copy code from all sheet modules to the appropriate sheet modules of the xlsx
- Copy code in ThisWorkbook too.
- Make sure the correct references are set
- Save As the xlsx to xlsm
You could also use this tool to copy the VBA project from the xlsm to the xlsx: https://jkp-ads.com/download.asp#CopyVBAProject
- I don't know the answer I'm afraid. Does it help to clean the VBA project?
- How do you recommend cleaning the project? Is there a tool you recommend?
- The only tool I know of only works in 32 bit Office. You can clean a project manually like this:
- Save-as the file as xlsx (yes, the VBA code is lost, this is on purpose)
- Close and reopen the newly saved file and the original one with the code
- Drag all modules, userform and class modules from the xlsm to the xlsx
- Copy code from all sheet modules to the appropriate sheet modules of the xlsx
- Copy code in ThisWorkbook too.
- Make sure the correct references are set
- Save As the xlsx to xlsm
You could also use this tool to copy the VBA project from the xlsm to the xlsx: https://jkp-ads.com/download.asp#CopyVBAProject
