Declaring a set of Variables as a Function of Another Variable

Question

I'm attempting to assign strings based on column entries of which the amount changes (may be 15-40 entries depending on the day), then open URLs by adding the strings to a fixed beginning of the URL. My issue is the assignment of the variables. My code is as follows:

'LOOP 1:
    'Table Entries

Dim RowCount As Integer


    Sheets("Table").Select
    Sheets("Table").Range("A2").Select
    Do While True
        ActiveCell.Offset(1, 0).Select
        If IsEmpty(ActiveCell.Value) Then
            RowCount = ActiveCell.Row
            Exit Do
        End If
    Loop

RowCount = RowCount - 2

'LOOP 2:
    'Assign IDs and URLs

Dim ID(1 To RowCount) As Variant
Dim URL(1 To RowCount) As String
Dim i1 As Integer

    For i1 = 1 To RowCount
        ID(i1) = Sheets("Table").Range("A" + CStr(i + 1)).Value
        URL(i1) = "--Redacted--" + CStr(URL(i1))
    Next i1

The issue is declaring the ID and URL variables as a function of RowCount. I'm sure there is another way to declare these, but my lack of experience is showing.

Thanks in advance.

What exactly is your problem? You say you have an issue but what is it? — SJR, Jul 17 '18 at 13:20
"My issue is the assignment of the variables" --- what variables? This is unclear. — John Coleman, Jul 17 '18 at 13:21
I recommend to read: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to make your code faster, shorter and more reliable. — Pᴇʜ, Jul 17 '18 at 13:36

score 7 · Accepted Answer · answered Jul 17 '18 at 13:26

7

As you have found:

Sub ThisFails()
    RowCount = 9
    Dim URL(1 To RowCount) As String
End Sub

does not work, but:

Sub ThisWorks()
    Dim URL() As String
    RowCount = 9
    ReDim URL(1 To RowCount)
End Sub

will work

answered Jul 17 '18 at 13:26

Gary's Student

95,722
10
59
99

score 0 · Answer 2 · answered Jul 17 '18 at 14:21

Looking at the thing you're trying to accomplish; an array with ["--redacted--" + string] you could try:

Sub Try()
Dim URL() As String
With Sheets("Table")
    URL = Split("--Redacted--" & Join(Application.Transpose(.Range(.Cells(2, 1), .Cells(1, 1).End(xlDown))), "|--Redacted--"), "|")
End With

End Sub

No need to loop or find an empty cell for your RowCount

Declaring a set of Variables as a Function of Another Variable

2 Answers2