Doing MS Training in Excel, having Macro Issue

[Judi]

I am trying to work through the online training provided by Microsoft about
Excel 2003 and I have run into an issue with the Macro lesson.

Following the instructions exactly, I have entered this macro in to a module
in the spreadsheet:

Sub CountRows()

x = ActiveCell.Row
y = ActiveCell.Column
z = 0

Do While Cells(x, y).Value <> ""
x = x + 1
z = z + 1
Loop

MsgBox "There are " & z & " rows in the current range."

End Sub

When I try to run the macro, it tell me that variable x is undefined. I
tried going into the spreadsheet and placing the focus where they said and
running it from there, but I get the same message.

Please help me understand what I need to do to fix this. I am doing the
training so that I can understand this, but if the training is wrong, how am
I supposed to learn?

Feel free to talk to me like a child, I'm completely new to VBA.

Thank you to all who offer help.

 

[Bob Phillips]

You have Option Explicit at the head of your code. Use

Sub CountRows()
Dim x As Long
Dim y As Long
Dim z As Long

x = ActiveCell.Row
y = ActiveCell.Column
z = 0

Do While Cells(x, y).Value <> ""
x = x + 1
z = z + 1
Loop

MsgBox "There are " & z & " rows in the current range."

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chip Pearson]

You probably have an 'Option Explicit' line at the top of the code module
(before and outside of any procedure). This tells the compiler that all
variables must be explicitly declared with the 'Dim' statement before they
can be used in code. Thus, you need to declare your variables with code like

Dim x As Long 'or As whatever type is appropriate

If you omit the Option Explicit declaration (a very bad habit to get into),
the compiler will create an instance of the variable when it is first
encountered in code. So, you can do either of two things: remove the 'Option
Explicit' statement (bad idea), or explicitly declare the variables using
the 'Dim' statement (good idea).


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dave Peterson]

You have "option explicit" at the top of that module with the code. That means
you're telling VBA that you must declare all the variables that you use--and
that's a good thing.

Option Explicit
Sub CountRows()

Dim x As Long
Dim y As Long
Dim z As Long

x = ActiveCell.Row
y = ActiveCell.Column
z = 0

Do While Cells(x, y).Value <> ""
x = x + 1
z = z + 1
Loop

MsgBox "There are " & z & " rows in the current range."

End Sub


The Dim statements declare all 3 of those variables as Long's--whole numbers
between -2,147,483,648 to 2,147,483,647. (I looked at VBA's help!)

 

[RobWN]

Judi;
Not that this is much of an answer but I copied your code directly and it works perfectly for me
(Excel '03 on Xp).
Try adding these statements to the beginning of the module (before the "Sub" statement).

Dim x as long
Dim y as integer
Dim z as long

Sounds like you options are set to "Require Variable Declaration" (Tools->Options->Editor->Code
Settings")
This means that any variable you use must be declared (done via "Dim" statement).

HTH

 

[Judi]

Thank you to everyone who answered, I don't understand why the writers of the
course would not include this, and why they would not explain this to the
students.

Thank you all so much!

 

[Judi]

Now I have this one. I defined z as long, but VBA is telling me that MyCell
needs to be defined too, how do you define a cell?

Thanks again,
Judi <><


Option Explicit

Sub CountCells()

Dim z As Long

z = 0

For Each MyCell In Selection
z = z + 1
Next

MsgBox "There are " & z & " cells in the selection."

End Sub

 

[RobWN]

Judi;
As I noted, the declaration is the "Dim" statement.
The "As" portion tells Excel what the format is.
Try
Dim MyCell as Variant

 

[Judi]

Thank you so much...

Is there a list that tell you what to define each thing as, or is this
something you just learn from experience?

Thanks again!!

 

[RobWN]

The first thing to do, in my opinion is to go to the help function in VB and search/browse
through it.

Another way - place your cursor on the item (Dim-for ex) and hit F1.
This should open up the help topic for this item.

Have fun.

 

[Judi]

I will do that, thanks.

The first thing to do, in my opinion is to go to the help function in VB and search/browse
through it.

Another way - place your cursor on the item (Dim-for ex) and hit F1.
This should open up the help topic for this item.

Have fun.

 

[Dave Peterson]

You could use:
Dim myCell as Variant
but in this case, it's much more natural to use:
Dim myCell as Range

You may want to consider a book (or two).

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's books are very good to start.

 

[Judi]

Thank you, I will look into this too. I just started learning Access a month
ago and now I'm delving deeper into VBA and seeing how it can be used in
other programs too.

I'm very interested in this, and I am thinking about perhaps taking some
classes to learn more.

Thanks again, I always get good advice here.

 

[Gord Dibben]

Din MyCell as Range


Gord Dibben MS Excel MVP

 

[Bob Phillips]

Why so loud Gord?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gord Dibben]

LOL

Just another of my typoos