{"id":299,"date":"2021-01-10T15:43:28","date_gmt":"2021-01-10T15:43:28","guid":{"rendered":"http:\/\/www.haroldstreet.org.uk\/osgb\/?page_id=299"},"modified":"2021-01-12T12:47:32","modified_gmt":"2021-01-12T12:47:32","slug":"migrating-vba-office-code-to-mac","status":"publish","type":"page","link":"https:\/\/www.haroldstreet.org.uk\/osgb\/migrating-vba-office-code-to-mac\/","title":{"rendered":"Migrating VBA\/Office code to MAC"},"content":{"rendered":"\n

Background<\/u><\/strong><\/h3>\n\n\n\n

This is an outline of some of the challenges encountered in porting an Excel\/VBA application, OSGB,  from Windows to MAC.<\/p>\n\n\n\n

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\/<\/a><\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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\u2019s 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. <\/p>\n\n\n\n

This page outlines some of the major challenges of this enhancement.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Which Version?<\/u><\/strong><\/h3>\n\n\n\n

The vba construct Application.Version<\/em> gives the version number.  Versions tried were:<\/p>\n\n\n\n

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<\/em> failed.<\/p>\n\n\n\n

MAC Office 2019 version 16.43 which seems to be stable.<\/p>\n\n\n\n

MAC Office 365 version 16.41.<\/p>\n\n\n\n

<\/p>\n\n\n\n

What Worked?<\/u><\/strong><\/h3>\n\n\n\n

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. <\/p>\n\n\n\n

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:<\/p>\n\n\n\n

<\/p>\n\n\n\n

Differences in the format of filenames. <\/u><\/strong><\/h3>\n\n\n\n

Path separators for MAC are \/ rather than \\ of Windows.  Very easily accommodated.  Use Application.PathSeparator.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Spaces in filenames<\/u><\/strong><\/h3>\n\n\n\n

Spaces in filenames are an issue for shell commands under both Windows and MAC and in applescript functions.  Either \u2018wrap\u2019 them in chr(34) characters or, in the case of MAC, prefix the spaces with the backslash character \\.<\/p>\n\n\n\n

<\/p>\n\n\n\n

How to install the add-in?<\/u><\/strong><\/h3>\n\n\n\n

With XP + Excel 2003 \u2013 copy it to Application.UserLibraryPath<\/em>.  No further action is needed.<\/p>\n\n\n\n

For Excel 2007 or later, whether Windows 7, Windows 10 or MAC you need to also mark it as \u2018installed\u2019 in registry like this:<\/p>\n\n\n\n

Dim ai As Excel.AddIn<\/em><\/p>

Dim DestFile as string<\/em><\/p>

DestFile= Application.UserLibraryPath & Application.PathSeparator &  \u201cosgb.xlam\u201d<\/em><\/p>

Set ai = AddIns.Add(Filename:=DestFile)<\/em><\/p>

ai.Installed = True<\/em><\/p><\/blockquote>\n\n\n\n

Note that with MAC + Excel 2016 version 15.32  Application.UserLibraryPath<\/em> gives a compilation error.    Upgrade Excel to 16.16.<\/p>\n\n\n\n

<\/p>\n\n\n\n

GetOpenFileName<\/u><\/strong><\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

<\/p>\n\n\n\n

GetSaveAsFileName.<\/u><\/strong><\/h3>\n\n\n\n

S<\/strong>imilarly, 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 \u2018poison\u2019 Excel in the process and freeze on subsequent calls.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Choosing Files:   Applescript Solution<\/u><\/strong><\/h3>\n\n\n\n

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!)<\/p>\n\n\n\n

The applescript construct choose file<\/u><\/em> allows a file to be chosen for reading, and choose file name<\/u><\/em> 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:<\/p>\n\n\n\n

fname = AppleScriptTask(“osgb.applescript”, “ChooseGPXread,\u201d\u201d)<\/em><\/p><\/blockquote>\n\n\n\n

but you will need to check the response that the right file suffix has been supplied.<\/p>\n\n\n\n

Choosing a file in this way seems to avoid all the instability problems.<\/p>\n\n\n\n

Placing the file in    ~\/Library\/Application Scripts\/com.microsoft.Excel\/ osgb.applescript<\/em> 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<\/em>) 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.<\/p>\n\n\n\n

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<\/em>.<\/p>\n\n\n\n

If you copy\/paste this please beware of any line splitting by Word, particularly the shell copy.<\/p>\n\n\n\n

The full file, osgb.applescript is:<\/p>\n\n\n\n

do shell script “mkdir -p \u2018\/Users\/<username>\/Library\/Application Scripts\/com.microsoft.Excel \u2018”<\/em><\/p>

do shell script “cp ‘<the temporary location>\/ osgb.applescript’  \u2018\/Users\/<username>\/Library\/Application Scripts\/com.microsoft.Excel\/osgb.applescript\u2019'”<\/em><\/p>

display dialog “Please save the examples workbook to complete installation.” buttons {“ok”}<\/em><\/p>

return<\/em><\/p>

on Present()<\/em><\/p>

return “present”<\/em><\/p>

end Present<\/em><\/p>

on ChooseGPXread()<\/em><\/p>

set theDocument to ((choose file with prompt “Please select a gpx file” of type {“gpx”}) as string)<\/em><\/p>

return POSIX path of theDocument<\/em><\/p>

end ChooseGPXread<\/em><\/p>

on ChooseGPXwrite()<\/em><\/p>

set theDocument to ((choose file name with prompt “Please specify a .gpx file for writing”)  as string)<\/em><\/p>

return POSIX path of theDocument<\/em><\/p>

end ChooseGPXwrite<\/em><\/p>

on ChooseKMLwrite()<\/em><\/p>

set theDocument to ((choose file name with prompt “Please specify a .kml file for writing”) as string)<\/em><\/p>

return POSIX path of theDocument<\/em><\/p>

end ChooseKMLwrite<\/em><\/p>

on ChoosePMwrite()<\/em><\/p>

set theDocument to ((choose file name with prompt “Please specify a postmortem file (.pm) for writing”) as string)<\/em><\/p>

return POSIX path of theDocument<\/em><\/p>

end ChoosePMwrite<\/em><\/p>

on osgbopen(f)<\/em><\/p>

tell application “Finder” to open f as POSIX file<\/em><\/p>

return 1<\/em><\/p>

end osgbopen<\/em><\/p><\/blockquote>\n\n\n\n

The routine present<\/em> allows you to check whether the applescript file needs to be supplied.  Eg:<\/p>\n\n\n\n

Function AppleScriptNeeded() As Boolean<\/p>

#If Mac Then<\/p>

    On Error GoTo errh<\/p>

    AppleScriptNeeded = False<\/p>

    If (AppleScriptTask(“osgb.applescript”, “Present”, “”) <> “present”) Then<\/p>

                AppleScriptNeeded = True<\/p>

    End if<\/p>

    Exit Function<\/p>

errh:<\/p>

    AppleScriptNeeded = True<\/p>

#Else<\/p>

    AppleScriptNeeded = False<\/p>

#End If<\/p>

End Function<\/p><\/blockquote>\n\n\n\n

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 \u2013 the write functions froze the system, but a month or so later it did work.  We believe that an intervening system restart fixed it.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Sandbox Implications<\/u><\/strong><\/h3>\n\n\n\n

Excel 2016 onwards is run on MAC within a \u2018sandbox\u2019 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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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?<\/p>\n\n\n\n

Our solution to choosing, writing to then opening a file (eg a kml file) was:<\/p>\n\n\n\n