Setting constant to range and worksheet

[jeffreybrown]

I need to establish a sheet as global and some ranges, but this method is not working. I get a debug on the first If statement under the Sub Test.

Any thoughts on making it work?

Option Explicit
Public Const SrcRng1 = "A1"
Public Const SrcRng2 = "A2"

Public Property Get ModelWorksheet() As Worksheet
    Const cfgSheet As String = "Sheet2"
    Set ModelWorksheet = ActiveWorkbook.Worksheets(cfgSheet)
End Property

Sub Test()
    If ModelWorksheet(Range(SrcRng1)) Then
        MsgBox True
    Else
        MsgBox False
    End If
End Sub

 

[shg]

What's the broad objective, Jeff?

Where does the Property sub appear?

 

[jeffreybrown]

The main objective is I use Range("A1") through Range("A6") on sheet (codename) sheet2 to hold the value of True after a certain macro has been run; thereby, preventing the macro from running again.

Mostly these macros are used for the initial setup of the spreadsheet.

So, at the top of a macro I use,

If Sheet2.Range("A1") then Exit Sub.

I use this in other macros and was just looking for a way to make the references global so if they ever need to be change it can be done in one place versus many.

 

[shg]

Maybe like this, but I don't like it because it uses the worksheet name rather than the codename...

Option Explicit
Public Const sRng As String = "A1"
Public Const sWks As String = "Sheet1"
 
Sub Test()
    MsgBox ActiveWorkbook.Worksheets(sWks).Range(sRng).Value
End Sub

EDIT: You could instead use a name:

Private Sub Workbook_Open()
    ActiveWorkbook.Names.Add Name:="Macro1DoneRun", RefersTo:=False
End Sub

... then

Sub Macro1()
    ' do stuff
    
    ActiveWorkbook.Names.Add Name:="Macro1DoneRun", RefersTo:=True
End Sub

 

[jeffreybrown]

Hi shg,

I was thinking about the names and have read up about them on cpearson and xldynamic.

I like the idea you suggested as this will eliminate the need for storing True on a worksheet cell, instead, refer to the name.

Sub Macro1()
    Dim s As String
    s = ThisWorkbook.Names("Macro1DoneRun").RefersTo
    s = Mid(s, 2)
    
    If s = True Then Exit Sub
    
    MsgBox "Macro has not run"

    ActiveWorkbook.Names.Add Name:="Macro1DoneRun", RefersTo:=True
End Sub

This works fine, but again, I would have to add the first part in more than one spot. In viewing Chip's page he has a Function that seems to fit, however, it gets to the Set R line and produces a R/T error 1004.

Is this because it isn't actually a range? Any thoughts on if I'm still going about this the wrong way?

Sub Macro12()
    
    If GetNameRefersTo("Macro1DoneRun") = True Then Exit Sub
    
    MsgBox "Macro has not run"

    ActiveWorkbook.Names.Add Name:="Macro1DoneRun", RefersTo:=True

End Sub

Function GetNameRefersTo(TheName As String) As String
    Dim s As String
    Dim HasRef As Boolean
    Dim R As Range
    Dim NM As Name
    Set NM = ThisWorkbook.Names(TheName)
    On Error Resume Next
    Set R = NM.RefersToRange
    If Err.Number = 0 Then
        HasRef = True
    Else
        HasRef = False
    End If
    If HasRef = True Then
        s = R.Text
    Else
        s = NM.RefersTo
        If StrComp(Mid(s, 2, 1), Chr(34), vbBinaryCompare) = 0 Then
            ' text constant
            s = Mid(s, 3, Len(s) - 3)
        Else
            ' numeric contant
            s = Mid(s, 2)
        End If
    End If
    GetNameRefersTo = s
End Function

 

[shg]

Rather than get too exotic, you can just do

If Evaluate("Macro1DoneRun") Then

or (in one of few appropriate examples of brackets as a shortcut to Evaluate, IMO),

If [Macro1DoneRun] Then

 

[jeffreybrown]

shg,

This works great...many thanks.