Results 1 to 12 of 12
  1. #1
    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
    Where D1 to D15 are numbers which will be entered by me based on Project requirement. Once I got the Month number I will calculate the mobilization date with help of other table.
    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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
    Have a nice day!

  3. #3
    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

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Simply count top-down:
    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!

  5. #5
    Join Date
    Oct 2013
    Posts
    163
    Thank you very much Sinndho. That solved my problem.
    Thanks and Regards
    R. Vadivelan

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

  7. #7
    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
    But I am getting the value error in my file.
    Thanks and Regards
    R. Vadivelan

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you removed M00, the initialization of the loop must be:
    Code:
    For i = 1 To 14
    ...
    You can also use:
    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!

  9. #9
    Join Date
    Oct 2013
    Posts
    163
    Thank you very much Sinndho.

    It worked fine.
    Thanks and Regards
    R. Vadivelan

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

  11. #11
    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
    I had tried to modify the code but not succeded could you please help me solve this issue
    Thanks and Regards
    R. Vadivelan

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do it this way?

    use a calendar table which gives you this functionality
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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