usiung a variable to refer to a range

[Tuneman]

I want to be able to refer to a certain range as a variable, so when my if statements below decided which ranges to assign to that variable, I can simply use that single variable name instead of writing it over and over again. Any ideas?

Sub varrates()
Dim t, x As Integer
Dim d, r As Range
x = 1


If Range("designatedmaturity") = "1 Month" Then

  d = Worksheets("Interpolated").Range("p4:p14622")
  r = Worksheets("Interpolated").Range("q4:q14622")

ElseIf Range("designatedmaturity") = "3 Month" Then
  d = Worksheets("Interpolated").Range("t4:t14622")
  r = Worksheets("Interpolated").Range("u4:u14622")

ElseIf Range("designatedmaturity") = "6 Month" Then
  d = Worksheets("Interpolated").Range("x4:x14622")
  r = Worksheets("Interpolated").Range("y4:y14622")

End If


End Sub

[Leith Ross]

Hello Tuneman,

You have declared the variables "d" and "r" as Range Objects which is correct. The step in assigning an object variable to an existing Object is to use the Set statement.

Sub varrates()
Dim t, x As Integer
Dim d, r As Range
x = 1


If Range("designatedmaturity") = "1 Month" Then

  Set d = Worksheets("Interpolated").Range("p4:p14622")
  Set r = Worksheets("Interpolated").Range("q4:q14622")

ElseIf Range("designatedmaturity") = "3 Month" Then
  Set d = Worksheets("Interpolated").Range("t4:t14622")
  Set r = Worksheets("Interpolated").Range("u4:u14622")

ElseIf Range("designatedmaturity") = "6 Month" Then
  Set d = Worksheets("Interpolated").Range("x4:x14622")
  Set  r = Worksheets("Interpolated").Range("y4:y14622")

End If


End Sub

[jaslake]

Hi Leith

I've oftened wondered about this

Dim d, r As Range

According to http://www.cpearson.com/excel/DeclaringVariables.aspx he says

Pay Attention To Variables Declared With One Dim Statement
VBA allows declaring more than one variable with a single Dim statement. I don't like this for stylistic reasons, but others do prefer it. However, it is important to remember how variables will be typed. Consider the following code:
Dim J, K, L As Long You may think that all three variables are declared as Long types. This is not the case. Only L is typed as a Long. The variables J and K are typed as Variant. This declaration is functionally equivalent to the following:
Dim J As Variant, K As Variant, L As Long You should use the As Type modifier for each variable declared with the Dim statement:
Dim J As Long, K As Long, L As Long

According to the Author, in this particular case d would be declared as a variant and r as a range. What's correct?

I see this syntax used regularly and I wonder...is this the correct?

John

[Leith Ross]

Hello John,

Good catch. I missed that. Unless a variable is given a type using the "As" statement, it will by default be declared a Variant.

[jaslake]

Hi Leith

Wasn't trying to "catch". I see this syntax so often and was just wondering, for my personal edification, "what's correct".

Thanks for the feedback.

John

[Leith Ross]

Hello John,

Many people mistakenly think that the final type will be assigned to all the preceding variables. That is why you see it so often. It isn't necessarily wrong as a Variant will assume the type of the variable it is assigned to. However, not declaring the variable explicitly may not yield the expected results.