Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range naming | Excel Discussion (Misc queries) | |||
Naming a range versus formula in Excel | Excel Programming | |||
naming a range | Excel Programming | |||
Naming a range | Excel Programming | |||
VB Code Naming a Range (range changes each time) | Excel Programming |