Visual Basic for Excel Programming Language question

[Thu Thu Train]

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

[TrueTears]

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

[Thu Thu Train]

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!

[TrueTears]

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

[Lasercookie]

Edit: yeah, all good

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.

[TrueTears]

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?

Code: [Select]

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




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:

Code: [Select]

For J = LBound(MyMat1, 2) To
UBound(MyMat1, 2)
It should really be:

Code: [Select]

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!




Thank you!

[Lasercookie]

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:

Code: [Select]

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

[TrueTears]

OHHH i get it, great explanation, thanks once again!

[mark_alec]

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.

[TrueTears]

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

[TrueTears]

Also just another question:



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!

[Lasercookie]

X actually has been defined.

If you look at the function definition, it's

Code: [Select]

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?

[TrueTears]

I kinda get it but what does the array X look like? And what exactly does LBound(X) and UBound(X) equal to?

[Lasercookie]

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.

Code: [Select]

Function Moo(RANGE As Variant, row As Integer, col As Integer)
Dim X As Variant
X = RANGE
Moo = X(row, col)
End Function


[TrueTears]

Awesome, thanks I get it now