Porting an Excel VBA application from Windows to Mac

[perkin_warbeck]

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?

[#2]

I don't know the answer I'm afraid. Does it help to clean the VBA project?

[#3]

How do you recommend cleaning the project? Is there a tool you recommend?

[#4 · Accepted solution]

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

[#5]

Thank you for your suggestion, and for the link to CopyVBAProject. It sounds like a very sensible approach. I will try it.

[#6]

I installed your CopyVBAProject Add-In in an otherwise empty workbook which I called Cleaner.xlsx. I opened the Source workbbok (an .xlsm) and the Target workbook (an .xlsx) and then clicked on the Add-In. In the user form, I selected the source workbook from the dropdown menu. But as soon as I clicked the Target workbook, the form code runs (I was not given a chance to click OK) and hit an error which causes execution to Stop with Err.Number = 1004. The line that causes the error is
```
For Each oComp in SourceBook.VBProject.VBComponents
```
By the way, the target workbook is the same as the source workbook, but was saved as an .xlsx to remove all VBA code, as you suggested.

[#7]

You must turn on "Allow Access to the VBAProject object model" in Excel Options, Trust center, Trust center settings, Macro settings.