Excel VBA code not working on a Mac - having trouble figuring this out!

[kbc]

So I've written a routine for customers, and those with Macs are not able to run it. It works flawlessly on PCs. Don't have a Mac myself to test on, which doesn't help. I've Googled this quite a bit and can't seem to figure it out. Here is what the error message on their Mac says:

Compile Error: User-defined type not defined (modPanda 8:4)

So the module is obviously called "modPanda" and I think the "8:4" references screen coordinates for the offending code (line 8 column 4). Not sure if I'm even right on that. At first I thought there could be a missing library reference or something, but I think the error message would state such instead of just being a compile error, no?

In any case, the code on line 8 is the first line of code in the module, just declaring a string variable:

Dim sFileName As String

And I've placed the full code below (two subroutines contained in the same module). Would certainly appreciate if someone could point me in the right direction here. Is there an issue in my code? A potential issue with a setting on their Mac? I'm told that the client has the "latest" version of Excel for Mac if that matters. Thanks!

Option Explicit
Option Private Module
Option Base 1
Dim m_sScope As String
Dim m_wbSource As Workbook, m_wbTemplate As Workbook
Dim m_sgData() As Single
Sub LaunchUpdateTemplate()
Dim sFileName As String
Dim fd As Office.FileDialog


' manage source file, launch update template routine and present confirmation dialog


Set m_wbTemplate = ThisWorkbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)

' present browse dialog to select file
With fd
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "All Excel Files", "*.xls*"
If .Show = True Then
sFileName = Dir(.SelectedItems(1))
End If
End With

' account for no file selected
If sFileName = "" Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With


' open selected file
Workbooks.Open Filename:=sFileName
Set m_wbSource = ActiveWorkbook

Call UpdateTemplate("Forecast")
Call UpdateTemplate("Budget")

m_wbSource.Close SaveChanges:=False

With m_wbTemplate
.Worksheets("Administration").Activate
.Save
End With

With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub
Sub UpdateTemplate(m_sScope As String)
Dim ws As Worksheet
Dim vSourceSegment As Variant
Dim rCell As Range, rWriteRange As Range, rSourceRange As Range
Dim q As Integer, x As Integer, y As Integer, z As Integer


' update template with forecast or budget data from source file

' create array with source file segment names
vSourceSegment = Array("BAR", "DISCOUNT", "LEISURE", "NEG CORP", "NEG GOLD", "NEG GOV", "NET ONLINE", "FAIR GROUP", "RESIDENTIAL", _
"ROOM ONLY", "GOVERNMENT GROUP", "AIRLINES", "LEISURE GROUP", "LONG TERM", "SPECIAL GROUP", "HOUSE USE", "COMP")

' size array to hold rooms and revenue figures
x = m_wbTemplate.Sheets(3).Range("A:A").Cells.SpecialCells(xlCellTypeFormulas).Count
ReDim m_sgData(x, 2)

' populate array with rooms and revenue figures from source file
With m_wbSource.Sheets(1)
z = .Range("C:C").Cells.SpecialCells(xlCellTypeConstants).Count
Set rSourceRange = .Range("C2:C" & z)
For y = 1 To UBound(vSourceSegment)
q = y
For Each rCell In rSourceRange
If rCell.Value = vSourceSegment Then
Select Case m_sScope
Case Is = "Forecast"
m_sgData(q, 1) = rCell.Offset(0, 4).Value ' store rooms to array
m_sgData(q, 2) = rCell.Offset(0, 7).Value ' store revenue to array
Case Is = "Budget"
m_sgData(q, 1) = rCell.Offset(0, 8).Value ' store rooms to array
m_sgData(q, 2) = rCell.Offset(0, 10).Value ' store revenue to array
End Select
q = q + UBound(vSourceSegment) ' skip spaces in array for number of segments
End If
Next rCell
Next y
End With

' transfer array to proper worksheet depending on scope (forecast or budget)
For Each ws In m_wbTemplate.Worksheets
If Left(ws.Name, 1) = Left(m_sScope, 1) Then
x = ws.Index
End If
Next ws
Set rWriteRange = m_wbTemplate.Sheets(x).Range(m_wbTemplate.Sheets(x).Cells(5, 3).Address, m_wbTemplate.Sheets(x).Cells(UBound(m_sgData) + 4, 4).Address)
rWriteRange.Value = m_sgData
m_wbTemplate.Sheets(x).Columns("C:D").Columns.AutoFit

End Sub

 

[GJSBED]

Unfortunately Macros on Macs talk a language called Applescript not VBA since 2008 -

I dont think there's anything you can do but buy a mac and re-write the macro in Applescript, or give all your customers PC's.


http://www.mactech.com/vba-transition-guide/index-001.html

 

[kbc]

Thanks for your response. You are right that VBA for Office went away for a while in the Mac version, but I have been able to confirm that since Office 2011 it is back. So my code should work in theory - just need to figure out why I'm getting this compile error.

 

[wideboydixon]

I'd guess that this is the problem:

Dim fd As Office.FileDialog

Not sure you'd going to have access to the MS Office Object Library on a Mac?

Update: See here - excel - VBA Application.FileDialog not found - Stack Overflow

WBD

 

[kbc]

Thanks WBD. I suspect that you're right on this. I'll try the solution on the link you posted. I also found this just now, giving similar advice:

https://msdn.microsoft.com/en-us/library/office/hh710200(v=office.14).aspx

Looks like the solution is a combination of VBA and Apple Script. I'll try this, and will post back here with results.

 

[alansidman]

crossposted: Trying to get my Excel VBA code to compile & run on a Mac with most recent MS Office

 

[kbc]

So as it turns out, Excel for Mac doesn't support Application.FileDialog. I ended up following the guidance from the link I posted above (using a combination of VBA and AppleScript) and it worked well. These links were also helpful:

Select files on a Mac (GetOpenFilename)
Test if it is a Mac or a Windows Machine

Thanks again for the help.