VBA - How to refer to column names in Array?

Question

To start, I have a scraping function that scrapes a table from a web page, and stores the data in a 2D array.

The 2D array starts from row 0 to however many rows of data are on the page, and columns 1 to however many columns there are.

Row 0 simply contains all the column names.

My ReDim:

ReDim Addresses(0 To lngTotalRecords, 1 To columns.Count) As String

I've then stored this 2D array into a scripting dictionary called dictClients, as there are multiple clients that all have their own entries for the same table on the web page.

So in my dictionary I have something like the following to refer to a particular address table for a particular client:

dictClients(1)("Addresses")
dictClients(2)("Addresses")

I now want to be able to check if a cell in a certain row contains a specific value, however the web page allows the columns to be reorganized so that:

dictClients(1)("Addresses")(1,1) 'row 1 column 1

will not always refer to the "Street Number" column. The street number column could be the following for someone else for example:

dictClients(1)("Addresses")(1,3) ' row 1 column 3

Given that these cells:

dictClients(1)("Addresses")(0,1) '(0,2) (0,3) etc.

all refer to the column's names, what's the best way for me to find the position of a particular named column?

Example: I want to get the value of the Postal Code cell in row 1, so I need to look in dictClients(1)("Addresses")(1, POSTALCODECOLUMN), which isn't always in the same position on the web page.

I was thinking of using the following function:

Public Function column(strArr() As String, strColumnName As String)
Dim i As Long
For i = 1 To UBound(strArr, 2)
If strArr(0, i) = strColumnName Then column = i
Next
End Function

But it just feels so lengthy calling it like:

strPostalCode = 
dictClients(1)("Addresses")(1,column(dictClients(1)("Addresses"), "Postal Code")

Is there a better and easier way to do this?

Thanks.

It's strange how all the scraping questions come in at once. My standard answer: **Welcome to Stack Overlow! "How to scrape web pages?" is one of the most common Excel/VBA questions on this site (close to 1000 questions & answers) and elsewhere. I find it hard to believe that you've exhausted all options before posting. Please see the help center and also "How to Ask" and these tips and also: minimal reproducible example. If you run into a problem not covered elsewhere, edit your post.*Good Luck* — ashleedawg
– ashleedawg, Commented Mar 14, 2018 at 21:36
@ashleedawg looks to me like web scraping is incidental to the Q. Real Q is about accessing an array with named columns — chris neilsen
– chris neilsen, Commented Mar 14, 2018 at 22:57
@loael your function look ok to me as is. But if you really want to improve it, look into using a Class. You could then create a Method that accepts a row number and column name, returns the required data. Its call might look like strPostalCode = Clients.GetData(1, "Addresses") — chris neilsen
– chris neilsen, Commented Mar 14, 2018 at 22:59

Jedi-X · Accepted Answer · 2024-02-23 16:39:44Z

0

Option 1

The simplest answer may be to use ListObjects:

ListObject("Table1").ListColumns("Column1").DataBodyRange...

Option 2

Referencing Dick Kusleika's answer in Naming array dims in Excel VBA:

If I had a three dim array, I would probably create some custom class modules to model the objects that I was using.

If your first dimension is a SchoolID, your second dimension is a ClassID, and your third dimension is a StudentID, then code using custom class modules like this

Debug.Print Schools(10).Classes(3).Students(7).Name

is more readable than

Debug.Print arrLeeftijdenG5(10,3,7)

answered Feb 23, 2024 at 16:39

Jedi-X

516 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA - How to refer to column names in Array?

1 Answer 1

Option 1

Option 2

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option 1

Option 2

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related