Pass user input from excel cells to an Array

Question

I am very new to VBA, so I apologize if this is a very simple question. I am trying to pass user input data into an array. Actually, 4 different arrays. All 4 arrays can have up to 3 elements, but could only need one at any given time. They are then sorted a specific way via For Loops and then will output the sendkeys function to the active window (which will not be excel when it is running). I have the for loops figured out and it is sorting the way i need it to. I just need to be able to get the user input into those arrays and then output them to a phantom keyboard (i.e. sendkeys). I appreciate any help or advice!

FYI, I have declared the arrays as strings and the variables as long... the message boxes are there to just test the sort, they are not very important

    For i = 0 To UBound(SheetPosition)
        If j = UBound(Position) Then
            j = 0
        End If
        For j = 0 To UBound(Position)
            If k = UBound(Direction) Then
                k = 0
            End If
            For k = 0 To UBound(Direction)
                If l = UBound(Temper) Then
                    l = 0
                End If
                For l = 0 To UBound(Temper)
                    MsgBox(i)
                    MsgBox(SheetPosition(i))
                    MsgBox(j)
                    MsgBox(Position(j))
                    MsgBox(k)
                    MsgBox(Direction(k))
                    MsgBox(l)
                    MsgBox(Temper(l))

                Next
            Next
        Next
    Next

user3598756 · Accepted Answer · 2016-12-31 07:30:00Z

1

you could use Application.InputBox() method in two ways:

Dim myArray As Variant

myArray = Application.InputBox("List the values in the following format: " & vbCrLf & "{val1, val2, val3, ...}", Type:=64) '<--| this returns an array of 'Variant's

myArray = Split(Application.InputBox("List the values in the following format: " & vbCrLf & "val1, val2, val3, ...", Type:=2), ",") '<--| this returns an array of 'String's

answered Dec 31, 2016 at 7:30

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

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

Comments

elixenide · Accepted Answer · 2016-12-31 04:46:49Z

0

Yes, you could get the input from the user using Input boxes:

myValue = InputBox("Give me some input")

Or forms, which is the preferred method. Unfortunately, forms take some time to develop and are best deployed through Excel add-ins, which also require time to learn how to setup.

Here is a good tutorial on using the SendKeys method: http://www.contextures.com/excelvbasendkeys.html

edited Dec 31, 2016 at 4:46

elixenide

45k16 gold badges79 silver badges103 bronze badges

answered Dec 31, 2016 at 4:18

mike morris

1747 bronze badges

Comments

YowE3K · Accepted Answer · 2016-12-31 07:16:20Z

The usual way of getting data from cells into an array would be:

Dim SheetPosition As Variant
SheetPosition = Range("A1:A3").Value

or perhaps

Dim SheetPosition As Variant
SheetPosition = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value

A few things to note:

The array needs to be dimensioned as a Variant.
The dimension of the array will be rows x columns, so in the first example above SheetPosition will be dimensioned 1 To 3, 1 To 1, and in the second example it might be dimensioned 1 To 5721, 1 To 1 (if the last non-empty cell in column A was A5721)
If you need to find the dimensions of a multi-dimensioned array, you should use UBound(SheetPosition, 1) to find the upper bound of the first dimension and UBound(SheetPosition, 2) to find the upper bound of the second dimension.
Even if you include Option Base 0 at the start of your code module, the arrays will still be dimensioned with a lower bound of 1.

If you want a single dimensioned array and your user input is in a column, you can use Application.Transpose to achieve this:

Dim SheetPosition As Variant
SheetPosition = Application.Transpose(Range("A1:A3").Value)

In this case SheetPosition will be dimensioned 1 To 3.

If you want a single dimensioned array and your user input is in a row, you can still use Application.Transpose to achieve this, but you have to use it twice:

Dim SheetPosition As Variant
SheetPosition = Application.Transpose(Application.Transpose(Range("A1:C1").Value))

FWIW - Your If statements in the code in the question are not achieving anything - each of the variables that are being set to 0 are going to be set to 0 by the following For statements anyway. So your existing code could be:

For i = LBound(SheetPosition) To UBound(SheetPosition)
    For j = LBound(Position) To UBound(Position)
        For k = LBound(Direction) To UBound(Direction)
            For l = LBound(Temper) To UBound(Temper)
                MsgBox i
                MsgBox SheetPosition(i)
                MsgBox j
                MsgBox Position(j)
                MsgBox k
                MsgBox Direction(k)
                MsgBox l
                MsgBox Temper(l)
            Next
        Next
    Next
Next

Collectives™ on Stack Overflow

Pass user input from excel cells to an Array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related