Given the code below, in every case while an error is thrown at runtime the GetAcive variable is not noticed under Compile action. My typing is my fault, but shouldn’t this come up with an undeclared symbol error?
Option Explicit
Option Base 0
Sub TestSheetName
MsgBox SheetName(1)
End Sub
'Get name of 'current' sheet
'May require a full recalculation after a sheet name change
'Works as expected when used as a UDF
'BUT NOTE: ActiveSheet and sheet calling this code may not be the same
' so use with some caution
'Sheet index is assumed to be 1-based
Function SheetName(Optional SheetIndex As Variant)
Dim GetActive As Boolean
Dim Result As Variant
Dim Row As Integer
Dim Column As Integer
Dim Index as Variant
Const ErrorText = "[No sheet]"
On Error Goto Handler
GetActive = False
If IsMissing(SheetIndex) Then
GetActive = True
ElseIf IsEmpty(SheetIndex) Then
GetActive = True
ElseIf IsNull(SheetIndex) Then
GetActive = True
EndIf
If GetAcive Then 'Bad symbol
SheetName = ThisComponent.CurrentController.Activesheet.Name
Exit Function
EndIf
If IsArray(SheetIndex) Then
ReDim Result(LBound(SheetIndex) to UBound(SheetIndex), LBound(SheetIndex,2) to UBound(SheetIndex,2))
For Row = LBound(SheetIndex,1) to UBound(SheetIndex,1)
For Column = Lbound(SheetIndex,2) to UBound(SheetIndex,2)
If IsNumeric(SheetIndex(Row, Column)) Then
'Empty or text entries in SheetIndex() will result in "", the empty string
Index = SheetIndex(Row, Column) - 1
If (Index >= 0) And (Index < ThisComponent.Sheets.Count) Then
Result(Row, Column) = ThisComponent.Sheets(Index).getName
Else
Result(Row, Column) = ErrorText
End If
End If
Next Column
Next Row
SheetName = Result
Else
If IsNumeric(SheetIndex) Then
'Normal case for looking up sheet name from 1-based index
Index = SheetIndex - 1
If Index <= ThisComponent.Sheets.Count - 1 Then
SheetName = ThisComponent.Sheets(Index).getName
Else
SheetName = ErrorText
EndIf
Else
SheetName = ErrorText
End If
End If
Exit Function
Handler:
SheetName = ErrorText
End Function