I’ve been creating Microsoft VBAs for years to solve different problems ranging from Finance to Engineering. These VBAs have been developed and hosted on Windows platforms using different versions of Microsoft Office products. Recently I had a VBA requirement where the client used Excel (365) on a Mac. For tis application the key differences between the two products/OSs were:
- No userform development UI on Mac
- Different file dialog
- Full path filename parsing
To solve the the userform development UI issue, I just developed the userform on Windows. When the VBA executed on the Mac the userform was properly displayed without any issues. Any changes to the userform had to be done on a Windows platform.
The second problem was more difficult and required some research. Prevously I’ve accessed files using the Application.FileDialog function with the msoFileDialogFilePicker parameters. It’s straight forward and I have used this code many times. A code segment is shown below.
'Display a Dialog Box that allows to select a single file. 'The path for the file picked will be stored in fullpath variable With Application.FileDialog(msoFileDialogFilePicker) 'Makes sure the user can select only one file .AllowMultiSelect = False 'Filter to just the following types of files to narrow down selection options .Filters.Add "CSV/CRC Files", "*.csv; *.crc", 1 'Show the dialog box .Show 'Store in fullpath variable if file selected If .SelectedItems.Count <> 0 Then fullpath = .SelectedItems.Item(1) Else MsgBox ("No file selected. Exiting program...") Exit Sub End If End With
The problem is Application.FileDialog is not supported by Excel on the MacOS. I am thankful someone else had already solved this problem using an Applescript. I was able to reuse the posted script without any modifications. I created a function for each operating system to create a file dialog window and return the user selected file. On a Mac, if the user doesn’t select a file, the string “-128” (error code userCanceledErr) is returned. So the Windows function also returns “-128” when the user doesn’t select a file.
Function BrowseMac(mypath As String) As String Dim sMacScript As String sMacScript = "set applescript's text item delimiters to "","" " & vbNewLine & _ "try " & vbNewLine & _ "set theFiles to (choose file " & _ "with prompt ""Please select a file or files"" default location alias """ & _ mypath & """ multiple selections allowed false) as string" & vbNewLine & _ "set applescript's text item delimiters to """" " & vbNewLine & _ "on error errStr number errorNumber" & vbNewLine & _ "return errorNumber " & vbNewLine & _ "end try " & vbNewLine & _ "return theFiles" BrowseMac = MacScript(sMacScript) End Function ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Function BrowseWin() As String 'Display a Dialog Box that allows to select a single file. 'The path for the file picked will be stored in fullpath variable With Application.FileDialog(msoFileDialogFilePicker) 'Makes sure the user can select only one file .AllowMultiSelect = False 'Filter to just the following types of files to narrow down selection options .Filters.Add "Text Stock Files", "*.txt, 1" .Title = "Select Input Stock File" 'Show the dialog box .Show 'Store in fullpath variable if file selected If .SelectedItems.Count <> 0 Then BrowseWin = .SelectedItems.Item(1) Else BrowseWin = "-128" ' what Mac OS returns when file is not selected End If End With End Function
Since I was developing on Windows 10 and testing using both operating systems, I used Application.OperatingSystem to determine the host OS to call the appropriate routine. On MacOS Application.OperatingSystem includes “Macintosh” in the returned string. The call to the Mac file browser also uses an Applescript to the provide the documents folder directory path.
' determine OS and get input file via dialog box theOS = Application.OperatingSystem If (InStr(1, theOS, "Macintosh", vbTextCompare) = 0) Then ' not Mac ' Windows Version fullFilename = BrowseWin fileName = getWINFName(fullFilename) Else ' Mac Version fullFilename = BrowseMac(MacScript("return (path to documents folder) as String")) fileName = getMACFName(fullFilename) End If ' check for a good file name If fullFilename = "-128" Then MsgBox ("File not selected, no action taken.") Exit Sub End If
The returned filename has the full path name. For this application I wanted to capture the filename only. So another difference between the operating sytsem is Windows uses the “\” character and MacOS used the “:” character to separate the path.
Function getWINFName(pf) As String: getWINFName = Mid(pf, InStrRev(pf, "\") + 1): End Function Function getMACFName(pf) As String: getMACFName = Mid(pf, InStrRev(pf, ":") + 1): End Function
Working with the texted based input file was the same for both Windows 10 and MacOS 10. Below is a small code snippet showing the opening the file, reading text line, and closing the file once finished.
Open fullFilename For Input As #1 Do Until EOF(1) ' get input record and separate items ' record data time, open, high, low, close, [volume] Line Input #1, textLine ' input record inputSplit = Split(textLine, ",") ' split on ' < good code stuff > Loop Close #1
The Open with the full path name worked on the Mac, but I found if you are using Workbooks.Open with Excel 2016, then the full path filename needs to be modified by changing “:” to “/” and removing the “Macintosh HD” from the full path. I found this nice code on stack overflow, which worked great.
If CInt(Split(Application.Version, ".")(0)) >= 15 Then 'excel 2016 support wbName = Replace(wbName, ":", "/") wbName = Replace(wbName, "Macintosh HD", "", Count:=1) End If
So my first experience developing an Excel VBA for MacOS had small, but solvable technical hurdles, which were solved by using a good search engine and leveraging other developer’s code.