Migrating VBA/Office code to MAC
Background
This is an outline of some of the challenges encountered in porting an Excel/VBA application, OSGB, from Windows to MAC.
The OSGB application imports gpx (cartography) files into Excel, compresses GPX data, exports gpx files for plotting on map applications, and exports kml files for plotting with Google Earth. It performs conversions and calculations on the British Ordnance Survey mapping grid, and also accesses the Streetmap web page to translate UK postcodes to position coordinates. See http://www.haroldstreet.org.uk/osgb/
It is issued as an addin which includes the main routines, and an Excel workbook which performs installation of the addin and gives examples of the functions.
Parts of it were originally developed in Visual Basic in the 1990s, were further developed under Excel 2003, then enhanced to use the ribbon interface of Excel 2007+ and Excel 2016 on Windows.
More recently, thanks to excellent cooperation with Ian Baines, it has been enhanced to operate on MAC with Excel 2016 and 2019. I would not like to minimise the challenge of this task or Ian’s dedication in debugging code remotely. More than 90 versions of the code or testbed programs were tried over a period of six months before we were satisfied that we had robust code. Trial and error were sometimes our only tools due to major omissions, errors and half-truths in the documentation! My son has helped with testing on Office 365.
This page outlines some of the major challenges of this enhancement.
Which Version?
The vba construct Application.Version gives the version number. Versions tried were:
MAC Office 2016. As originally installed it gave version 15.32 which needed a (free) upgrade to take it to 16.16. Without that, very basic functions such as Application.UserLibraryPath failed.
MAC Office 2019 version 16.43 which seems to be stable.
MAC Office 365 version 16.41.
What Worked?
The code for creating the ribbon interface was fine, as was all the code to manipulate and convert coordinates, compute distances, calculate magnetic variation, to create user forms, the undo functionality, to perform both recursive and iterative routines and to manipulate cells.
All this worked without change which was both surprising and impressive! However, joy turned to despair once file access was attempted. Issues needing care or a major rewrite included:
Differences in the format of filenames.
Path separators for MAC are / rather than \ of Windows. Very easily accommodated. Use Application.PathSeparator.
Spaces in filenames
Spaces in filenames are an issue for shell commands under both Windows and MAC and in applescript functions. Either ‘wrap’ them in chr(34) characters or, in the case of MAC, prefix the spaces with the backslash character \.
How to install the add-in?
With XP + Excel 2003 – copy it to Application.UserLibraryPath. No further action is needed.
For Excel 2007 or later, whether Windows 7, Windows 10 or MAC you need to also mark it as ‘installed’ in registry like this:
Dim ai As Excel.AddIn
Dim DestFile as string
DestFile= Application.UserLibraryPath & Application.PathSeparator & “osgb.xlam”
Set ai = AddIns.Add(Filename:=DestFile)
ai.Installed = True
Note that with MAC + Excel 2016 version 15.32 Application.UserLibraryPath gives a compilation error. Upgrade Excel to 16.16.
GetOpenFileName
This does not support the FileFilter parameter so file type checking will need to be done after the user has selected a file. Apart from that, it appears to work provided you meet sandbox requirements.
GetSaveAsFileName.
Similarly, this does not support FileFilter. However, far more seriously, we found GetSaveAsFileName to be quite unstable and caused Excel to freeze and lock-up. It would seem to work first time but ‘poison’ Excel in the process and freeze on subsequent calls.
Choosing Files: Applescript Solution
The solution to stability in choosing files was to drop down into applescript. It needed a file in folder: ~/Library/Application Scripts/com.microsoft.Excel/ (but note the pesky space in that folder path!)
The applescript construct choose file allows a file to be chosen for reading, and choose file name to choose one for writing so we simply used an applescript function (event?) for each type of file required. It is typically called like this:
fname = AppleScriptTask(“osgb.applescript”, “ChooseGPXread,””)
but you will need to check the response that the right file suffix has been supplied.
Choosing a file in this way seems to avoid all the instability problems.
Placing the file in ~/Library/Application Scripts/com.microsoft.Excel/ osgb.applescript proved challenging and we found no way of doing this directly from vba. Our solution was to place the applescript file initially in a benign location (we used Application.ThisWorkbook.Path) then ask the user to invoke the file manually from Finder, whereupon the copy command at the start of the script did the job for us.
The contents of the applescript file is environment dependent as it includes the username and its own location so needs creating dynamically during installation. The username can be extracted by splitting Application.UserLibraryPath.
If you copy/paste this please beware of any line splitting by Word, particularly the shell copy.
The full file, osgb.applescript is:
do shell script “mkdir -p ‘/Users/<username>/Library/Application Scripts/com.microsoft.Excel ‘”
do shell script “cp ‘<the temporary location>/ osgb.applescript’ ‘/Users/<username>/Library/Application Scripts/com.microsoft.Excel/osgb.applescript’'”
display dialog “Please save the examples workbook to complete installation.” buttons {“ok”}
return
on Present()
return “present”
end Present
on ChooseGPXread()
set theDocument to ((choose file with prompt “Please select a gpx file” of type {“gpx”}) as string)
return POSIX path of theDocument
end ChooseGPXread
on ChooseGPXwrite()
set theDocument to ((choose file name with prompt “Please specify a .gpx file for writing”) as string)
return POSIX path of theDocument
end ChooseGPXwrite
on ChooseKMLwrite()
set theDocument to ((choose file name with prompt “Please specify a .kml file for writing”) as string)
return POSIX path of theDocument
end ChooseKMLwrite
on ChoosePMwrite()
set theDocument to ((choose file name with prompt “Please specify a postmortem file (.pm) for writing”) as string)
return POSIX path of theDocument
end ChoosePMwrite
on osgbopen(f)
tell application “Finder” to open f as POSIX file
return 1
end osgbopen
The routine present allows you to check whether the applescript file needs to be supplied. Eg:
Function AppleScriptNeeded() As Boolean
#If Mac Then
On Error GoTo errh
AppleScriptNeeded = False
If (AppleScriptTask(“osgb.applescript”, “Present”, “”) <> “present”) Then
AppleScriptNeeded = True
End if
Exit Function
errh:
AppleScriptNeeded = True
#Else
AppleScriptNeeded = False
#End If
End Function
The Office 365 system we tried this on did not have the applescript folder created during office installation, hence the initial mkdir in the applescript file. This did not work initially – the write functions froze the system, but a month or so later it did work. We believe that an intervening system restart fixed it.
Sandbox Implications
Excel 2016 onwards is run on MAC within a ‘sandbox’ which requires permissions to be granted for folder and file accesses outside the sandbox. The Excel routine GrantAcesstoMultipleFiles can be used to ask the user for permissions.
Before opening any file, we adopted the strategy of issuing GrantAcesstoMultipleFiles for the folder holding the file and then, if the file exists, for that too. That is in in addition to the special measures which were needed to choose files.
We wonder whether there is a chicken and egg situation with GetFileName and GetSaveasFileName. If you do not have permissions to access the folder then how can you read it in order to choose a file?
Our solution to choosing, writing to then opening a file (eg a kml file) was:
- Choose file with applescript function ChooseKMLWrite.
- Check file type.
- Grant permissions on folder
- Grant permissions on file (If it exists)
- Open and write to the file
- Grant permissions on the file
- Open the file with the system dependent application with the applescript function osgbopen.
Note that these are sufficient but perhaps not necessary steps.
Application.IsSandboxed
There is a vba function Application.IsSandboxed which sounds to be very useful. Unfortunately, it isn’t!
- It does not work – it does not return ‘true’ with Excel 2019/365 as one would expect
- Even if it did work, it would not be useful – it needs to be a compile time directive to exclude GrantAcesstoMultipleFiles calls in a non sandboxed environment
You might try:
#if MAC_OFFICE_VERSION >=16 then
GrantAcesstoMultipleFiles …….
#end if
Reading a URL
We (ab)use the Streetmap web site to ‘scrape’ the location of postcodes in GB and this needed a different approach on MAC. See the brilliant execShell() function courtesy of Robert Knight via StackOverflow:
https://stackoverflow.com/questions/15981960/how-do-i-issue-an-http-get-from-excel-vba-for-mac
Open a file with its native application:
Once we have written a gps or a kml file we ask the user whether it should be opened – typically with a mapping application like MemoryMap, Garmin Basecamp or Google Earth.
With windows we used:
Reply=shell(“explorer “ & chr(34) & filename & chr(34))
On MAC we tried:
Reply=shell(“open “ & chr(34) & filename & chr(34))
That was fine with Excel 2019 but not Excel 2016. Instead, we had to delve down into the Applescript routine osgbopen.
Reply= AppleScriptTask(“osgb.applescript”, “osgbopen”, chr(34) & filename & chr(34))
The chr(34) is necessary in case there are spaces in the filename.
References
http://www.haroldstreet.org.uk/osgb/
https://docs.microsoft.com/en-us/office/vba/api/overview/office-mac
https://macadmins.software/docs/UserContentIn2016.pdf
http://www.rondebruin.nl/mac/mac034.htm
https://warwick.ac.uk/fac/sci/systemsbiology/staff/dyer/software/excelvbafileopen/
https://forum.latenightsw.com/t/debugging-an-applescript-handler-in-vba/2705
http://youpresent.co.uk/developing-installers-for-office-mac-2016-application-add-ins/
http://stackoverflow.com/questions/6136798/vba-shell-function-in-office-2011-for-mac
https://stackoverflow.com/questions/15981960/how-do-i-issue-an-http-get-from-excel-vba-for-mac
Thanks
Thanks to Phil Newby for his generosity in hosting this content, to Ian Baines for his assistance in debugging on the MAC and my son for testing it with Office 365.
Contact
You can contact me with my first name dot second name on my UK Hotmail account to discuss the above. No spam please!
Phil Brady 15 Dec 2020.