Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 29th, 2007, 01:42 PM
Registered User
 
Join Date: Jul 2007
Location: , OR, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default strings as array indexes

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

 
Old July 29th, 2007, 05:00 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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>
 
Old July 29th, 2007, 05:36 PM
Registered User
 
Join Date: Jul 2007
Location: , OR, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old July 30th, 2007, 02:50 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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>




Similar Threads
Thread Thread Starter Forum Replies Last Post
Array of Strings to dll jjd Excel VBA 2 February 26th, 2006 07:25 AM
Non-Corresponding Array Indexes Pericles PHP How-To 0 September 20th, 2005 03:34 AM
Array of Strings jjd Visual C++ 1 June 23rd, 2005 02:56 PM
sort array of strings error lanita BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 1 December 14th, 2004 08:52 PM
Re-Using an Array (for strings of varying lengths) WebDevel Javascript How-To 2 December 6th, 2003 06:46 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.