Very simple...early vba learner - range name in excel and in vba...

[wbrowse]

Hi,

===Module===
Dim zone2test As Range
Set zone2test = Sheets("moddate").Range("zone2test")

'Test cells in given range
For Each zone2test In zone2test
blablabla
next zone2test

===End module==

Is "zone2test" 2 differents objects in here? If yes, in both cases, is
"zone2test" an object everytime?

I am learning vba for excel on my own with little exposure to computer
language...

Thanks

 

[Ron Coderre]

You should always avoid ambiguous references. Otherwise, three months from
now you'll be cursing your self for writing code that's almost impossible to
re-figure out.

Something like this demonstrate better coding technique:

===Module===
Option Explicit 'Critical to put this at the top of each module

Sub MyTesting()
Dim rng2Test As Range
Dim cCell as range

Set rng2 = Sheets("moddate").Range("zone2test")

'Test cells in given range
For Each cCell In rng2Test.Cells
blablabla
next cCell
End Sub
===End module==

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[wbrowse]

It helps. Just to make sure, a range of cell defined from Excel with a
name is no different than a variable defined as a range in vba?

If yes, I totally agree with you, I should make a distinction so that
someone else can review my code...

===another subject====

MS Office 2007 sets Xml files by default and thus works under Xml
format. Can Vba handle Xml references as well as the old system which
I don't know the name?

If yes, how is a cell reference defined in an Excel Xml spreadsheet?

Vba codes could look at some point like Css arrays? Yes/No ... Yes
and no and you don't get wet

 

[Ron Coderre]

I'm reluctant to say that an Excel Range is *exactly* the same as a Range
Variable in VBA. But, what you can do to one, you can do to the other.

Basically, these 3 examples are equivalent

iRowCt = Range("MyRange").Rows.Count
.....or
iRowCt = [MyRange].Rows.Count
.....or
Set rngTest = Range("MyRange")
iRowCt = rngTest.Rows.Count

Which method you use depends on your particular circumstances. If the range
will be referenced several times within your code, then setting a Range
Variable is more efficient than entering the compete reference each for each
instance.

Regarding Excel 2007: I don't use it yet, so let's see what response you get
from someone who does.

***********
Regards,
Ron

XL2002, WinXP