locked
VBA InputBox that returns array of values RRS feed

  • Question

  • I am attempting to have an InputBox request values from a user and place those values into an array.

    The problem I am having is that the InputBox seems to only want to accept delimited values as a string. Thus, requiring me to set the return data type to Type:=2. I have tried Type:= 64 which, according to Microsoft, is the array return data type but was unsuccessful and info on Type:= 64 is scarce.

    Because of this I am making the array equal to the split of the string. However, this fills my array with numbers that are formatted as text(not numeric).

    This is what I have attempted:

    Sub test_split()

        Dim TestArray As Variant
        Dim Proj As Long
        Dim InStng As String
        
        Proj = 6 

        InStng = Application.InputBox("List the values separated by commas:", "Values", Type:=2)
        
        TestArray = Split(InStng, ",")
       
        Range(Cells(20, 1), Cells(20, Proj)) = TestArray
       
    End Sub

    Is there a way to easily input delimited values into an array with them formatted as numeric values? If not, is there a way to convert the entire elements of an array to Long?

    Wednesday, September 10, 2014 1:37 AM

Answers


  • If you want to obtain an array using the Type:=64 you need to prompt the user to enter values into the box like this:

    {value, value, value, ...} 

    which should rather be:

    {1,2,3,4,1234,12345}

    Try the below code:

    Sub SixtyFour()
    
        Dim uInput As Variant
        uInput = Application.InputBox( _
        "List the values in this format: " & vbCrLf & _
        "{value, value, value, ...}", Type:=64)
        
        'optionally you may want to
        ' preformat row 2 to accept numbers
        Rows("2:2").NumberFormat = "0"
        
        Range("A2").Resize(1, UBound(uInput)) = uInput
    
    End Sub

    Rule no. 1: Never trust users input!

      * Please note that you should add a mechanism to check whether all the values entered in the InputBox are correct and in the correct format.




    • Edited by Michal Krzych Wednesday, September 10, 2014 8:08 AM
    • Proposed as answer by danishani Thursday, September 11, 2014 4:29 PM
    • Marked as answer by Name_As_String Sunday, September 14, 2014 9:39 PM
    Wednesday, September 10, 2014 8:05 AM

All replies

  • Try:

    Sub test_split()
    Dim TestArray As Variant
    Dim Proj As Long
    Dim InStng As String
    Dim L As Long
        Proj = 6
        InStng = Application.InputBox("List the values separated by commas:", "Values", Type:=2)
        TestArray = Split(InStng, ",")
        For L = 0 To UBound(TestArray)
            If Val(TestArray(L)) > 0 Then
                TestArray(L) = Val(TestArray(L))
            End If
        Next L
        Range(Cells(20, 1), Cells(20, Proj)) = TestArray
    End Sub


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Wednesday, September 10, 2014 2:44 AM
  • Unfortunately, that does not work. The numbers are still stored as text in the cells. Thanks for trying.
    Wednesday, September 10, 2014 2:51 AM

  • If you want to obtain an array using the Type:=64 you need to prompt the user to enter values into the box like this:

    {value, value, value, ...} 

    which should rather be:

    {1,2,3,4,1234,12345}

    Try the below code:

    Sub SixtyFour()
    
        Dim uInput As Variant
        uInput = Application.InputBox( _
        "List the values in this format: " & vbCrLf & _
        "{value, value, value, ...}", Type:=64)
        
        'optionally you may want to
        ' preformat row 2 to accept numbers
        Rows("2:2").NumberFormat = "0"
        
        Range("A2").Resize(1, UBound(uInput)) = uInput
    
    End Sub

    Rule no. 1: Never trust users input!

      * Please note that you should add a mechanism to check whether all the values entered in the InputBox are correct and in the correct format.




    • Edited by Michal Krzych Wednesday, September 10, 2014 8:08 AM
    • Proposed as answer by danishani Thursday, September 11, 2014 4:29 PM
    • Marked as answer by Name_As_String Sunday, September 14, 2014 9:39 PM
    Wednesday, September 10, 2014 8:05 AM
  • That was quite helpful and thanks for the advice.
    Thursday, September 11, 2014 3:58 AM