3 quick array questions (and 3 misc) plz help :>

[venomatic]

Happy 7/4 in advance!


I don't know if the following questions are necessary for a solid grasp of VBA, but I'll take whatever help I can get, after spending hours trying to decipher these. Please just ignore anything you find too silly! So much obliged for the feedback friends, as always. Starting with the most straightforward (and ending with a not so much one):



1) What's the difference between arrays and array functions? I believe they are both variants, except that it seems that the latter allows easier element assignment (at least for single dimensions).



2) I saw an example where it says Dim MyMatrix(1 To 5, 4 To 9, 3 To 5) As Double. From some simple testing, this seems to be a basic 3D array where some of the bounds don't start at 0 (or 1) (e.g., element MyMatrix(5,3,3) doesn't exist). I was curious if there will ever be a practical situation where you number these bounds similar to these?



3) Suppose that cells A2, A3 and A4 all contain {=ROW(C4:D6)}. What does that mean exactly?




4) I had created a simple sub which just assigns a Rnd (or Rand) random number to a few columns of cells. Now, one can assign a button to activate a macro to reset the numbers, if there were prior ones. I apologize for not having the actual code, but there was a recent situation where I can also reset the code by simply clicking on the same column of where those cells were. Is this a default setting?




5) This is actually an observation of a simple Sub that changes any chart into a line chart.

Sub ChangeCharts()
Dim Cht As ChartObject
For Each Cht In Sheets("Sheet1").ChartObjects
Cht.Chart.ChartType = xlLine
Next Cht
End Sub

I just found it interesting that there's a built-in object or property off a user-created variable (although I guess in this case, that variable is based on the built-in ChartObject).




6) I was hoping for a simple example where we can avoid using the Set statement, by using the New keyword in the Dim statement, e.g., Dim X As New Worksheet?
(Looking up their respective definitions for each (italics) in Excel Help only created more questions for me (e.g. does it matter that an unassigned object is new to be considered as empty; implicit object creation, which I'm still shaky on, despite its usage in C# and C++; all the shared keywords in their syntax; etc.) so I want to keep it simple for now).

 

[Hermanito]

I'll do my best to give some answers, I hope others will followup if I say stupid or incorrect things here

1) An array is a group of variables, can be multidimensional, can contain different variable types (it's not per definition variant, it can contain strings or doubles for instance). In Excel, a range of cells can be represented by an array (1D or 2D) of values, an array function is like a regular Excel function, except that it has as its result also an array, which gets represented in a range of cells (which can also be just one cell, to make it confusing). So, although arrays and array functions are both based on the concept of arrays, they are two very different things.

2) Changing the array bounds can be useful if it makes the array behave closer to what you want to model. I have never needed it yet though, so I can't give you a real-world example

3) That means you have an array function in the range A2:A4. For A2, the function will yield the result as if you put in =ROW(C4); for A3 as if it was =ROW(C5), etcetera

4) I believe you are referring here to the Worksheet_SelectionChange event. Every time you change selection in the sheet, you can have some code execute automatically, which could easily do what you describe

5) This is because the variable you created is based on a blueprint, the class. In this case the class is a chartobject which has built-in properties and methods (state and behaviour, in object oriented terms), and each instance you create from a class, automatically has all the properties and behaviour from that class.

6) Why do you want to avoid Set? You can indeed combine declaration and instantiation with Dim X As New Worksheet, but when you would want to assign a different worksheet to X later on, you would need to use Set anyway. Personally I like to separate declaration and instantiation, so I never use that shortcut code as above.
In modern languages, the compilers are smart enough to not need the Set anymore. When you assign a value to an object variable, it knows what you mean. But VB6 isn't that smart, here you need to make that differentiation yourself: Set for object variables, and nothing for value variables.
Actually, there is another keyword you can use for value variables, but you don't need to type it (there is one exception), namely Let. So if you assign a value to a stringtype variable, you can say: Let myString = "something"

I hope that clears up some questions, if you have more after this, feel free to followup...

 

[venomatic]

thanks for the superb help ^__^
if you have the time,

3) hopefully this isn't a dumb question, but when YOU say A3=Row(C5), is that the same as A3=Row(D5)? in other words, is C5 and D5 being mapped to A3 (and similarly for A2 and A4)?
4) nice insight!
5) good OOP review! hmm, but are there actually classes in VBA (even though I hear about 'em in Excel Help)? I'm kinda used to the idea that there is an umbrella Application that holds objects (collections), which hold other objects that have the methods & procedures
6) where's the instantiation with Dim X As New Worksheet? (i.e. like how Dim X As Integer, and X=4) and by separate 'declaratiom and instantiation', do you mean use Set?


cheers and thanks for your patience<3

 

[Hermanito]

3) I'm no specialist in array formulas, just guessing here really: the array formula in this case maps an array of 3x2 on a result array of 3x1. Therefore I think only the first 3x1 part of the 3x2 array is used to calculate the resulting 3x1. But in this particular case, it would make no difference anyway (since ROW(C5)=ROW(D5), so it's hard to tell.

5) Yes, you can create your own classes in VBA. It is a subject for which it is quite hard to find good information, but I've managed to become quite good at it (even saying so myself ) by a long process of trial and error. You can also create collection classes that hold instances of your own created classes. If you go into the VBE window, and rightclick to insert a new module, you have the option to insert a class module: that's where you would define your own class.

6) Dim X As New Worksheet: Dim is the declaration, and New is the instantiation. This code is in fact just a shortcut of saying
Dim X As Worksheet
Set X = New Worksheet
where Dim is again the declaration, but now Set is the instantiation. I like to keep them separate, because that way you can instantiate at the time and place you need, instead of right at the beginning. With this method you can also do things like
Set X = ThisWorkbook.Worksheets("myworksheet")
Instead of creating a new instance, you map your object variable to an existing instance. You could also do this after the first method (with the New keyword), but then there has been created a worksheet object you never used, and never disposed of. You might get in trouble if you do this a lot, things like that cause memory leaks, because you are instantiating, but never releasing... You would need to have a
Set X = Nothing
to let the compiler know it can safely delete the formerly instantiated instance.

 

[venomatic]

hopefully my last questions Hermanito
5) ah cool^^ so one can use objects and use instances of (collection) class objects

6)

"Instead of creating a new instance, you map your object variable to an existing instance"

I just wanted to double-check, do you mean:

Dim X As Worksheet
Set X = New Worksheet :Rem can this be eliminated?
Set X = ThisWorkbook.Worksheets("myworksheet")

or

Dim X As Worksheet
Set X = ThisWorkbook.Worksheets("myworksheet")
Set X = ThisWorkbook.Worksheets("myotherworksheet")

or both/neither? (or maybe Set Y = ThisWorkbook.Worksheets("myworksheet"), etc. ) And if I did want to create a "new instance", would that be something like Set W = New Worksheet? (I guess new refers to the keyword)
And for fixing the memory leak if any (and then re-assigning),

Dim X As New Worksheet
Set X = Nothing
Set X = ThisWorkbook.Worksheets("myworksheet")

or

Dim X As Worksheet
Set X = New Worksheet
Set X = Nothing
Set X = ThisWorkbook.Worksheets("myworksheet")

sorry, and thanks again :D

 

[Hermanito]

Dim X As Worksheet
Set X = New Worksheet
Set X = ThisWorkbook.Worksheets("myworksheet")

First line says: hey compiler, sometime in the near future I will be using this variable called X, and when you see me use it, you should know it will be a worksheet, but don't reserve any memory just yet, I'll let you know when I'm ready.
Second line says: okay compiler, remember that variable X I talked about, well, I'm ready to start using it, and I want you to create a default instance of the worksheet class for me and let X refer to that instance, go!
Third line says: hi again compiler, you know, this variable X you instantiated for me, well, guess what, I've changed my mind, now I want you to make X refer to this existing worksheet in this workbook. Forget the reference to that instance you created earlier, I don't want that anymore. (in this case, the compiler might or might not be smart enough to clean up the memory it reserved for the instance, created in the 2nd line).

So, if it would have been just those 3 lines, the 2nd one is unnecessary. It makes no sense to create an instance and immediately discard it again.

In your second example:

Dim X As Worksheet
Set X = ThisWorkbook.Worksheets("myworksheet")
Set X = ThisWorkbook.Worksheets("myotherworksheet")

no new instance is created, you only create a link between the variable name and an existing worksheet instance in the workbook. If you would want to use both those worksheets, for instance for copying info between them, you would indeed use 2 worksheet variables, for example X and Y.

Then about the memory leak:

Dim X As New Worksheet
Set X = Nothing
Set X = ThisWorkbook.Worksheets("myworksheet")

Doing it like that would cause no leak, you properly cleanup all instantiated object variables. But again, if it would be just those lines of code, you needlessly instantiate and destroy the instance, so better skip it altogether which brings us again to

Dim X As Worksheet
Set X = ThisWorkbook.Worksheets("myworksheet")

which declares a variable, and then assigns a value (which in this case has to be an existing instance of the correct class: worksheet).
You can never say this:

Set X = Worksheet

because then the compiler has no clue what 'value' you want X to refer to. You have to give it an instance to refer to, and the New method does just that when you use it on a class: it creates a new instance.

I hope that explains it a bit.

P.S.: I don't really hold conversations with the compiler, I'm not that crazy

 

[venomatic]

haha thanks xD
I will definitely review ur post several times