CODEWITHSUNDEEP
vbaexcelvbscript
chris
chris

Reputation: 11

Pass user input from excel cells to an Array

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

Upvotes: 1

Views: 2087

Answers (3)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 1

YowE3K
YowE3K

Reputation: 23974

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:

  1. The array needs to be dimensioned as a Variant.

  2. 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)

  3. 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.

  4. 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

Upvotes: 0

mike morris
mike morris

Reputation: 174

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

Upvotes: 0

Related Questions