strings as array indexes

cwalton · July 29th, 2007, 01:42 PM

Hi,
I haven't coded for 20 years and I've just started again a few weeks ago with VBA for excel. I'm wondering, is it possible to index an array using strings? I remember doing something like this in Pascal decades ago but can't get it to work for VBA.

For instance:

Is there a way to create a couple of types
("green", "yellow", "red")
("scoping", "analysis", "design", "testing")

then an array indexed by those types

DIM ReportValues( "green" to "red", "scoping" to "testing") as integer

Any help would be greatly appreciated.

Thank you,
Chris Walton

robzyc · July 29th, 2007, 05:00 PM

Hi Chris,

First off, welcome to the Forum!!

To answer your question, you cannot reference an Array subscipt via a string. The only way to do this is to create a Collection to store the data, you can use strings as they Key value. But I dont think thats really what you are trying to do here is it?

It sounds to me you just want to be able to have a String-for-a-Constant yes?
As in, in the code its an int, but to the user, you want them to see "green", "red", "yellow" etc.

Can I ask a bit more about your problem? As I may be able to suggest other alternatives?

Regards,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

cwalton · July 29th, 2007, 05:36 PM

Hi Rob,

Thanks for your response. I was afraid that might be the case as I've been searching all over for examples and coming up empty.

You asked for more information so...

I've got a spreadsheet with values in 4 dimensions. 3 of those dimensions are text
status ("green", "yellow", "red")
type ("scoping", "analysis", "design", "testing")
function ("CS", "CT", "FS", "GBU", "HHO")

I simply need counts of the different types of information in the spreadsheet. I figured if I could index an array by strings I could bypass a lot of conditional logic and just grab a line from the spreadsheet and do the counting simply as:
Dim Status as string
Dim Type as string
Dim Function as string

do while....
Status = ActiveCell.offset(0,1).value
Type = ActiveCell.offset(0,2).value
Function = activecell.offset(0,3).value

ReportValue(Status, Type, Function) = ReportValue(Status, Type, Function) + 1
loop.....

robzyc · July 30th, 2007, 02:50 AM

Hi Chris,

Try the following function, pass to it a Range and a string to look for:

Code:

Public Function ValueCount(ByRef OnRange As Range, ByVal ValueToCount As String) As Long

    ValueCount = CLng(WorksheetFunction.CountIf(OnRange, ValueToCount))

End Function

You can then take this one/two steps further to solve your problem.

â¢ Create an Array of the strings you want to look for.
â¢ Iterate through the array of strings, passing the string and the entire source range to the function.

I think that will make it "job done!" will it not? This code should actually execute a lot quicker, as
the function will scan the entire range in one hit, rather than iterating row-by-row (which can be very
slow on large sheets).

I hope this helps, if you have anything probs, then please let me know.

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>