Solved

Excel VBA - using arrays

Posted on 2011-02-16
9
346 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
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 50

Assisted Solution

by:teylyn
teylyn earned 100 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:Michael74
Michael74 earned 100 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
 
LVL 50

Accepted Solution

by:
Dave Brett earned 200 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 100 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 92

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now