Getting the value of a Name with no range-CodePudding

I have a workbook, UserFileBook, that contains the Name 'Version' which simply refers to a number (it does not refer to any range, in the Name Manager it just 'Refers to =5'). I am trying to compare this number with the version number of a different workbook. When I had UserFileBook's 'Version' as an actual named range (it referred to cell C1 which had the value of 5 in it) everything worked fine. But IdiotUser can edit that value or delete it right on the sheet, so I made it just refer to a number so it can only be edited through the manager. Is there a way for me to obtain the value of that Name and alter it from another WB now? Currently I'm trying this:

Sub CheckVersionNumber(Vers As Long)

'Checks to see if this version is compatible with the UW version
Dim wb As Workbook
Set wb = UserFileBook

Dim UWVers As Long
UWVers = wb.Names("Version").Value 'Breaks here

'Version information is in the range "Version" on UW
If UWVers < Vers Then
    GoTo LowerVersion
    
Else
    If wb.Names("Version") > Vers Then  'tried this originally and also breaks, also if .Value is added
        GoTo UpperVersion
    End If
End If

Exit Sub

I also tried comparing to wb.Range("Version"), and even wb.Worksheets("Sheet 1").Range("Version) but those didnt work either. How can I reference (and alter) the value of "Version" in the USerFileBook if it doesn't refer to a range?

CodePudding user response：

You cannot use .Range because Version is not a range. It's a named formula.

But you can evaluate it:

UWVers = wb.Worksheets(1).Evaluate("Version")

To update the named formula with a different value, say 999:

wb.Names.Add "Version", 999

As an aside... since you are having difficulties with users changing your solution settings you may wish to explore utilizing CustomXMLParts.Add to store your Version. There is no user interface to CustomXMLParts, but they are stored in the workbook. The only way to access them is through code. A normal user will NEVER see your version number stored this way. In fact most advanced developers would never find it either.

CodePudding user response：

You can use wb.Names("Version").Value but it returns a string >> =999. Therefore you have to omit the equal-sign before assigning to a long value.

If you want to hide the name from the normal user, you can set the visibility of the name - when adding it the first time - to false. Then the name doesn't show up in the name manager.

I would create a function and a sub.


'---> get current version
Public Function getVersion(wb As Workbook, Optional throwError As Boolean = False) As Long
    
    On Error Resume Next    'in case version does not exist function will return 0
        
    'remove =-sign as from returned value to return a long value
    getVersion = Replace(wb.Names("Version").Value, "=", vbNullString)
    
    'if useful you could throw an error here
    If Err <> 0 And throwError = True Then
        Err.Clear: On Error GoTo 0
        Err.Raise vbObjectError, , "Version hasn't been set for this workbook"
    End If
    
    On Error GoTo 0
End Function


'--->> set version
Public Sub setVersion(wb As Workbook, newVersion As Long)

    On Error Resume Next    'in case version doesn't yet exists
    wb.Names("Version").Value = newVersion
    
    If Error > 0 Then
        Err.Clear: On Error GoTo 0
        'Version name does not yet exist --> add as invisible name
        wb.Names.Add "Version", "=" & newVersion, Visible:=False
    Else
        On Error GoTo 0
    End If

End Sub

This is how you use them:

Sub testVersionAsNameConstant()

    Debug.Print getVersion(ThisWorkbook, False)
    
    'comment this out if you don't want to see the error
    Debug.Print getVersion(ThisWorkbook, True)

    setVersion ThisWorkbook, 1
    Debug.Print getVersion(ThisWorkbook), "should be 1"
    
    setVersion ThisWorkbook, 2
    
    Dim checkValue As Long
    checkValue = 1
    Debug.Print getVersion(ThisWorkbook) > checkValue, "should be true"
    Debug.Print getVersion(ThisWorkbook) = checkValue, "should be false"
    
End Sub