LinkBack URL
About LinkBacks
Hello,
I thougt I would 'try' to learn VBA.
My first attempt is to return a simple Array of ColorIndexes.
Could someone please help me with what is wrong.
Ola
Please Login or Register to view this content.
Hello,
I thougt I would 'try' to learn VBA.
My first attempt is to return a simple Array of ColorIndexes.
Could someone please help me with what is wrong.
Ola
Please Login or Register to view this content.
Good afternoon olasa
Congrats on taking the plinge!! It seems you're already into the weeping and gnashing of teeth stage. The good news is it doesn't get any better ...
If you're trying to return a series of numbers you're going to have to return it as a string. That was the problem with your function - it was returning multiple values, but a function can only return one value. I have made an amendment that shows each ColorIndex value seperated by a comma.
Also, when working with functions and ColorIndex you don't really need to use Application.Volatile, as the ColorIndex property doesn't get updated when a cell is changed - only when you force a recalc.Please Login or Register to view this content.
HTH
DominicB
Please familiarise yourself with the rules before posting. You can find them here.
I had a big laugh I must say. Thanks for the cheers.
I did not think I could stay anonymous for very long so I just had to bite it and come clean.
Thanks, but I want a VBA function that returns a VBA Array like the examples below. Not a text string - if possible.
So when I read "it was returning multiple values" - I sounded as if I was on the right path.
I also read that: "a function can only return one value" ??
Am I missing something or...I had a look at this and this - but I didn't know if it could solve my problem.
And yes I am tossing books and loosing some hair over this.
//Ola
I might as well attach my feable attempsPlease Login or Register to view this content.
For the record, solved. Questionably pretty, but a start.
It returns either a ColorIndex Row Array or Column Array.
//Ola
Please Login or Register to view this content.
You could return an array the same size and shape as the range. And if you're learning VBA, a good habit to get into is to declare all variables, which you can enforce by adding Option Explicit at the top of every module. And I wouldn't choose variable names that are the same as VBA key words.
Please Login or Register to view this content.
Last edited by shg; 08-01-2008 at 03:02 PM.
Can I jump in since I am trying to improve my VBA skills also?
shg, you said not to use Excel terms as variables but then you did thisI can't tell where we are using cell as a variable and where it's an excel term in your code.Please Login or Register to view this content.
Question2: Why didn't you initially dim aCl as (r.row to r.row...) instead of using the redim?
Thanks in advance.
ChemistB
It sounds like you think cell is a keyword; it's not. Cells is a both a property and a collection object. The variable is used here:I can't tell where we are using cell as a variable and where it's an excel term in your code.
Please Login or Register to view this content.Because the dimensions aren't known at compile time; you don't know what size to make the array until run-time, so the compiler cannot allocate it statically. It has to be a dynamic array.Why didn't you initially dim aCl as (r.row to r.row...) instead of using the redim?
Last edited by shg; 08-01-2008 at 03:40 PM.
Shg: thanks, much cleaner solution. I enjoy when the solution can delivers more from less.
Just tell me, why are these 'unfamiliar' Datatypes better than for instance Integer/Range?
Dim aCI() As XlColorIndex
Dim cell As Excel.Range
ChemistB: thanks for questioning. I was surprised I knew the answer to Question 2.
Ola
You could use a Long, but the ColorIndex property is a Variant. And using the enumeration allows Intellisense to work: If you type
... you'll see the enumerations as soon as you type the = sign.Please Login or Register to view this content.
As to dimensioning variables to include their library names (e.g., Excel.Range instead of just Range), it's a habit I'm trying to get into. Word, for example, also has a Range object, and if you were automating Word from Excel it would be important to distinguish.
But I wasn't consistent, because I didn't do that in the function declaration.
IMO, that can go too far. I see people that always use
... while I just usePlease Login or Register to view this content.
... because brevity has its own merits, and I find verbose code hard to read.Please Login or Register to view this content.
However, you can also use
... but I avoid that, because Intellisense works with the first two ways, but not with the third.Please Login or Register to view this content.
I also mostly avoid eliding default properties, and so use
... but the subscripts are themselves arguments to another default property:Please Login or Register to view this content.
and that, I think, is a bridge too far.Please Login or Register to view this content.
Since I am the only person that will read most code I write, I focus on making it understandable after years and many good, smokey, Scotches between visits.
Last edited by shg; 08-01-2008 at 07:13 PM.
Shg: thanks for the dialectal sides of the language.
It will probably take me some time to build up rules for a good recollection language. Associations are quite individual.
Sound like a good rulePlease Login or Register to view this content.
So since it's defined and makes it easier to read. Why not use it (once you know it exists)Please Login or Register to view this content.
I'm not there yet; programming across application borders. But important when new Applications come into the game.Please Login or Register to view this content.
I agree, it makes it more readable.Please Login or Register to view this content.
I think the best judge of a good code is if you can read it
after you have forgotten you ever wrote it. Due to a good Scotsh or not...
//Ola
Originally Posted by shg
It's a Variant is so that the property can return, for example, Null if you ask for the color index of a range that does not have a common value. Since each variable of my array is intended to receive the color index of a single cell, they will always be Longs (which is what enumerations are).
Me either, actually. I'm just trying to get a better understanding of the language.I'm not there yet; programming across application borders. But important when new Applications come into the game.
Here's another example from a thread yesterday. The OP asked:
You can set a ton of properties on Selection, without knowing what aspect of the Selection you're operating on, and neither the macro recorder nor Help makes it easy to figure out.Is there a way to change the picture formula without selecting the picture?Please Login or Register to view this content.
The answer, it turns out, is
Try to find the Pictures collection or the Picture object in Help, or where the Formula property of a Picture is explained.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks