?
Solved

Excel VBA - using arrays

Posted on 2011-02-16
9
Medium Priority
?
356 Views
Last Modified: 2012-05-11
Hi Experts,

What I need to do is to loop through a range and if a cell matches any one of 8 strings to call another procedure.  I had it working fine with a Select Case type approach but I was wondering if I could put the 8 funds into an array and then compare the array variable to the cell - something like the below (which doesn't work!).  One ofmy motivaitons for asking this question is that I don't use arrays at all in my code at the moment so I'm looking to learn as much about them as possible.

Dim fund As Variant

fund = Array("GCEP", "GHYFND", "GREP", "GVEP", "WEMP", "WSSP", "GGEP", "GTRFND")

For x = 1 To Lastrow

code = Sheets("Dimension").Cells(x, 1)
   
    If code Like fund Then

    Call Matcher

    End If

Next x


Cheers
Matt
0
Comment
Question by:matt_m
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 400 total points
ID: 34911939
Hello,

you could loop through the array

Dim fund As Variant

fund = Array("GCEP", "GHYFND", "GREP", "GVEP", "WEMP", "WSSP", "GGEP", "GTRFND")

For x = 1 To Lastrow

code = Sheets("Dimension").Cells(x, 1)


   For i = LBound(fund) to UBound(fund)
   
     If code = fund Then

          Call Matcher

         End If

   Next i

Next x

(not tested)

cheers, teylyn
 
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34911956
Matt,

I would reverse the logic of the approach

1) Set up an array of cells that matches your range
2) loop through the array of cells to be check (much faster than a range) then run say a MATCH formula on an 8 cell list (GCEP, GREP etc) to determine in a single shot if the cell matches any of the strings

My Variant Array artivle at http://www.experts-exchange.com/A_2684.html may help

Happy to code this if you provide a sample

Cheers

Dave
0
 
LVL 23

Assisted Solution

by:Michael Fowler
Michael Fowler earned 400 total points
ID: 34912027
You could use a dictionary instead of an array. Have a look at this article

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

If you make the array items above the keys and just add a dummy value you can then use the exists method to check if the string is in the dictionary.

eg

Dim MyDictionary As Object
Set MyDictionary = CreateObject("Scripting.Dictionary")
MyDictionary.Add("GCEP", 1)
.
.
.


For x = 1 To Lastrow
   If MyDictionary.Exists(Sheets("Dimension").Cells(x, 1)) Then Call Matcher
Next


Michael
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 50

Accepted Solution

by:
Dave Brett earned 800 total points
ID: 34912052
Working completely with arrays (for both cell test range and for Fund strings) you could do this to optimise speed

1) Assumed you range data is in column A
2) Sets an array equal to column A range
3) Test whether this array matches any string in the 8 string Fund array
4) Gives a MsgBox if True (replace with your call here)

Cheers

Dave
Sub Chck()
    Dim rng1 As Range
    Dim X
    Dim fund As Variant
    Dim lngrow As Long
    Dim lngMatch As Long

    fund = Array("GCEP", "GHYFND", "GREP", "GVEP", "WEMP", "WSSP", "GGEP", "GTRFND")
    Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp))
    X = rng1

    For lngrow = 1 To UBound(X)
        On Error Resume Next
        lngMatch = Application.WorksheetFunction.Match(X(lngrow, 1), fund, 0)
        On Error GoTo 0
        If lngMatch > 0 Then MsgBox "Call for " & X(lngrow, 1)
    Next
End Sub

Open in new window

0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 400 total points
ID: 34912125
matt_m,

In the attached file is the code below. Press the button on Sheet1 to run the macro.

You will see in the code that Split automatically creates a zero based array which can be indexed easily. That is the easiest way to create and array or elements which can be given as a simple string - being the easiest format to create in the first place.

Of course instead of the MsgBox you could Call another subroutine if a match is found.

Hope it helps explain in some small way how arrays can be used.

Patrick
Sub tester()
Dim str1 As String
Dim str2 As String
Dim fund() As String
Dim suffix() As String
Dim code As String
Dim x As Long

str1 = "GCEP,GHYFND,GREP,GVEP,WEMP,WSSP,GGEP,GTRFND"
str2 = "t,st,nd,rd,th"
fund = Split(str1, ",")
suffix = Split(str2, ",")

For x = 0 To UBound(fund)
    If fund(x) = Sheets("Sheet1").Cells(x + 1, 1) Then
        MsgBox "Yep, it matched the " & x & suffix(IIf(x < 4, x, 4)) & " element of the fund array"
    End If
Next x

End Sub

Open in new window

arrays-02.xls
0
 

Author Comment

by:matt_m
ID: 34912137
Hi All, thanks so much for your help - they all look like great ideas and if I can't use them in this situation I'm sure I'll find a use for them later.  I'll work through each one today and come back later to give out points.

Thanks again.
0
 

Author Closing Comment

by:matt_m
ID: 34914064
Hi All, I ended up using Brett's approach.  Everyone else's ideas though were so fantastic though I shared the points around (hope thats ok!).

Cheers
Matt
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34914446
Matt - Thanks for the points - Patrick
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34921116
matt_m,

Glad to see you found your answer, and especially glad to see that Michael recommended my Dictionary article :)

For a relatively small list, I would skip the arrays/dictionaries altogether and just make a concatenated list.  For example:


Const fund As String = "||GCEP||GHYFND||GREP||GVEP||WEMP||WSSP||GGEP||GTRFND||"

For x = 1 To Lastrow

    code = "||" & Sheets("Dimension").Cells(x, 1) & "||"
   
    If InStr(1, fund, code) > 0 Then

        Call Matcher

    End If

Next x

Open in new window



Note that for this to work, the delimiter must be applied to the beginning and end of the concatenated string, and that it should be a character or combination of characters that is unlikely to appear in any of the members of the concatenated list.

Patrick
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question