CODEWITHSUNDEEP
vbaexcelmacros
B. Torreano
B. Torreano

Reputation: 13

Excel VBA Retrieve Constant from Worksheet Code Fails When New Code is Written

In an attempt to retrieve constants from each worksheet in some reporting workbooks I use, three years ago I wrote some code that gets included in each worksheet. Here's an example of the code:

    Option Explicit
    ' Determine the type of worksheet
    Private Const shtType As String = "CR"

    Public Function GetShtType()
        GetShtType = shtType
    End Function

In other code that gets the values from the worksheets for processing, the following section of code is used, where 'wksToCheck' is the worksheet in question. This code is stored in a personal macro workbook, not in the workbook with the worksheet code:

    ' Get the sheet 'type' if it has one
    On Error Resume Next
    shtType = wksToCheck.GetShtType()
    If Err.Number <> 0 Then
        ' We do not have a type
        shtType = "Unknown"
        Err.Clear
    End If ' Err.Number...
    On Error GoTo Error_BuildTemplateWbk

My problem is, I use the code above to process workbooks several times a week, and I have for the past three years. Now, I am trying to write some new code with the above block to process the report workbooks in a different way. However, when I run code with the above block now, I get a 'Method or Data Member Not Found' error on the '.GetShtType()' portion of the code. I cannot compile the code and of course, consequently, the code doesn't work. I have tried adding the worksheet code to a worksheet in the macro workbook to see if that would fix the problem. It hasn't. Does anyone have any ideas? I am running Excel 2013 on a Windows 7 PC. Any ideas?

Brian

Upvotes: 0

Views: 149

Answers (1)

David Zemens
David Zemens

Reputation: 53653

Using late-binding, should avoid the error, Dim wksToCheck As Object, but you'll lose the intellisense.

If you're open to alternatives, you may have better luck simply using the CallByName function, or using worksheet's CustomProperties.

Using CallByName preserves backwards compatibility with your older workbooks if needed:

shtType = CallByName(wksToCheck, "GetShtType", VbMethod)

Or, using CustomProperties instead of a custom method, in your worksheets:

Private Sub Worksheet_Activate()
Const PropName$ = "ShtType"
Const ShtType$ = "CR"
On Error Resume Next
Me.CustomProperties(PropName) = ShtType$
If Err.Number = 13 Then
    Me.CustomProperties.Add "PropName", ShtType
End If
End Sub

Then,

' Get the sheet 'type' if it has one
On Error Resume Next
shtType = wksToCheck.CustomProperties("ShtType")
If Err.Number = 13 Then
    ' We do not have a type
    shtType = "Unknown"
    Err.Clear
End If ' Err.Number...
On Error GoTo Error_BuildTemplateWbk

Upvotes: 0

Related Questions