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