userform with textboxes and dates to show timeline

Question

I am writing the following VBA code to make a timeline, which I will be using for other parts of the code later on for other calculations and output. I have a userform where the following number of text boxes and labels are present:

• wkbox* and intbox* are textboxes which have weeks as input from user. wkbox* is duration of a stage, intbox* is interval between two stages.
• date* are labels which give output as dates when each stage starts.
• mpsd is the input date for the entire timeline start date from mpsd.text box, which is considered as the start date in my code below

Public mpsd as date 
Private Sub Setbutton_Click()
 
 Me.date1 = mpsd
 Me.date2 = DateAdd("d", ((wkbox1 * 5) + (intbox1 * 5)), date1)
 Me.date3 = DateAdd("d", ((wkbox2 * 5) + (intbox2 * 5)), date2)
 Me.date4 = DateAdd("d", ((wkbox3 * 5) + (intbox3 * 5)), date3)
 Me.date5 = DateAdd("d", ((wkbox4 * 5) + (intbox4 * 5)), date4)
 Me.date6 = DateAdd("d", ((wkbox5 * 5) + (intbox5 * 5)), date5)
 Me.date7 = DateAdd("d", ((wkbox6 * 5) + (intbox6 * 5)), date6)
 Me.date8 = DateAdd("d", ((wkbox7 * 5) + (intbox7 * 5)), date7)
 Me.date9 = DateAdd("d", ((wkbox8 * 5) + (intbox8 * 5)), date8)
 Me.date10 = DateAdd("d", ((wkbox9 * 5) + (intbox9 * 5)), date9)
 Me.date11 = DateAdd("d", ((wkbox10 * 5) + (intbox10 * 5)), date10)
 Me.date12 = DateAdd("d", ((wkbox11 * 5) + (intbox11 * 5)), date11)
 
End sub

I was trying to think if there is a way to optimise this code through for,case, etc.

I am very new to VBA, so am not able to think how I can reduce this to a maybe 3 line for or case code to speed up the running of the code.

(This is just a part of a big schedule planner code, so any sec saved is good for me.) Is there a way to do it?

• πάντα ῥεῖ: What's annoying my eyes first is having member variables like Me.date1 to Me.dateN, which should be rather replaced with an array Me.dates[N] and and a properly indexing loop to call DateAdd().
• Barbarian: Thank you for your input, @πάντα ῥεῖ. However, I dont understand date1 to dateN are actual labels on my userform. I am unable to replace them with an array. It gives me a compile error. Sorry, I am very new to userform part of VBA :)
• Barbarian: my code runs properly. It is quite simple, input is start date and weeks for each stage from the user. Output is start date for each stage. I only wanted to simplify it, if possible. As you said. the N lines of date1 to dateN is annoying to read.
• Sᴀᴍ Onᴇᴌᴀ: Welcome to Code Review! I changed the title so that it describes what the code does per site goals: "State what your code does in your title, not your main concerns about it.". Feel free to edit and give it a different title if there is something more appropriate.
• Peilonrayz: Sam and I voted to reopen in unison. If you disagree you can reach out to me.

Answer 1

We can use Me.Controls to reference the Controls by name. Although, it looks cleaner there will be no noticeable change in performance.

Public mpsd As Date
Private Sub Setbutton_Click()
 Me.date1 = mpsd
 Dim n As Long
 For n = 1 To 11
 With Me
 .Controls("date" & n + 1) = DateAdd("d", ((.Controls("wkbox" & n) * 5) + (.Controls("intbox" & n) * 5)), .Controls("date" & n))
 End With
 Next
End Sub

• Greedo: Not sure about using + to concat, maybe it works with operator precedence but I would say it is a bit of a surprising behaviour in vba and doesn't work for all situations, no reason not to use & (or is there?)
• TinMan: Did I really do that! LOL..too much Java. JavaScript that is. Thanks @Greedo.
• Greedo: I thought you were using some weird behaviour of the + operator learn.microsoft.com/en-us/office/vba/language/reference/… but turns out that requires the numbers to be enclosed in strings so wouldn't work in this context