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

011015, 02:21 #1Registered User
 Join Date
 Oct 2013
 Posts
 165
Answered: 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

Sinndho
Posted by"This 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

011115, 15:21 #2Moderator
 Join Date
 Mar 2009
 Posts
 5,442
Provided Answers: 15This 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
 165
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: 15Simply 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
 165
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: 15You're welcome!
Have a nice day!

030915, 13:18 #7Registered User
 Join Date
 Oct 2013
 Posts
 165
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: 15If 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
 165
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: 15You're welcome!
Have a nice day!

051616, 00:01 #11Registered User
 Join Date
 Oct 2013
 Posts
 165
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

042317, 09:25 #13Registered User
 Join Date
 Oct 2013
 Posts
 165
Dear Sinndho,
Could you please help me to solve the problem as above. I already fixed the code little bit as below and it works fine. My current manning plan starts from M05 to M59 and it includes M00. This function was working perfectly, but I have a strange problem for some people (very few) where the Man hours end on M19 and later mobilized again in M58 for As built Works, however the function shows only M19.
Code:Public Function NewMob_Finish(ParamArray D() As Variant) As String Dim i As Long NewMob_Finish = "NA" For i = 59 To 0 Step 1 If IsMissing(D(i)) Then Exit For If D(i) <> 0 Then NewMob_Finish = "M" & Format(i  6, "00") Exit For End If Next i End Function
Thanks and Regards
R. Vadivelan

042317, 09:27 #14Registered User
 Join Date
 Oct 2013
 Posts
 165
Dear Healdem,
I tried to use the calendar table, But it works fine if I you use array formulas. I am sharing the workbook with other users, so I can't use the array formulas. I tried to use the vlookup with min and max but it does not works.Thanks and Regards
R. Vadivelan