Creath an array by input in VBA

Question

I would like to create an array to store the name of different departments of the company. First of all, I make users to input the total amount of departments and then create a array, allowing users to key in the name of departements. But the compiler said the index of array is out of range.

Dim myarray As Variant
myarray = Array

deptnum = InputBox("Please enter the total amount of departments.")

For k = 0 To deptnum
x = InputBox("Please enter the name of department:"
x = myarray(k)
Next

Dont't use an array. Instead, use a COllection, or better still, a scripting.dictionary. — freeflow
– freeflow, Commented Jul 20, 2020 at 8:50

Dang D. Khanh · Accepted Answer · 2020-07-20 09:03:13Z

1

If you still use the array, you can use the redim array, then use array(index) to assign the name you have entered.

Sub Test()
    Dim departments() As String
    Dim x As String

    deptnum = InputBox("Please enter the total amount of departments.")
    If Not (IsNumeric(deptnum)) Then Exit Sub
    ReDim departments(deptnum - 1)

    For k = 0 To deptnum - 1
        x = InputBox("Please enter the name of department:")
        departments(k) = x
    Next
End Sub

answered Jul 20, 2020 at 9:03

Dang D. Khanh

1,4618 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Creath an array by input in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related