Newbie VBA Question

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?

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.

Btw, I realise my example makes no sense as Harry is not a number, but hopefully you get the gist?!

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.

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.

>>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

> 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.

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

> 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.

Thanks!