Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Arrays - creating (Excel 97)

    Here's my question, I want to create an Array, that the user creates by their input

    ARRX () as String

    Redim ARRX(1)
    ARRX(1) = Userinput
    Is there a way to fill the array with user input?

    Thanks, Darryl.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Arrays - creating (Excel 97)

    The easiest way is to let the user fill a series of cells, then use code to create an array from that. But, as with your previous question (<post#=382210>post 382210</post#>), it would help if you provided more details, and told us what you want to accomplish with the array.

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Arrays - creating (Excel 97)

    Thanks Hans,

    What I am trying to acommplish is to fill a combobox, with an Array, but have array formed by the User, not by me I would like to have it in code so that without the user knowing it they are creating the array, I only want them to see it in the combobox, Enough info? I guess part of my problem is that I don't understand the potential use of an array, other than a filling a listbox, or a combobox.

    Darryl.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays - creating (Excel 97)

    Does this do what you want?

    <pre>Dim ARRX() As String
    Dim strIn As String
    Dim I As Long
    I = 0
    Do While True
    strIn = InputBox("Enter next input", "Enter")
    If strIn = "" Then Exit Do
    I = I + 1
    ReDim Preserve ARRX(1 To I)
    ARRX(I) = strIn
    Loop
    </pre>

    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Arrays - creating (Excel 97)

    You may want to think a little differently. Check out how I used Data Validation in the attached workbook. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Arrays - creating (Excel 97)

    <P ID="edit" class=small>(Edited by sdckapr on 20-Jun-04 07:13. Added PS)</P>Here is an another way by using a text box (named textbox1) and a commandbutton (named commandButton1) on a userform. The textbox must have multiline set to True and the EnterKeyBehavior set to true(setting both of these to true has the enter adding a new line to the lable, instead of going to next field)

    Add this code to the useform code.

    <pre>Option Explicit
    Private Sub CommandButton1_Click()
    Dim sLabel As String
    Dim lLineCount As Long
    Dim x As Long
    Dim lCurrent As Long
    Dim lNext As Long
    Dim sArray() As String
    sLabel = TextBox1.Value
    sLabel = Application.WorksheetFunction.Substitute(sLabel, vbCrLf, vbLf)
    If Right(sLabel, 1) <> vbLf Then _
    sLabel = sLabel & vbLf
    lLineCount = Len(Application.WorksheetFunction.Substitute(sLabe l, vbLf, ""))
    lLineCount = Len(sLabel) - lLineCount
    ReDim sArray(1 To lLineCount)
    sLabel = vbLf & sLabel
    lNext = 1
    For x = 1 To lLineCount
    lCurrent = InStr(lNext, sLabel, vbLf)
    lNext = InStr(lCurrent + 1, sLabel, vbLf)
    sArray(x) = Mid(sLabel, lCurrent + 1, lNext - lCurrent - 1)
    Next
    Unload Me
    End Sub</pre>


    Calling the userform will display the textbox and command button. After you fill in the textbox with items, it will be parsed to separate the items and put them into an array based on when the <enter> was placed. Currently it ignores an extra <enter> made at the end, but internal "extra enters" are assumed will add null strings to the array. The array is a string, though you could add code to validate and convert to numbers or whatever if so desired (though this might require some error handling to determine what type it should be)

    Steve
    PS another enhancement:
    If you also set the TabKeyBehavior to True, you could create 2-D arrays using the TAB (vbTab) as the column delimiter. Then you could parse each "row", then go thru each row and see how many columns it has and if it has more than you currently have, then redim preserve the array and add the required columns, then parse the row for the columns. At the end you would have a 2-d array.

  7. #7
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Arrays - creating (Excel 97)

    Thanks all,

    I'm gonna take a course on VBA, and see if I have a moment of enlightenment The Help files only take me so far

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •