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!
I have posted this on another forum here:http://www.mrexcel.com/forum/e…trouble-figuring-out.html
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(y) 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