Solved Variable as range declaration (1 Viewer)

[CosmaL]

Dear friends,

i have the following statement in a loop.

For i = 1 To 20

  If (InStr(1, Range("E" & i).Value, "Valid", vbTextCompare)) And (Range("D" & i).Value >= 10) Then

  ...

  End if

Next

My question is if there's a way to declare as following:

Dim ValidVar as range

Dim ValidVal as range



set ValidVar = Range("E")

set ValidVal = Range("D")



For i = 1 To 20

  If (InStr(1, (ValidVar & i).Value, "Valid", vbTextCompare)) And (ValidVal & i).Value >= 10) Then

  ...

  End if

Next

I have tried any combination i could think of but i always get compilation or variables errors

Any ideas?

Thanks!!!!!

 

[Gasman]

I just use strings?

 

[Isaac]

Maybe what I am about to write is what Gasman meant, maybe not, not sure.

Remember: A range is a range, and is only a range. But range("Address") - in that, Address is a literal string value.

Dim i As Long
Dim ValidVar As String
Dim ValidVal As String
ValidVar = "E"
ValidVal = "D"

For i = 1 To 20
  If (InStr(1, Range(ValidVar & i).Value, "Valid", vbTextCompare) > 0) And (Range(ValidVal & i).Value >= 10) Then
  '...
  End If
Next

 

[Gasman]

Exactly @Isaac
Thank you, I was on my phone.

 

[CosmaL]

Thank you, it's working!

 

[Isaac]

Thank you, it's working!

Great, glad to hear it!

Please note you should fully qualify and not leave things like Range
What will happen as your VBA-developer-life goes on and gets more complex, more widely deployed, to more users, who are less predictable and known to you is this:

Someday a user will run that code (maybe), while they have 13 others Excel files open. Believe it or not, where your code says Range is not at all guaranteed to refer to the current workbook, current active worksheet, etc. If the user suddenly activates another worksheet while your code is running, "Range" might decide it means their currently active worksheet. I had these EXACT types of problems for about 3 months (of extreme psychological pain & suffering) until I learned I must fully qualify everything.

One way to think of it is, your Range should become: ThisWorkbook.Worksheets("Sheet1").Range

But of course, we use variables to shorten all that - and not only to shorten them, but to give you (the developer) Intellisense popups which are specific to your variable types, and extremely helpful to you while developing.

for example, what realistically I would do if I were you is:

dim ws as worksheet
set ws=thisworkbook.worksheets("sheet1")
ws.range.......... (etc)

And if you ever want to set a Workbook variable, make sure it is SET to ThisWorkbook, i.e.
dim wb as workbook
set wb=thisworkbook

On this piece of advice, if implemented with 100% diligence, you can build very robust pieces of code that deploy widely and keep working with a large, diverse, and unpredictable user base.

 

[CosmaL]

Great, glad to hear it!

Please note you should fully qualify and not leave things like Range
What will happen as your VBA-developer-life goes on and gets more complex, more widely deployed, to more users, who are less predictable and known to you is this:

Someday a user will run that code (maybe), while they have 13 others Excel files open. Believe it or not, where your code says Range is not at all guaranteed to refer to the current workbook, current active worksheet, etc. If the user suddenly activates another worksheet while your code is running, "Range" might decide it means their currently active worksheet. I had these EXACT types of problems for about 3 months (of extreme psychological pain & suffering) until I learned I must fully qualify everything.

One way to think of it is, your Range should become: ThisWorkbook.Worksheets("Sheet1").Range

But of course, we use variables to shorten all that - and not only to shorten them, but to give you (the developer) Intellisense popups which are specific to your variable types, and extremely helpful to you while developing.

for example, what realistically I would do if I were you is:

dim ws as worksheet
set ws=thisworkbook.worksheets("sheet1")
ws.range.......... (etc)

And if you ever want to set a Workbook variable, make sure it is SET to ThisWorkbook, i.e.
dim wb as workbook
set wb=thisworkbook

On this piece of advice, if implemented with 100% diligence, you can build very robust pieces of code that deploy widely and keep working with a large, diverse, and unpredictable user base.

Thank you Isaac! Your advise is very higly appreciated!