We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel VBA - Working with MultiDimensional Arrays

Medium Priority
42 Views
Last Modified: 2020-06-06
I am working on building a macro to manipulate some data in a spreadsheet that is downloaded. What I need to do is acquire the following information:

the contents of the first cell in column A that contains "clock:"[variable] I will want to store the [variable], as well as the row, which I will name Beginclockrow. The cell will contain the characters "clock:" followed by 3 characters.....

so i have the initial range select for the first cell in column a that has "clock:'
then I start a loop......ending at the last row on the sheet

Next I need to scan down column a, and find the next occurrence of "clock:" if the [variable] has not changed, I will keep looping until it changes. This becomes the next array element, with a new clockname, and beginclockrow.........this also gives me the Endclockrow for the previous array element

So my array in my mind looks like this:

clockarray (clockname, beginclockrow, endclockrow)

Thank you

My question is how do I dimension the array and redimension it as it needs to grow, it is OK if I have to redim in as I find each new occurance at each loop since I don't know the size going in.
Comment
Watch Question

Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Note that with a Collection or it's newer-fanlgled cousin a Dictionary, you don't have to re-dimension at all as it grows.
NorieAnalyst Assistant
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I have never worked with a dictionary, how is a dictionary declared in Excel VBA?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Dim MYDict As Dictionary

I believe there's an article in EE about using them.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
NorieAnalyst Assistant
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
If you need to do any look ups, I found the dictionary much faster than looping arrays.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I realize that the question has already been answered, but you may be interested to learn that you could have done a ReDim Preserve on a two dimensional array--but the first dimension would have to be the fixed (smaller) one. This is because VBA only permits changing the last dimension in a multi-dimensional array.
Dim clockArray() As Variant
Dim i As Long

For i = 0 To 10
    ReDim Preserve clockArray(2, i)
    clockArray(0, i) = "0" & i
    clockArray(1, i) = "1" & i
    clockArray(2, i) = i
Next
Debug.Print clockArray(2, 6)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.