Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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) "Judi<<" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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) "Judi<<" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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!) Judi<< wrote: 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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 -- Regards Rob "Judi<<" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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<<" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
Judi;
As I noted, the declaration is the "Dim" statement. The "As" portion tells Excel what the format is. Try Dim MyCell as Variant -- Regards Rob "Judi<<" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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" wrote: Judi; As I noted, the declaration is the "Dim" statement. The "As" portion tells Excel what the format is. Try Dim MyCell as Variant -- Regards Rob "Judi<<" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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. -- Regards Rob "Judi<<" wrote in message ... 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" wrote: Judi; As I noted, the declaration is the "Dim" statement. The "As" portion tells Excel what the format is. Try Dim MyCell as Variant -- Regards Rob "Judi<<" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
I will do that, thanks.
"RobWN" wrote: 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. -- Regards Rob "Judi<<" wrote in message ... 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" wrote: Judi; As I noted, the declaration is the "Dim" statement. The "As" portion tells Excel what the format is. Try Dim MyCell as Variant -- Regards Rob "Judi<<" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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<< wrote: 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 -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
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. "Dave Peterson" wrote: 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<< wrote: 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 -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
Din MyCell as Range
Gord Dibben MS Excel MVP On Wed, 16 Jul 2008 11:25:01 -0700, Judi<< wrote: 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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
Why so loud Gord?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Din MyCell as Range Gord Dibben MS Excel MVP On Wed, 16 Jul 2008 11:25:01 -0700, Judi<< wrote: 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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Doing MS Training in Excel, having Macro Issue
LOL
Just another of my typoos On Wed, 16 Jul 2008 22:26:32 +0100, "Bob Phillips" wrote: Why so loud Gord? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro- sort issue in Excel2003 | Excel Worksheet Functions | |||
Macro training | Excel Discussion (Misc queries) | |||
Excel 2007 Macro/Link Issue (apologies for cross-posting) | Links and Linking in Excel | |||
Excel training | Excel Discussion (Misc queries) | |||
Training in Excel | Excel Discussion (Misc queries) |