Filtering a VBA Array using some criteria

I am wondering if there is a way to filter an array applying some criteria on the elements of the array and populating another array using some filter criteria.

Sub FilterArrayExample()

Dim MyBooks(1 To 5) As String
Dim FavouriteBooks(1 To 2) As String

MyBooks(1) = "apple"
MyBooks(2) = "actor"
MyBooks(3) = "band"
MyBooks(4) = "land"
MyBooks(5) = "lab"

' Need code here which will apply a Filter on MyBooks and populate FavoriteBooks Array with only those
' elements from MyBooks which start with the letter "a"
' so if the code works what I will have in FavoriteBooks will be equivalent of the following

' FavouriteBooks(1) = MyBooks(1)
' FavouriteBooks(2) = MyBooks(2)

End Sub

Open in new window

LVL 1
FaheemAhmadGulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Where would these arrays be seen? On a worksheet?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Sub FilterArrayExample()

Dim MyBooks(1 To 5) As String
Dim FavouriteBooks() As String
Dim Book
Dim i As Long

MyBooks(1) = "apple"
MyBooks(2) = "actor"
MyBooks(3) = "band"
MyBooks(4) = "land"
MyBooks(5) = "lab"

For Each Book In MyBooks
    If Left(Book, 1) = "a" Then
        i = i + 1
        ReDim Preserve FavouriteBooks(1 To i)
        FavouriteBooks(i) = Book
    End If
Next Book
If i > 0 Then MsgBox Join(FavouriteBooks, ", ")
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FaheemAhmadGulAuthor Commented:
@Martin - the results are to be displayed on some controls on a UserForm, not on an Excel Worksheet.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
Can you supply a sample workbook?
0
FaheemAhmadGulAuthor Commented:
Many thanks Martin and Subodh. The code provided by Subodh is working. However, I would just like to understand a few other concepts if that is OK.
What I need to do is to pull data from an Excel WorkSheet and use it to create an Array for a VBA Project in Word.
I will then use this array in a VBA Word project. Where I use this array my Word Project I need to be able to filter this data,
I know how to do this in C#. In my .NET project I create a Book class and create a list of Book Objects. I can then use LINQ to filter this list of objects. I am just trying to understand how to achieve this in VBA.
I thought I will start with a simple array of Strings and see how to filter it and then learn how to filter a list of object. Hope this clarifies the context.
I include code I am using to create array from an Excel Workbook.
I am attaching the Excel Work here as well.
This is an array that contains in the first column the title of the book and in the second column the name of the author.
In this case I would like to filter out books written by Jane Auston

Public MyBooks As Variant

Sub GetBooks()
Set exWb = objExcel.Workbooks.Open("C:\Library\Book Collection.xlsm")
MyBooks = exWb.Worksheets("books").range("A1:B4").Value
exWb.Close

End Sub

Open in new window

--moscow1-users--fahmad-allwork-myd.xlsx
0
Martin LissOlder than dirtCommented:
If Subodh's answer is satisfactory for this question then I suggest you close this question assigning him the points. Then open a new question including both Excel and Word in the Topics. BTW I deleted the Visual Basic Classic topic that was in this question because as far as I can tell, VB6 has nothing to do with your requirements.
0
FaheemAhmadGulAuthor Commented:
Many thanks Subodh. This worked well. I will create a new question for my additional related questions as suggested by Martin.  
Thank for your help.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Faheem! Glad it worked as desired.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.