Results 1 to 12 of 12
Thread: Simplification in VBA Code

011015, 02:21 #1Registered User
 Join Date
 Oct 2013
 Posts
 163
Unanswered: Simplification in VBA Code
Dear Seniors,
I had created the following code for calculating the Mobilization month in my access database. It works fine however I would like to seek your help to simplify the code since I would like to expand this monthly code to Weekly Code.
Code:Public Function Mob_Start(D1 As Double, D2 As Double, D3 As Double, D4 As Double, D5 As Double, D6 As Double, D7 As Double, D8 As Double, D9 As Double, D10 As Double, D11 As Double, D12 As Double, D13 As Double, D14 As Double, D15 As Double) As String If D1 <> 0 Then Mob_Start = "M01" ElseIf D2 <> 0 Then Mob_Start = "M01" ElseIf D3 <> 0 Then Mob_Start = "M02" ElseIf D4 <> 0 Then Mob_Start = "M03" ElseIf D5 <> 0 Then Mob_Start = "M04" ElseIf D6 <> 0 Then Mob_Start = "M05" ElseIf D7 <> 0 Then Mob_Start = "M06" ElseIf D8 <> 0 Then Mob_Start = "M07" ElseIf D9 <> 0 Then Mob_Start = "M08" ElseIf D10 <> 0 Then Mob_Start = "M09" ElseIf D11 <> 0 Then Mob_Start = "M10" ElseIf D12 <> 0 Then Mob_Start = "M11" ElseIf D13 <> 0 Then Mob_Start = "M12" ElseIf D14 <> 0 Then Mob_Start = "M13" ElseIf D15 <> 0 Then Mob_Start = "M14" Else Mob_Start = "NA" End If End Function
Thanks and Regards
R. Vadivelan

011115, 15:21 #2Moderator
 Join Date
 Mar 2009
 Posts
 5,442
Provided Answers: 14This should work:
Code:Public Function NewMob_Start(ParamArray D() As Variant) As String Dim i As Long NewMob_Start = "NA" For i = 0 To 14 If IsMissing(D(i)) Then Exit For If D(i) <> 0 Then NewMob_Start = "M" & Format(i, "00") Exit For End If Next i End Function
Have a nice day!

011215, 08:59 #3Registered User
 Join Date
 Oct 2013
 Posts
 163
Thank you very much Sinndho.
That code works fine. Could you please explain me how can I modify the formula to calculate the Mobilization finish month. The following formula I had used to calculate the Finish Month earlier.
Code:Public Function Mob_Finish(D1 As Double, D2 As Double, D3 As Double, D4 As Double, D5 As Double, D6 As Double, D7 As Double, D8 As Double, D9 As Double, D10 As Double, D11 As Double, D12 As Double, D13 As Double, D14 As Double, D15 As Double) As String If D15 <> 0 Then Mob_Finish = "M14" ElseIf D14 <> 0 Then Mob_Finish = "M13" ElseIf D13 <> 0 Then Mob_Finish = "M12" ElseIf D12 <> 0 Then Mob_Finish = "M11" ElseIf D11 <> 0 Then Mob_Finish = "M10" ElseIf D10 <> 0 Then Mob_Finish = "M09" ElseIf D9 <> 0 Then Mob_Finish = "M08" ElseIf D8 <> 0 Then Mob_Finish = "M07" ElseIf D7 <> 0 Then Mob_Finish = "M06" ElseIf D6 <> 0 Then Mob_Finish = "M05" ElseIf D5 <> 0 Then Mob_Finish = "M04" ElseIf D4 <> 0 Then Mob_Finish = "M03" ElseIf D3 <> 0 Then Mob_Finish = "M02" ElseIf D2 <> 0 Then Mob_Finish = "M01" ElseIf D1 <> 0 Then Mob_Finish = "M01" Else Mob_Finish = "NA" End If End Function
Thanks and Regards
R. Vadivelan

011215, 09:25 #4Moderator
 Join Date
 Mar 2009
 Posts
 5,442
Provided Answers: 14Simply count topdown:
Code:Public Function NewMob_Finish(ParamArray D() As Variant) As String Dim i As Long NewMob_Start = "NA" For i = 14 To 0 Step 1 If IsMissing(D(i)) Then Exit For If D(i) <> 0 Then NewMob_Finish= "M" & Format(i, "00") Exit For End If Next i End Function
Have a nice day!

011215, 09:30 #5Registered User
 Join Date
 Oct 2013
 Posts
 163
Thank you very much Sinndho. That solved my problem.
Thanks and Regards
R. Vadivelan

011215, 11:07 #6Moderator
 Join Date
 Mar 2009
 Posts
 5,442
Provided Answers: 14You're welcome!
Have a nice day!

030915, 13:18 #7Registered User
 Join Date
 Oct 2013
 Posts
 163
Dear Sinndho,
With reference to your above code I can use the same in my project to calculate the Months. However I getting the error in the formula result since I deleted the 1 month data from my file. (i.e) M00 data is deleted and instead of 14 months now I have only 13 months to calculate the start and end month.
Code:Public Function NewMob_Start(ParamArray D() As Variant) As String Dim i As Long NewMob_Start = "NA" For i = 0 To 13 If IsMissing(D(i)) Then Exit For If D(i) <> 0 Then NewMob_Start = "M" & Format(i, "00") Exit For End If Next i End Function
Thanks and Regards
R. Vadivelan

030915, 13:56 #8Moderator
 Join Date
 Mar 2009
 Posts
 5,442
Provided Answers: 14If you removed M00, the initialization of the loop must be:
Code:For i = 1 To 14 ...
Code:For i = 0 To 13 If IsMissing(D(i)) Then Exit For If D(i) <> 0 Then NewMob_Start = "M" & Format(i + 1, "00") Exit For End If Next i
Have a nice day!

031115, 10:11 #9Registered User
 Join Date
 Oct 2013
 Posts
 163
Thank you very much Sinndho.
It worked fine.Thanks and Regards
R. Vadivelan

031115, 13:42 #10Moderator
 Join Date
 Mar 2009
 Posts
 5,442
Provided Answers: 14You're welcome!
Have a nice day!

051616, 00:01 #11Registered User
 Join Date
 Oct 2013
 Posts
 163
Dear Sinndho,
I was using the following code for finding the mobilization month in my project and it was working fine. But now i have a different situation where my mobilization starts from M06 to M60. This does not include M00.
Code:Public Function NewMob_Start(ParamArray D() As Variant) As String Dim i As Long NewMob_Start = "NA" For i = 0 To 14 If IsMissing(D(i)) Then Exit For If D(i) <> 0 Then NewMob_Start = "M" & Format(i+1, "00") Exit For End If Next i End Function
Thanks and Regards
R. Vadivelan

051616, 10:48 #12Jaded Developer
 Join Date
 Nov 2004
 Location
 out on a limb
 Posts
 13,692
Provided Answers: 59why do it this way?
use a calendar table which gives you this functionalityI'd rather be riding on the Tiger 800 or the Norton