PDA

View Full Version : Help, User input dynamic array



BeachBum
08-01-2016, 12:30 AM
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

p45cal
08-01-2016, 03:30 AM
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

BeachBum
08-01-2016, 06:58 PM
Brilliant! Thanks p45cal.

Paul_Hossler
08-01-2016, 06:58 PM
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

16762

SamT
08-01-2016, 09:29 PM
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: 16764

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

BeachBum
08-01-2016, 09:37 PM
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.

BeachBum
08-01-2016, 09:56 PM
Thanks SamT. That looks fantastic. I will make a few tweaks, but I think that should solve my problem.

SamT
08-01-2016, 09:59 PM
Pay careful attention to Control Names.

Paul_Hossler
08-02-2016, 06:39 AM
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