ATAR Notes: Forum
VCE Stuff => VCE Mathematics/Science/Technology => VCE Subjects + Help => VCE Technology => Topic started by: TrueTears on January 18, 2012, 12:24:04 am
-
Hey guys, just wondering if any of you are familiar with VBA for excel programming language, I'm trying to make a loop using the Do While statement for the factorial function, namely, f(n) = n! = n*f(n-1). I can construct this using If ... Then ... ElseIf ... but I'm trying to construct it using Do While.
My book says the code is
Function DoWhileDemo(N As Integer) As Integer
Dim i, j As Integer
If N < 2 Then
DoWhileDemo = 1
Else
i = 1
j = 1
Do While i <= N
j = j * i
i = i + 1
Loop
DoWhileDemo = j
End If
End Function
So apparently this loop gives the function DoWhileDemo(N) = N*DoWhileDemo(N-1), however I'm confused with the
Do While i <= N
j = j * i
i = i + 1
Loop
DoWhileDemo = j
part of the code. My book doesn't explain it either, what the hell does
j = j * i
i = i + 1
even mean?
It clearly makes no mathematical sense, so I'm suspecting this is some programming language I haven't heard of.
Many thanks!
-
While i is less than or equal to N - it will run the below stuff.
j = j * i multiplies j by i. I'm not sure what j is being used for though.
i = i + 1 increments i by 1. Then the while loop will run again, with this new value of i. The loop will keep doing that until i is eventually greater than N.
-
OK, so the reason you're confused from what I can tell is you're misleading the context. It's a simple enough algorithm in any language.
I'll go through the code step by step.
Dim i, j As Integer
Defines i and j as numbers, basically.
If N < 2 Then
DoWhileDemo = 1
So, if N is less than 2, DoWhileDemo = 1 - that is, the whole code is equal to 1.
Else
i = 1
j = 1
If N is not less than 2, i and j are equal to 1. Still with me? This is where that all gets relevant.
Do While i <= N
So i is defined as 1 straight-off when it is less than N. While i is less than N...
j = j * i
J = 1 * 1
i = i + 1
I = 1 + 1
Loop
It will keep looping the algorithm until i > N. i and j will increase in value each time. While j's and i's formulas don't make sense mathematically, in the sense of the algorithm they make perfect sense.
DoWhileDemo = j
It will end with DoWhileDemo equalling j - the algorithm will make j a certain number. Too lazy to work it out, haha, but you get the point.
edit: clarification.
-
omg thanks so much you two, awesome explanations, completely get it now!!!
As you can see I'm a complete novice at programming, i treat = as the mathematical definition anytime i see it LOL
i'll be bound to have more questions sooner or later, would be awesome if you guys can keep an eye out for this thread in the near future :)
thanks again!
-
Got another question.
My book says another way of constructing the factorial function is by using a Do ... Loop While code like this:
Function DoLoopWhileDemo(N As Integer) As Long
Dim i As Integer
Dim j As Long
If N < 2 Then
DoLoopWhileDemo = 1
Else
i = 1
j = 1
Do
j = j * i
i = i + 1
Loop While i <= N
DoLoopWhileDemo = j
End If
End Function
I understand how the above works, however what's the difference between this one and the Do While ... Loop one?
Thanks heaps!
-
As far as I can see, it's a different way of writing the same code... I could be misreading it, though.
-
i see, thanks!
-
Do While Loop checks for the condition (i <= N) before it runs the loop
Do Loop While checks for the condition after it runs the loop. This means that this one will do one more loop than the Do While Loop.
The advantage with the second one is that you force the loop to run once, no matter what. This might be useful in some situations.
If you want some Wikipedia links to look at:
http://en.wikipedia.org/wiki/While_loop --- this is the Do While loop in VBA
http://en.wikipedia.org/wiki/Do_while_loop --- this is the Do Loop While in VBA
edit: reworded statement for clarity.
-
thanks laseredd!
-
Function ForDemo1(N As Integer) As Long
Dim i As Integer
Dim j As Long
If N <= 1 Then
ForDemo1 = 1
Else
j = 1
For i = 1 To N Step 1
j = j * i
Next i
ForDemo1 = j
End If
End Function
This code also creates the factorial function, however just to clarify, what does the Step and Next part of the code mean? I'm guessing the Step is the i increment and Next is like a loop that makes i cycle through from 1, 2, 3, ..., N?
-
This code also creates the factorial function, however just to clarify, what does the Step and Next part of the code mean? I'm guessing the Step is the i increment and Next is like a loop that makes i cycle through from 1, 2, 3, ..., N?
Yep, that's correct.
For i = 1 To N Step 1
Just to be clear, this starts i at 1, and the loop will keep going until i is equal to N - incrementing i by 1 each time.
The interesting thing with step is that it doesn't have to be 1.
For i = 1 To N Step 10
That would increase i by 10.
For i = 1 To N Step -10
That would decrease i by 10.
-
awesome thanks!
-
Having a bit of trouble understanding the creation of arrays.
Say for example this one:
(http://img694.imageshack.us/img694/9199/arrayt.jpg)
Now there are a few lines in this code which I have no idea about.
First:
Dim MyArray(5)
Does this mean we are declaring the largest value that the array index can take? ie, 5
Second:
Temp =
...Have absolutely no clue what this does/means?
Third:
Temp = Temp & # & MyArray(i)
Firstly what does this code mean?
Also I'm confused about the Temp (after the = sign) and " " around the #. I know what the & does however I thought you don't have to add " " around each element connected by &? Why is there a " " around #?
Thanks
-
Having a bit of trouble understanding the creation of arrays.
Say for example this one:
(http://img694.imageshack.us/img694/9199/arrayt.jpg)
Now there are a few lines in this code which I have no idea about.
First:
Dim MyArray(5)
Does this mean we are declaring the largest value that the array index can take?
No. Maximum number of "items" in an array NOTE: array index starts at 0 so the array can store 6 items.
Second:
Temp =
...Have absolutely no clue what this does/means?
Sets the variable Temp to an empty string so it is declared and has a value so it won't error if it is called.
Third:
Temp = Temp & # & MyArray(i)
Firstly what does this code mean?
This sets a new value for "temp" what this does is it takes the "previous value" of temp and the item from MyArray at location 'i' "adds" them together and sets the value to Temp and they will have a "#" between them.
For example:
Temp = "A"
i = 1
MyArray(1) = "b"
Temp = Temp & " # " & MyArray(1)
means Temp is now "A # b"
Also I'm confused about the Temp (after the = sign) and " " around the #. I know what the & does however I thought you don't have to add " " around each element connected by &? Why is there a " " around #?
Thanks
the ""s are there because # isn't a valid variable name it is a "string". if you somehow had a variable named "#" you would not need the ""s there you could just go Temp = Temp & # & MyArray(i)
Hope I helped
-
alright thanks, but just one last bit i still don't quite get
with
Temp = Temp & " # " & MyArray(i)
So we first have when i = 0 MyArray(0) = 0
So Temp is now (empty string) # 0
then we have i = 1 then MyArray(1) = 1 that means:
Temp is now (empty string) # 1
Then when MyArray(2) = 4
we have Temp is now (empty string) # 4
and so on.
So I'm assuming (empty string) doesn't show, ie, it's just a blank. So shouldn't the image be:
#0#1#4#...
But it is displayed as 0#1#4#...
[Ie, the difference is that I wudda thought it began with a # but it doesn't?]
I know it's pretty trivial but ....
Sub ArrayDemo1()
Dim MyArray(5)
Dim i As Integer
Dim Temp As String
For i = 0 To 5
MyArray(i) = i * i
Next i
Temp =
For i = 0 To 5
[b]Temp = Temp & # & MyArray(i)[/b]
Next i
MsgBox Temp
End Sub
For laseredd: The bolded part [ehhh, never knew couldn't bold in code quote, it's the line with around it ;P] is what I wudda thought should be written as, ie, the # without the " " around it, however when I run the macro VBA says "syntax error" so I'm assuming the " " has to be around the #. Cthulhu said it was because # is a string and strings when connected by & needs a " " around it, but variables when connected with & do not need a " " around it, this makes sense as it coincides with the examples I've seen so far, what do you think of it?
-
Nope!
Okay let me explain.
Temp = "" is just a "declaring/assigning" statement
inside the for loop for i = 0
MyArray(i) = 0
Temp = Temp & " # " & MyArray(0)
So temp is now "#0"
On the second pass (i=1)
MyArray(1) = 1
Temp = Temp & " # " & MyArray(1)
In "words":
Temp = " # 0" & " # " & "1"
So Temp = " # 0 # 1"
Basically you don't reassign Temp to the empty string everytime. Temp is only an empty string when i = 0. Everytime after that it is " # 0" " # 0 # 1" etc etc...
-
Nope!
Okay let me explain.
Temp = "" is just a "declaring/assigning" statement
inside the for loop for i = 0
MyArray(i) = 0
Temp = Temp & " # " & MyArray(0)
So temp is now "#0"
On the second pass (i=1)
MyArray(1) = 1
Temp = Temp & " # " & MyArray(1)
In "words":
Temp = " # 0" & " # " & "1"
So Temp = " # 0 # 1"
Basically you don't reassign Temp to the empty string everytime. Temp is only an empty string when i = 0. Everytime after that it is " # 0" " # 0 # 1" etc etc...
oh right opps! I actually just recognised that, silly me! yup so it should be #0#1#4... but how come when executed in excel it's 0#1#4... (ie, the difference is that theres no # before the 0)?
-
because like I said Temp is an empty string the first time its called. Temp="" when i = 0 because its the first "pass" and doesnt have a value yet if you change Temp = "" to Temp = "#" then there would be a # in front of the 0; try it!
-
oh wait nvm, i just tried it on excel, looks like my book must have had a typo lol, if you type the code in as it is, you do in fact get #0#1#4... lol
Thanks you guys :) <3
-
Edit: yeah, all good :D
I just ran the code in excel and it executes as #0#1#4... for me?
http://imgur.com/KBJBv
For laseredd: The bolded part [ehhh, never knew couldn't bold in code quote, it's the line with around it ;P] is what I wudda thought should be written as, ie, the # without the " " around it, however when I run the macro VBA says "syntax error" so I'm assuming the " " has to be around the #. Cthulhu said it was because # is a string and strings when connected by & needs a " " around it, but variables when connected with & do not need a " " around it, this makes sense as it coincides with the examples I've seen so far, what do you think of it?
Yeah, Cthulhu's right, you want # displayed in the output, so you put quotation marks around it so it's a string. # isn't a valid variable (if you tried Dim #, it wouldn't work, since you can't use a symbol as a variable name) nor is it an operator in VBA, hence why you get a syntax error.
-
Hey guys, I'm having a bit of trouble understanding the LBound and Ubound functions for VBA.
I attached below what my book explains what LBound and UBound does and I kinda get it for one dimensional arrays (but have no idea what it does for 2 dimensional arrays).
So firstly, my understanding of LBound and UBound for 1-d arrays is basically it treats the array like a matrix, then LBound just pumps out the number that the first column is named with and UBound pumps out the number that the last column is named with. For example, in the attached picture, MyArray(5), since by default the first "column" of the matrix is named 0, LBound would be 0 and UBound is 5. But say we had a different code, something like this that specifies which index the array starts: [Emphasized in the following code with >>>>>>>>>>>]. Then LBound would be 6 and UBound would be 10, right?
Sub ArrayDemo4()
>>>>>>>>>>>>>Dim MyArray(6 To 10)
Dim i As Integer
MsgBox Index of MyArray Starts at: & _
LBound(MyArray)
MsgBox Index of MyArray Stops at: & _
UBound(MyArray)
For i = LBound(MyArray) To UBound(MyArray)
MyArray(i) = i * i
Next i
MsgBox The Value in MyArray(7) is: & _
MyArray(7)
End Sub
(http://img408.imageshack.us/img408/8498/bounds.jpg)
Ok, now I don't seem to get what LBound and UBound does for multidimensional arrays, using the MyMat1 (ie, 3 by 2 array).
This part of the code confuses me:
For J = LBound(MyMat1, 2) To
UBound(MyMat1, 2)
It should really be:
For J = 1 To 2
But the book is just highlighting the fact that LBound/UBound can also be used, however I don't get how LBound(MyMat1, 2) =1 and UBound(MyMat1, 2) =2?
Namely, what is the 2 in LBound(MyMat1, 2) and UBound(MyMat1, 2) mean? The explanation says this array is a 2 dimensional array, how?? It's a 3 by 2 array, how do you determine it's dimension? And how does LBound, UBound work in general for multi dimensional arrays? Maybe my fundamental understanding of UBound/LBound is wrong so that's why I don't seem to get it, please correct me!
(http://img535.imageshack.us/img535/7274/mutilarray.jpg)
Thank you!
-
A dimension of an array is by the number of indices you need to access an element in that array. So for 1-dimensional arrays, you can use MyArray(x) but for 2-dimensional arrays, you need to give it MyArray(x,y).
Now what LBound does is return the smallest index, but you indicate in what dimension. Similar thing for UBound, but the maximum index. It's not actually looking at the values of the numbers inside the array, only what their indexes are.
So for LBound(MyMat1, 2), you're wanting the minimum index of array MyMat1 in it's second dimension. For UBound(MyMat1,2), it's getting the maximum index in it's second dimension.
We can look at the definition of MyMat1:
Dim MyMat1 (1 to 3, 1 to 2) As IntegerSo the second dimension of the array is defined as being from 1 to 2. This is why the LBound(MyMat1, 2) = 1 and UBound(MyMat1, 2) = 2.
Maybe first and last index of an array is a better way of thinking about it, but I'm not entirely sure if that is valid for all situations of this UBound and LBound function (I don't know if it has any other odd behaviours etc.).
-
OHHH i get it, great explanation, thanks once again!
-
If I can interject. If you wish to learn and understand programming, I suggest you don't learn with the brainfuck that is VBA - look into understanding concepts with a simple and powerful language like python or ruby. Or if you are curious about the academics, lisp, scheme or haskell.
-
Thanks mark, yeah this is just for a finance unit next sem which uses excel and VBA, although it's the first time I've done any sort of programming it is indeed quite interesting and will definitely have a look at C++ and python in my spare time
-
Also just another question:
(http://img338.imageshack.us/img338/1968/vbaarray.jpg)
So I don't quite understand how LBound and UBound works for something that is not exactly an array. Say in the above example, isn't X (=CF) just a parameter for the function? If so, how is LBound/UBound defined for things like this?
Also I don't understand what X(i,1) means in the code, Isn't that the notation for an array with i being the number of rows for the array and 1 meaning 1 column for the array? But we haven't defined (Dim) any X() yet so how can we just randomly chuck in a X(i,1) in the code?
Thanks!
-
X actually has been defined.
If you look at the function definition, it's
Function VarPV (CF As Variant) As DoubleSo the variable CF is the input for the function. If you look at the spreadsheet, CF is being assigned to a range. Where you have =VarPV(A3:A$7). Then there's the Dim X As Variant, and then the X=CF.
All this sets the array X to be equal to the range that is passed to function via CF.
From there on, you can use X as a regular array. Does that clear things up for you?
-
I kinda get it but what does the array X look like? And what exactly does LBound(X) and UBound(X) equal to?
-
A range of cells is always a two-demensional array even if it is only a row or a column; the indices always run from one and not from zero
Well if the range is VarPV(A3:A$7), then it's just a single column of 100s. However, due to that behaviour as stated in your textbook, it's a two dimensional array that starts from 1.
If you scroll down to 'transferring values between arrays and ranges', there is a bit more elaboration here: http://msdn.microsoft.com/en-us/library/aa139976(v=office.10).aspx
When you assign range values to a variable such as vaSalesData, the variable must have a Variant data type. VBA copies all the values in the range to the variable, creating an array with two dimensions. The first dimension represents the rows and the second dimension represents the columns, so you can access the values by their row and column numbers in the array.
...
Also, the array always has two dimensions, even if the range has only one row or one column. This preserves the inherent column and row structure of the worksheet in the array and is an advantage when you write the array back to the worksheet.
For X = A3:A7 we would have:
So X(1,1) would access A1. X(2,1) would access A2. If you had a bigger range, X(2,2) would access B2. In this case, LBound(X) would be the smallest index in the first dimension (the rows), so it'd be equal to 1. UBound(X) would be the largest index in the first dimension, so it'd be equal to 7.
To test this out, I wrote up this. I set out just some dummy values in rows A1 to B3 (just 1,2,3,4,5,6). =Moo(range, row, col)
So if I wanted to get the value from A2 in the range that I had set up, I would use =Moo(A1:B3,2,1).
Have a play around with it and see if it helps you get your head around it.
Function Moo(RANGE As Variant, row As Integer, col As Integer)
Dim X As Variant
X = RANGE
Moo = X(row, col)
End Function
-
Awesome, thanks I get it now
-
TrueTears, I don't know why you're getting what you're getting, this is what I get, from VB.
It could possibly be that you put # instead of "#" - strings are always meant to be in quotes.
Anyways, here's what I got - I just edited a little bit of the code for a console, so I wouldn't have to mess around with GUI stuff.
(http://i41.tinypic.com/73i0s3.jpg)