Solved

Newbie VBA Question

Posted on 2010-09-07
14
637 Views
Last Modified: 2013-12-25
Hello,

I am very new to VBA and programming in general.  I am using this website right now to learn VBA.

http://excelexperts.com/Learn-VBA

I finished #1 and #2 successfully.  The website provides a good step-by-step outline of what to do.  But what it doesn't tell me is why to do that.  It provides no analysis of the code or anything.

Here is the code for #2:


Sub DisplayBiggest()
maxWeight = 0
For Each theCell In Range("D25:D34")
    If theCell.Value > maxWeight Then
        maxWeight = theCell.Value
        pigNumber = theCell.Offset(0, -1).Value
    End If
Next
MsgBox "The biggest piglet is number: " & pigNumber & " weighing:" & maxWeight
End Sub
-------------------------

I typed it in to the VBA editor, and sure enough - I did get a popup saying pig # 1 weights the most at 10.  But I do not understand how that code works.  Could someone please help me understand it, line to line?


Thanks!
0
Comment
Question by:Kenny537
  • 3
  • 3
  • 3
  • +2
14 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 33619261
The code through goes each cell in D25:D34 and if its value is greater than Maxweight (which starts at zero) then maxweight is assigned the value of that cell and pignumber is assigned the value of the cell to the left (e.g. C25 - offset 0 rows and -1 columns). At the end a message appears giving the biggest value (maxweight) and its corresponding entry to the left.

Does that help?
0
 
LVL 3

Accepted Solution

by:
DanMerk earned 125 total points
ID: 33619265
Not a problem! I'll go line by line.

Sub DisplayBiggest()
-Here we are entering our program

maxWeight = 0
-This is setting up a temporary variable to track the largest value

For Each theCell In Range("D25:D34")
-This begins a loop that goes through each cell (TheCell) between D25 -> D34

    If theCell.Value > maxWeight Then
-This is a conditional statement which basically says "If the cell I'm looking at right now has a value greater then the largest number I've seen, then run the next couple lines of code. Coincidently, the very first cell (in this case D25), will always cause this statement to be true.

        maxWeight = theCell.Value
- If the above conditional statement was true, that is the current cell has the largest value, then it will set our temporary variable maxWieght to the value of the current cell.

        pigNumber = theCell.Offset(0, -1).Value
-If the above conditional statement was true, that is the current cell has the largest value, this is setting the pigNumber to the value of the cell to the left. This is most likely the trickiest part of the code. The reason it is doing this is because the loop is going through all of the cells with weights. To get to the Pig number, it must look to the cell to the left or theCell.Offset(0, -1). If the column of Pig numbers were to the right of the weights, it would be theCell.Offset(0, 1).

    End If
-This simply ends the if statement

Next
-This simply ends the for statement

MsgBox "The biggest piglet is number: " & pigNumber & " weighing:" & maxWeight
-This displays the message.

End Sub
-This exits our program
0
 
LVL 24

Assisted Solution

by:broomee9
broomee9 earned 125 total points
ID: 33619271
Sub DisplayBiggest() 'Declare the procedure with the key word 'Sub'.  Sub will allow you to call this from the macros in the excel menu.  You can make it private or public as well, to hide the scope of the procedure.  You can also make it a Function instead, which will allow
you to pass variables to it and also keeps it hidden from the macro menu in Excel (DisplayBiggest is just the name of the procedure and can be any text value (no numbers and no special characters, including no spaces)).


maxWeight = 0  'maxWeight is a variable, that is not declared.  You should always declare you variables.  It will help with simplicity for other developers and it will also allow you to preset the data being stored in it.  This should be set as a double, so that way you can have decimals.  This should be below your declaration above:  Dim maxWeight as Double
maxWeight is being initialized to 0.

For Each theCell In Range("D25:D34") 'theCell is also a variable which is not being declared.  It should be declared as a range.  This is using the For Each Next loop structure.  For Each something In somewhere  will loop through each of the somethings in the somewhere.  This specific example is looping through each cell in the range specified D25:D34.  So each time it loops, it will go to the next cell.  1st iteration will be D25, 2nd is D26, 3rd is D27, 4th is D28 and it will continue all the way to D34.


    If theCell.Value > maxWeight Then  'This is using an If statement structure (If Then Else).  If the value of the variable called 'theCell' is greater than the value of maxWeight which is 0, then do the following condition.


        maxWeight = theCell.Value 'this is the following condition, this and the next line.  This changes the value of maxWeight to whatever is in the current cell (ie. the value of D25)
        pigNumber = theCell.Offset(0, -1).Value 'this is setting the value of the pigNumber variable to the previous column (ie. if your loop is D25, this is setting pigNumber to the value in C25).
    End If 'Ends the if statement.  This is required.
Next 'loops again
MsgBox "The biggest piglet is number: " & pigNumber & " weighing:" & maxWeight 'This is a message box and will display whatever you enter.  In this case you're entering the text "The biggest piglet is number: " and the concatenating (&) the value of pigNumber, and then concatenting the text "weighing:" and then finally concatenating the value of the maxWeight variable.

End Sub 'Ends your procedure.  If it was a function, you'd have End Function instead of End Sub.

Hope this helps.
0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 125 total points
ID: 33619278
Ok, step by step description:

Sub DisplayBiggest()
Name of the sub for returning the highest value

maxWeight = 0
Variable that will hold the highest value

For Each theCell In Range("D25:D34")
This will start a cycle. In the range specified (d23:d34) each cell that exists will be stored in the variable theCell

If theCell.Value > maxWeight Then
Checks to see if the value of the current cell (remember, this is a cycle, so you're checking one cell at a time) is bigger than the one in the variable maxWeight

        maxWeight = theCell.Value
        pigNumber = theCell.Offset(0, -1).Value
Now, this part of the code only happens when the above check is true. If it isn't, then nothing happens. But if it is, then the variable maxWeight will be assigned the value of the current cell. And the variable pigNumber will hole the value of the cell to the left (Offset(0,-1) means MoveFromThisCell(0 Rows, -1 Columns))

    End If
Next
Ends the if, next loops for the next cell.

MsgBox "The biggest piglet is number: " & pigNumber & " weighing:" & maxWeight
End Sub
Sends a messagebox with the higher number, ends the sub.

Any other doubts, feel free to ask :)
0
 

Author Comment

by:Kenny537
ID: 33619708
Thanks for the quick responses guys.

Stephen -

That does help considerably in telling me what the code is doing - thanks!  But to take it a step further, I also want to know line by line what is going on so that I can write this code from scratch if I wanted to.

Dan -

Thanks, this explains it in simple terms.

How does setting it to 0 make it track the largest value?  

For the conditional statement - it will only be true for D25 and no other ones then, right?  You are saying that it is scanning through the cells.  Since the conditional statement is only true for D25, it will only do the next couple conditional lines of code for D25, and not even do them for D26-D34, correct?

For the msgbox code - why are there 3 ampersands and three quote signs.  Can't make any sense out of it!

Broomee -

So what does sub mean exactly?  Is it short for subroutine?  What does subroutine mean?  It's just naming the macro, right?  How come there are two paranthesis after the name?  How do I make it private or public?  How do I make it a function?  So a function allows me to incorporate variables into the subroutine?  I'd understand it better if you gave an example.  

I am sorry, I do not understand your next paragraph about maxWeight.  Keep in mind that I am new to this.  I think all this "being declared" and "not being declared" is confusing me.  What does all that mean?

Cluskitt -

You are saying it is a cycle - but for the If statement, you are saying it is going through each cell one by one to check if the value of the current cell is bigger than maxWeight.  But maxWeight is equal to 0 .. so they're all bigger than 0.  I do not understand what this accomplishes.
0
 
LVL 24

Assisted Solution

by:StephenJR
StephenJR earned 125 total points
ID: 33619835
An example usually helps best. Say a sample of cells have these values
C1="Fred"      
C2="George"
C3="Harry"
D1=5
D2=4
D3=6

First the loop checks D1. It is bigger than maxweight (=0) so maxweight becomes 5 and pignumber becomes "Fred"
Then it checks D2. 4 is less than maxweight (=5) so it skips everything until the Next line.
Then it checks D3. 6 is more than maxweight (=5) so maxweight becomes 6 and pignumber becomes "Harry"

The ampersand joins text together (sure the guys above explained this) so the string
"The biggest piglet is number: " & pigNumber & " weighing:" & maxWeight
is
"The biggest piglet is number: " & "Harry" & " weighing:" & 6
which is
"The biggest piglet is number: Harry weighing: 6"

If you don't have experience of basic VBA, your enquiry is more general than this question. Google basic VBA or something or the guys can will have good sources I'm sure.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 33619845
Btw, I realise my example makes no sense as Harry is not a number, but hopefully you get the gist?!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:DanMerk
ID: 33620217
Hi,

Here are the answers to your questions:

How does setting it to 0 make it track the largest value?  

-Since we are tracking pigs, we know that there will be no pigs that weigh zero or less pounds. The variable MaxWeight is really just being set to a value that we know will be less then any of the pigs. If the value of MaxWeight were to be set to 20, then our program would not work if any of the pigs were 20 pounds or less.

For the conditional statement - it will only be true for D25 and no other ones then, right?  You are saying that it is scanning through the cells.  Since the conditional statement is only true for D25, it will only do the next couple conditional lines of code for D25, and not even do them for D26-D34, correct?

-No. The FOR statement will scan through every cell from D25 -> D34. Since MaxWeight is set to a value that has to be lower than D25 (since a pig weighing zero or -1 pounds is silly :P), we know that the D25 will make the IF statement true which will trigger the inner code to run (setting MaxWeight and pigNumber). Once this is done, the FOR statement will begin to look at D26, and if D26 is larger, then it will set MaxWeight to it and set pigNumber accordingly. If D26 is smaller, the FOR statement will move on to D27. This process will continue until the last cell is checked which will be D34.

For the msgbox code - why are there 3 ampersands and three quote signs.  Can't make any sense out of it!

-This is really just a syntax rule thing (meaning its just a grammar rule that has to be remembered). Whenever you want to output a string of characters, like a sentence or phrase, you must put them in quotes. In this case we want to output  "The biggest piglet is number: " and " weighing:". Ideally, we want to display the pig number after the first phrase and the MaxWeight after the second one and have this come out as a nice sentence. To combine phrases with variables, or vise vera, you need to place an & between them; therefore, we have  "The biggest piglet is number: " & pigNumber & " weighing:" & maxWeight. Like I said, this is a grammar rule of this language, so there really isn't any deep meaning in why this is done the way it is.
0
 
LVL 3

Expert Comment

by:DanMerk
ID: 33620478
This is BTW kind of post to answer some of your questions:

So what does sub mean exactly?  Is it short for subroutine?  What does subroutine mean?  
-Sub does stand for subroutine which really means a function.

It's just naming the macro, right?  
-Well, not really. Subroutines are the evolution of Macros. While they can provide the same functionality, Subroutines can do much more while being more elegant than macros.

How come there are two paranthesis after the name?  
-This is where you could pass in variables for a subroutine to work on. If this sounds weird or foreign, I would recommend shelving this until a later lesson. You should just know that you can add items between them to generate more powerful and useful functions, and you will learn about them soon.

How do I make it private or public?  
-As I've said, I would wait on this for a later lesson.

How do I make it a function?  So a function allows me to incorporate variables into the subroutine?  
-As I've said, I would wait on this for a later lesson.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 33620594
>>So what does sub mean exactly?  Is it short for subroutine?
Yes, it is short for subroutine.

>>What does  subroutine mean?
A subroutine is a procedure in VBA, which allows you to execute code within the procedure.

>> It's just naming the macro, right?  
Nope, it does a lot more than name the macro.  It allows you to pass values to the procedure and it also allows you to return values if you use a function.

>>How come there are  two parenthesis after the name?  
The parenthesis are standard syntax.  A lot of the time they are left blank, but if you want to pass values to the procedure, then in the parenthesis is where you would enter this.  Then you can use the variable in between the parenthesis in the code of your procedure.

   Example:  Sub CalculateTaxes(taxRate as double)
    Now you can use the variable taxRate in your procedure.  And you can have it passed from another procedure that actually calculates your tax rate
   
>>How do I make it private or public?  
Simply by adding the word public or private in front of it:

Public Sub EveryoneCanSeeThis()
Private Sub CokeSecretRecipe()

>>How do I make it a function?  
This is also a simple one, you change sub to function and set it to return a specified data type:

    Example:  Function CalculateTaxes(taxRate as Double) As Double

>>So a function allows me to incorporate  variables into the subroutine?
A function returns a value, and a subroutine does not.  Both a subroutine and a function can have variables passed to it.

   Example:  Function CalculateTaxes(taxRate as Double) As Double
     This will have the taxRate passed into the function, and the function will return a double.
So for instance, if 15.2% is passed in, then based on the code of your procedure, you could return a tax amount for a certain person (ie. $2,346.79).




>> I am sorry, I do not understand your next paragraph  about maxWeight.  Keep in mind that I am new to this.  I think all this  "being declared" and "not being declared" is confusing me.  What does  all that mean?

Declare a variable is simply setting up the data type it will hold throughout the procedure.  You can choose to do this or you can choose to skip it.  I would suggest, as would most other experts, that it is best to always declare your variables.  This way it is much easier to keep track of and you won't run into mistakes down the line with typos, since variables are case sensitive.  So if you declare a variable MaxWeight, then it will always appear with a capital M and W.  If you are typing it in later down in the code and it doesn't turn to capital, it's most likely because you've spelt it wrong.  In addition, if you use the optional "Option Explicit" command at the top of your module, then you have to declare the variables.  This will make more sense when you see the example I've put together.

>> I'd understand it better if you gave an  example.  
See attached.  Notice that there are two modules in the attached to explain the above.

Example.xls
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33620649
> You are saying it is a cycle - but for the If statement, you are saying it is going through each cell one by one to check if the value of the current cell is bigger than maxWeight.  But maxWeight is equal to 0 .. so they're all bigger than 0.  I do not understand what this accomplishes.

No, you misunderstood. The For is the cycle. The If is a check that is run once for each of the loops in the for. So, the first time the code reaches the for, it will assign theCell=D25.
It will then run the if. Cell D25 has a value of (for example) 5. 5 is bigger than 0 (this is one reason of setting the maxWeight to 0. The other is that undeclared variables are assigned an address in memory. And that address may already have some value in it. So, if you're going to use a variable in a check before ever assigning any value to it, always assign some value first).

Anyway, since 5>0, it will then run the 2 statements in the if block. maxWeight=5, pigNumber with the value in C25. It will then reach the "Next" line. This will continue the loop. First, it will silently check if the condition to exit the loop is reached. Since theCell doesn't equal D34, it will then assign theCell with the next one, D26. It will then run the if again, but maxWeight will already be 5. If D26 is 3, it will simply skip to End If. Where it will loop again. On and on until all are done.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 33620701
Here's a good reference to look at too, when you're trying to get a grasp of all this stuff we're throwing at you:

http://www.excelfunctions.net/VBA-Functions-And-Subroutines.html
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33620711
> I am sorry, I do not understand your next paragraph  about maxWeight.  Keep in mind that I am new to this.  I think all this  "being declared" and "not being declared" is confusing me.  What does  all that mean?

Ok, there is a certain confusion arising here due to the way VB handles variables. In truth, every single variable you use is declared. What happens is that, when you use a variable for the first time, without having declared what type it was, VB will, "in the back", run a declaration of that variable. It will assign the variable, by default, as type Variant. This means that the variable size in memory will be the largest possible, in order to hold every single type of data you might want to put there.
When you declare a variable to be of a certain type (for example, String), that variable will only accept values of that type.
0
 

Author Closing Comment

by:Kenny537
ID: 33745093
Thanks!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

This is an explanation of a simple data model to help parse a JSON feed
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now