Naming a Range for use in a Non-Formula
 

Greetings. I have the following "UseNames" procedure in which I am trying to
give a range a meaningful name such that the name can be used in a
non-formula:

Sub UseNames()

' If I have the following code:

Dim Month As String

If Range("$G$1") = 28 Then
Month = "February"
End If

' In order to make the code more explanatory I could also code this as:

Dim vNrDays As Long
vNrDays = Range("$G$1")

If vNrDays = 28 Then
Month = "February"
End If

' which requires an extra statement and extra memory and runs more slowly.

' I thought I could also do the following:
Range("$G$1").Name = ActiveSheet.Name & "!NrDays"

If NrDays = 28 Then
Month = "February"
End If

' but when I execute this, VBE tells me: "Compile Error. Variable not
defined."
' I then recalled that named ranges can only be used in formulas, and I was
' indeed able to use the name NrDays in a formula.
' Is there any way (other than storing the range in a variable as above)
' of giving the range, Range("$G$1"), a meaningful name such that it can be
' used in non-formula logic, like in the IF-THEN code below:

If Range("$G$1") = 28 Then
Month = "February"
End If

' NrDays is so much more explanatory and meaningful than Range("$G$1")

End Sub
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

Naming a Range for use in a Non-Formula
 

Like so

If Range("NrDays") = 28 Then
Month = "February"
End If


--
__________________________________
HTH

Bob

"MichaelDavid" wrote in message
...
Greetings. I have the following "UseNames" procedure in which I am trying
to
give a range a meaningful name such that the name can be used in a
non-formula:

Sub UseNames()

' If I have the following code:

Dim Month As String

If Range("$G$1") = 28 Then
Month = "February"
End If

' In order to make the code more explanatory I could also code this as:

Dim vNrDays As Long
vNrDays = Range("$G$1")

If vNrDays = 28 Then
Month = "February"
End If

' which requires an extra statement and extra memory and runs more slowly.

' I thought I could also do the following:
Range("$G$1").Name = ActiveSheet.Name & "!NrDays"

If NrDays = 28 Then
Month = "February"
End If

' but when I execute this, VBE tells me: "Compile Error. Variable not
defined."
' I then recalled that named ranges can only be used in formulas, and I
was
' indeed able to use the name NrDays in a formula.
' Is there any way (other than storing the range in a variable as above)
' of giving the range, Range("$G$1"), a meaningful name such that it can
be
' used in non-formula logic, like in the IF-THEN code below:

If Range("$G$1") = 28 Then
Month = "February"
End If

' NrDays is so much more explanatory and meaningful than Range("$G$1")

End Sub
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

Naming a Range for use in a Non-Formula
 

Hi Bob:
Thanks for your help. Your "Like So" is perfect. None of the Excel VBA
books I have (and I have about 6) gives this usage.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick


"Bob Phillips" wrote:

Like so

If Range("NrDays") = 28 Then
Month = "February"
End If


--
__________________________________
HTH

Bob

"MichaelDavid" wrote in message
...
Greetings. I have the following "UseNames" procedure in which I am trying
to
give a range a meaningful name such that the name can be used in a
non-formula:

Sub UseNames()

' If I have the following code:

Dim Month As String

If Range("$G$1") = 28 Then
Month = "February"
End If

' In order to make the code more explanatory I could also code this as:

Dim vNrDays As Long
vNrDays = Range("$G$1")

If vNrDays = 28 Then
Month = "February"
End If

' which requires an extra statement and extra memory and runs more slowly.

' I thought I could also do the following:
Range("$G$1").Name = ActiveSheet.Name & "!NrDays"

If NrDays = 28 Then
Month = "February"
End If

' but when I execute this, VBE tells me: "Compile Error. Variable not
defined."
' I then recalled that named ranges can only be used in formulas, and I
was
' indeed able to use the name NrDays in a formula.
' Is there any way (other than storing the range in a variable as above)
' of giving the range, Range("$G$1"), a meaningful name such that it can
be
' used in non-formula logic, like in the IF-THEN code below:

If Range("$G$1") = 28 Then
Month = "February"
End If

' NrDays is so much more explanatory and meaningful than Range("$G$1")

End Sub
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick