VBA Sizing an Array with User Choosing its Size

Question

I am trying to make a string array of keywords. The string array will have a set number of columns (various number of keywords for 3 different fruits: apple, banana, orange). However, the column for each row will be a different size based on how many keywords the user is looking for, for EACH fruit. The user may be looking for 1 keyword for apple, 2 keywords for banana, and 3 keywords for orange. when it's all said and done, I want a string array of 3xC, where C is the largest number of keywords, and the unfilled cells are empty. Here is the code I have so far, but I am unsure of how to dimensionalize the array.

Public strTemp(0 To 2, 0 To 100) As Variant
Sub GetKeyWords()

Dim numKeyA As Integer
Dim numKeyB As Integer
Dim numKeyO As Integer



'Ask user for integer value of keywords looking for
numKeyA = InputBox("How many keywords would you like to search for the A? (Integer)", "A Integer Value Please")

'Ask user for the specific keywords
For k = 1 To numKeyA

    'Save keywords into strTemp array
    strTemp(0, k - 1) = InputBox("Please enter keyword" & k)

Next

numKeyB = InputBox("How many keywords would you like to search for the B? (Integer)", "B Integer Value Please")

For a = 1 To numKeyB

    'Save keywords into strTemp array
    strTemp(1, a - 1) = InputBox("Please enter keyword" & a)

Next

numKeyC = InputBox("How many keywords would you like to search for the C? (Integer)", "C Integer Value Please")

For b = 1 To numKey777

    'Save keywords into strTemp array
    strTemp(2, b - 1) = InputBox("Please enter keyword" & b)



    maxColumn = WorksheetFunction.Max(numKeyA, numKeyB, numKeyC)
    ReDim strTemp(2, maxColumn)



Next


End Sub

The way the code is right now, I get an error when trying to redim the array at the end to size it based on the largest column. If I don't size the code at the beggining, I get an error "subscript out of range" for strTemp(0, k-1) = InputBox("please enter keyword" & k).

Answer 1

Public strTemp() As Variant

Leave out the indices in the declaration. Then you do need to ReDim before you try to put anything in strTemp. If you do all three InputBoxes first, you'll be able to size strTemp and then read in the keywords.

Alternatively, use

ReDim strTemp(2, numKeyA)

after the first InputBox, then say

ReDim Preserve strTemp(2, IIf(numKeyA>numKeyB, numKeyA, numKeyB))

after the second InputBox (the IIf is so you don't shrink the array). Preserve will keep values that are already there. Similarly, after the third InputBox,

ReDim Preserve strTemp(2, maxColumn)