June 01, 2023, 10:21:47 pm

### AuthorTopic: Visual Basic for Excel Programming Language question  (Read 17567 times) Tweet Share

0 Members and 1 Guest are viewing this topic.

#### TrueTears

• TT
• Honorary Moderator
• Great Wonder of ATAR Notes
• Posts: 16363
• Respect: +667
##### Visual Basic for Excel Programming Language question
« on: January 18, 2012, 12:24:04 am »
0
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

Quote
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

Quote
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!
« Last Edit: January 18, 2012, 12:25:38 am by TrueTears »
PhD @ MIT (Economics).

Interested in asset pricing, econometrics, and social choice theory.

• Honorary Moderator
• ATAR Notes Legend
• Posts: 3168
• Respect: +326
##### Re: Visual Basic for Excel Programming Language question
« Reply #1 on: January 18, 2012, 12:29:07 am »
+2
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.

#### iamtom

• Victorian
• Forum Obsessive
• Posts: 451
• Indubitably dubious
• Respect: +35
• School: Melbourne High School
##### Re: Visual Basic for Excel Programming Language question
« Reply #2 on: January 18, 2012, 12:34:35 am »
+4
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.
« Last Edit: January 18, 2012, 12:36:09 am by iamtom »
2013: Wizardry, life.

#### TrueTears

• TT
• Honorary Moderator
• Great Wonder of ATAR Notes
• Posts: 16363
• Respect: +667
##### Re: Visual Basic for Excel Programming Language question
« Reply #3 on: January 18, 2012, 12:45:50 am »
0
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!
PhD @ MIT (Economics).

Interested in asset pricing, econometrics, and social choice theory.

#### TrueTears

• TT
• Honorary Moderator
• Great Wonder of ATAR Notes
• Posts: 16363
• Respect: +667
##### Re: Visual Basic for Excel Programming Language question
« Reply #4 on: January 18, 2012, 01:34:26 pm »
0
Got another question.

My book says another way of constructing the factorial function is by using a Do ... Loop While code like this:

Code: [Select]
Function DoLoopWhileDemo(N As Integer) As LongDim i As IntegerDim j As LongIf N < 2 ThenDoLoopWhileDemo = 1Elsei = 1j = 1Doj = j * ii = i + 1Loop While i <= NDoLoopWhileDemo = jEnd IfEnd Function
I understand how the above works, however what's the difference between this one and the Do While ... Loop one?

Thanks heaps!
PhD @ MIT (Economics).

Interested in asset pricing, econometrics, and social choice theory.

#### iamtom

• Victorian
• Forum Obsessive
• Posts: 451
• Indubitably dubious
• Respect: +35
• School: Melbourne High School
##### Re: Visual Basic for Excel Programming Language question
« Reply #5 on: January 18, 2012, 01:54:13 pm »
+1
As far as I can see, it's a different way of writing the same code... I could be misreading it, though.
2013: Wizardry, life.

#### TrueTears

• TT
• Honorary Moderator
• Great Wonder of ATAR Notes
• Posts: 16363
• Respect: +667
##### Re: Visual Basic for Excel Programming Language question
« Reply #6 on: January 18, 2012, 02:06:43 pm »
0
i see, thanks!
PhD @ MIT (Economics).

Interested in asset pricing, econometrics, and social choice theory.

• Honorary Moderator
• ATAR Notes Legend
• Posts: 3168
• Respect: +326
##### Re: Visual Basic for Excel Programming Language question
« Reply #7 on: January 18, 2012, 02:10:08 pm »
+2
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.
« Last Edit: January 18, 2012, 02:17:54 pm by laseredd »

#### TrueTears

• TT
• Honorary Moderator
• Great Wonder of ATAR Notes
• Posts: 16363
• Respect: +667
##### Re: Visual Basic for Excel Programming Language question
« Reply #8 on: January 18, 2012, 02:42:52 pm »
0
thanks laseredd!
PhD @ MIT (Economics).

Interested in asset pricing, econometrics, and social choice theory.

#### TrueTears

• TT
• Honorary Moderator
• Great Wonder of ATAR Notes
• Posts: 16363
• Respect: +667
##### Re: Visual Basic for Excel Programming Language question
« Reply #9 on: January 18, 2012, 03:26:52 pm »
0
Code: [Select]
Function ForDemo1(N As Integer) As LongDim i As IntegerDim j As LongIf N <= 1 ThenForDemo1 = 1Elsej = 1For i = 1 To N Step 1j = j * iNext iForDemo1 = jEnd IfEnd 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?
PhD @ MIT (Economics).

Interested in asset pricing, econometrics, and social choice theory.

• Honorary Moderator
• ATAR Notes Legend
• Posts: 3168
• Respect: +326
##### Re: Visual Basic for Excel Programming Language question
« Reply #10 on: January 18, 2012, 03:36:21 pm »
+1
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.

Code: [Select]
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.
Code: [Select]
For i = 1 To N Step 10
That would increase i by 10.

Code: [Select]
For i = 1 To N Step -10
That would decrease i by 10.

#### TrueTears

• TT
• Honorary Moderator
• Great Wonder of ATAR Notes
• Posts: 16363
• Respect: +667
##### Re: Visual Basic for Excel Programming Language question
« Reply #11 on: January 18, 2012, 03:39:29 pm »
0
awesome thanks!
« Last Edit: January 18, 2012, 11:25:27 pm by TrueTears »
PhD @ MIT (Economics).

Interested in asset pricing, econometrics, and social choice theory.

#### TrueTears

• TT
• Honorary Moderator
• Great Wonder of ATAR Notes
• Posts: 16363
• Respect: +667
##### Re: Visual Basic for Excel Programming Language question
« Reply #12 on: January 19, 2012, 12:56:05 am »
0
Having a bit of trouble understanding the creation of arrays.

Say for example this one:

Now there are a few lines in this code which I have no idea about.

First:

Code: [Select]
Dim MyArray(5)
Does this mean we are declaring the largest value that the array index can take? ie, 5

Second:

Code: [Select]
Temp = “”
...Have absolutely no clue what this does/means?

Third:

Code: [Select]
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
« Last Edit: January 19, 2012, 12:59:53 am by TrueTears »
PhD @ MIT (Economics).

Interested in asset pricing, econometrics, and social choice theory.

#### Thu Thu Train

• Voted AN's sexiest member 2012
• Victorian
• Posts: 667
• <3
• Respect: +336
##### Re: Visual Basic for Excel Programming Language question
« Reply #13 on: January 19, 2012, 01:03:56 am »
+2
Having a bit of trouble understanding the creation of arrays.

Say for example this one:

(Image removed from quote.)

Now there are a few lines in this code which I have no idea about.
Quote
First:

Code: [Select]
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.
Quote
Second:

Code: [Select]
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.
Quote
Third:

Code: [Select]
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"
Quote
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
(
'( '
"'  //}
( ''"
_||__ ____ ____ ____
(o)___)}___}}___}}___}
'U'0 0  0 0  0 0  0 0    0 0
BBSN14

i actually almost wish i was a monash student.

#### TrueTears

• TT
• Honorary Moderator
• Great Wonder of ATAR Notes
• Posts: 16363
• Respect: +667
##### Re: Visual Basic for Excel Programming Language question
« Reply #14 on: January 19, 2012, 01:28:50 am »
0
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 ....

Code: [Select]
Sub ArrayDemo1()Dim MyArray(5)Dim i As IntegerDim Temp As StringFor i = 0 To 5MyArray(i) = i * iNext iTemp = “”For i = 0 To 5[b]Temp = Temp &  #  & MyArray(i)[/b]Next iMsgBox TempEnd 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?
« Last Edit: January 19, 2012, 01:35:25 am by TrueTears »
PhD @ MIT (Economics).

Interested in asset pricing, econometrics, and social choice theory.