VBA, Create array dynamically using an Input Box

[darkdimension]

Can someone demonstrate how to create an array dynamically using an Input Box?

The length of array is determined by user input, i.e., random number of elements.

Thanks in advance.

 

[xld]

Try

Dim rng As Range
Dim myArray As Variant

    Set rng = Application.InputBox("Select target range with the mouse", Type:=8)
    If Not rng Is Nothing Then
    
        myArray = rng
    End If

 

[darkdimension]

Thank you very much. That is great information. I wasn't even considering having the user select a range by mouse.

What if the input would be entered sequentially, number by number? How do you take each inputted element and create an array?

Thank you, again.

 

[xld]

Can you explain how you would envisage them being 'entered number by number'?

 

[darkdimension]

Here is part of my code. I have a 'Do' loop doing the heavy lifting:

...
Do
STA = Application.InputBox(Prompt:="Station", Title:="STATION #")
...
(Some code in here that checks to see if they are ending input
for the stations and need to begin elevations)
...

Selection.NumberFormat = "00+00"
ActiveCell.FormulaR1C1 = STA

If STA <> "" Then 'In the excluded code, if the input
ActiveCell.Offset(1, 0).Select 'comes back as null, STA = ""
End If

Loop
...

I would like to retain the user inputted data in an array and use it to perform some calculations and throw the results to a template.

So, my STA loop ends and goes to an Elevation loop. The Elevation loop starts one column to the left and the exact same row as the STA first inputted data.

Now, I have two columns with paired data. I would think I would want that in an array to pass to a function.

Any ideas?

Thank you for your time.

 

[mikerickson]

Dim myArray() as Variant, myArrayPointer as Long
Dim uiValue as Variant

Redim myArray(0 to 0)
myArrayPointer = 1

uiValue = Application.InputBox("EnterSomething", type:=1)

Do Until uiValue = False
    myArrayPointer = myArrayPointer + 1
    If myArrayPointer > UBound(myArray) Then
        Redim Preserve myArray(1 to 2 * myArrayPointer)
    End If
    myArray(myArrayPointer) = uiValue
Loop

If myArrayPointer = 0 Then
    MsgBox "nothing entered"
Else
    Redim Preserve myArray(1 to myArrayPointer)
    MsgBox myArrayPointer & " elements entered"
End If

 

[darkdimension]

@ MikeRickson

Thank you for your input.

I needed to make a couple of changes but it is gonna work the way I need it.

Thanks, again.

Option Explicit

Sub ArrayOfFun()

Dim myArray() As Variant, myArrayPointer As Long
Dim uiValue As Variant

ReDim myArray(1 To 1)
myArrayPointer = 1

uiValue = Application.InputBox("EnterSomething", Type:=1)

Do Until uiValue = False

uiValue = Application.InputBox("EnterSomething", Type:=1)

If myArrayPointer > UBound(myArray) Then
ReDim Preserve myArray(1 To myArrayPointer)
End If
myArray(myArrayPointer) = uiValue

If uiValue <> False Then
myArrayPointer = myArrayPointer + 1
End If

Loop

If myArrayPointer = 0 Then
MsgBox "nothing entered"
Else
ReDim Preserve myArray(1 To myArrayPointer)
MsgBox myArrayPointer & " elements entered"
End If
End Sub

I guess I never understood that I needed to initialize my array and then redim.

Thank you all for your help.