Consulting

Results 1 to 9 of 9

Thread: Help, User input dynamic array

  1. #1
    VBAX Regular
    Joined
    Jul 2016
    Posts
    18
    Location

    Help, User input dynamic array

    Hi,

    I am very new to the forum and the world of VBA so apologies if I don't explain this well.

    I need to write a code that allows a user to input the number of variables and then for each of those variables ask a start time and an end time. These times will be used in a later code to separate large sections of data.
    So for instance, the user would execute the code which will ask for a number of sessions n. The code will then generate 2n variables (session_start(i), session_end(i) where i = 1 to n). Each of these require a user input of a time, for example session_start1 = 9:00 , session_end1 = 11:00, session_start2 = 11:30...etc.

    This is what I have so far:
    Sub UserSessionInput()
    Dim Sessions() As Variant, n As Variant, Session_start() As String, Session_end() As String
    Dim i As Integer
    n = InputBox("Please enter the number of Sessions per day:", "Survey Sessions")
    ReDim Sessions(0 To noSessions, 2)
    For i = 0 To n
    Session_start(i) = InputBox("Enter session start time in HH:MM")
    Session_end(i) = InputBox("Enter session end time in HH:MM")
       Next i
    End Sub
    As I said, I am very new to VBA and unfortunately I am stuck now.

    I also know that there will be an issue with the time format but not sure how to tackle that.

    Ultimately I believe the array should be something like below with all the values being input by the user.
    1 Session_start(1) Session_end(1)
    2 Session_start(2) Session_end(2)
    : : :
    n Session_start(n) Session_end(n)


    Any help is greatly appreciated. Thanks

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,876
    perhaps:
    Sub UserSessionInput()
    Dim Sessions(), n, i
    n = InputBox("Please enter the number of Sessions per day:", "Survey Sessions")
    ReDim Sessions(1 To n, 1 To 2)
    For i = 1 To n
      Sessions(i, 1) = InputBox("Enter session " & i & " start time in HH:MM")
      Sessions(i, 2) = InputBox("Enter session " & i & " end time in HH:MM")
    Next i
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular
    Joined
    Jul 2016
    Posts
    18
    Location
    Brilliant! Thanks p45cal.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    I'd suggest using a worksheet to keep the start and ends.

    1. One mistake and you have to start all over

    2. If you want to do something else, you don't have to enter the start and ends again

    3. DV takes care of making certain that only times are entered



    Data Validation will help

    Capture.JPG
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Here's an alternative. A user form to enter the session times in.

    ThisWorkbook Code. Holds the public Variant (Array) with the schedule times. Place a Break on line "Next" in Sub Test, then hover the mouse over X, Y, and i as you press F5 to cycle thru.
    Option Explicit
    
    Public SessionSchedule As Variant
    
    Private Sub Workbook_Open()
    frmSurveySessions.Show
    End Sub
    
    Public Sub test()
    Dim X, Y, i
    
    For i = LBound(SessionSchedule) To UBound(SessionSchedule)
    X = SessionSchedule(i, 1)
    Y = SessionSchedule(i, 2)
    Next
    End Sub
    The UserForm Code. Note that the Close Command button calls Test.
    Option Explicit
    
    Private Sub cbutClose_Click()
    ThisWorkbook.SessionSchedule = MakeSchedule
    Unload Me
    ThisWorkbook.test
    End Sub

    Private Sub tbxNumSessions_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Long
    
    For i = 2 To 16
    Me.Controls("Frame" & i).Visible = False
    Next i
    
    For i = 2 To CLng(Me.tbxNumSessions.Value)
    With Me.Controls("Frame" & i)
    .Visible = True
    .Height = 24
    .Top = Me.Controls("Frame" & i - 1).Top + 24
    End With
    Next i
    
    End Sub

    Private Function MakeSchedule() As Variant
    
    Dim NumSessions As Long
    Dim Tmp As Variant
    Dim i As Long
    Dim S As Long
    
    NumSessions = Me.tbxNumSessions.Value
    ReDim Tmp(1 To NumSessions, 1 To 2)
    
    With Me.Controls
      For i = 1 To NumSessions
        If .Item("Frame" & i).Visible Then
            S = S + 1
            Tmp(S, 1) = .Item("tbxSessionStart" & i).Value
            Tmp(S, 2) = .Item("tbxSessionEnd" & i).Value
        End If
      Next i
    End With
          
    MakeSchedule = Tmp
    End Function
    Download: Survey Sessions.xlsm

    This is just the basic outline. you still need to check each Time box for proper formatting, and I would use DateTime Serial values in the final array. They are much easier for all VBA and Excel to work with.

    The feature to change num-sessions on the fly is built in

    Some features that can be added:
    • Provide a "standard session length" and "standard time between" inputs, that would automatically fill in all the time slots.
    • Allow clearing the times of a session for breaks, lunches, etc
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    VBAX Regular
    Joined
    Jul 2016
    Posts
    18
    Location
    Paul, I see what you mean by having to start again if there is a mistake....

    I wonder if a UserForm could be created so that the user can enter the values all on one InputBox rather than having to go through say 24 individual screens if 12 sessions were entered initially.

    Ultimately, this will be a part of a much larger code to extract data and the times are simply going to be user input to break up the data as part of an IF statement. The user will not be familiar with excel so I want the program to prompt them to input various items as we go. So while I want to use excel as a basis and final output I want to keep the inputs and background calcs a fully automated process.

  7. #7
    VBAX Regular
    Joined
    Jul 2016
    Posts
    18
    Location
    Thanks SamT. That looks fantastic. I will make a few tweaks, but I think that should solve my problem.

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Pay careful attention to Control Names.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    SamT's UF is nice and certainly more expandable and more flexible, but remember you still have ...

    1. to check the inputs to make certain that they're times

    2. to make sure that both start and end times are entered

    3. allow user to correct bad inputs

    4. and possibly save the time pairs somewhere unless you want to re-enter them every time

    All that can be added to SamT's code
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

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