When you are referring to named ranges in your code never use the Range abbreviation [MyNamedRange].
Always prefix your enumeration values with a "en" prefix so they are not accidentally confused with named ranges.

Public Enum enEnumeration 
   MyFirstValue = 1 '0 is the default, can be overwritten to any value
End Enum

Public Sub Testing()
Dim svalue As String
' Debug.Print [MyNamedRange1].Address 'this will not compile with an 'Invalid Qualifier' error
' Debug.Print [MyNamedRange2].Address
' svalue = Application.VLookup(20, [MyNamedRange2], 2, False)
' Debug.Print svalue ' = two
'this will compile but will generate a 'Type Mismatch' when actually run
'This is becuase the enumeration is being used first
' svalue = Application.VLookup(20, [MyNamedRange1], 1, False)
'this can be tested by running the following line
'if the named range was being used this would generate a 'Type Mismatch' error
    Debug.Print [MyNamedRange1] ' = 3
End Sub

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext