CODEWITHSUNDEEP
excelvbaconstantsexcel-2016
User1974
User1974

Reputation: 386

CodeName as constant?

I have multiple functions/procedures in an Excel 2016 VBA module that reference a CodeName called DataSheet.

Public Sub LoadRecords()
    '***Caution: The 'DataSheet' CodeName is hardcoded (in multiple places).***
    With DataSheet.ListObjects(tblName)
        'Some code 
        Next
    End With
End Sub

Function ConcatVars(RowNum As Integer) As String
    '***Caution: The 'DataSheet' CodeName is hardcoded (in multiple places).***
    For Each Column In DataSheet.ListObjects(tblName).ListColumns
        'Some code
    Next
End Function

Question:

Is there a way to declare the CodeName as a constant?

What I've tried:

If I put the codeName in a constant:

Public Const codeName = "DataSheet"

And try to reference the constant:

With codeName.ListObjects(tblName)

I get this error:

Compile Error: Invalid qualifier

Upvotes: 1

Views: 121

Answers (1)

T.M.
T.M.

Reputation: 9948

Just define a function (and avoid to overload existing CodeName property with identical function or variable name):

Function myCodeName() As Worksheet
Set myCodeName = DataSheet          ' << change to your project's Code(Name) 
'Debug.Print myCodeName.CodeName
End Function

Upvotes: 2

Related Questions