CODEWITHSUNDEEP
functionexcelif-statementoffice-2013vba
Tarik
Tarik

Reputation: 77

If-statement routine into a function

Perhaps you could help me I stuck on this problem. ckuhn203 have suggested me to break down all of the repeated if-statement into a function. I am trying but I can't figure out what I am doing wrong. I had this sub:

Sub st_verdelen()
    Worksheets("totaallijst").Activate
    lastRtot = Sheets("totaallijst").Cells.SpecialCells(xlCellTypeLastCell).row
    counter_all = 0

    For a = 1 To lastRtot
        slbkoppeling = Sheets("totaallijst").Cells(a, 8).Value
        slbAanwezig = Sheets("totaallijst").Cells(a, 9).Value

        If Sheets("totaallijst").Cells(a, 7).Value = "2A2" And SLBers = "Mvs" And slbstdperklas = Sheets("StdntKlas").Cells(15, 5).Value And slbkoppeling = "" And slbAanwezig = "" Then
            If counter_all <= slbstdperklas And UUU = 0 Then
                Sheets("totaallijst").Cells(a, 9).Value = SLBers
                counter_all = counter_all + 1
                If counter_all = slbstdperklas Then
                    UUU = 1
                End If
            End If
        End If

        If Sheets("totaallijst").Cells(a, 7).Value = "1B4C" And SLBers = "htp" And slbstdperklas = Sheets("StdntKlas").Cells(16, 5).Value And slbkoppeling = "" And slbAanwezig = "" Then
            If counter_all <= slbstdperklas And VVV = 0 Then
                Sheets("totaallijst").Cells(a, 9).Value = SLBers
                counter_all = counter_all + 1
                If counter_all = slbstdperklas Then
                    VVV = 1
                End If
            End If
        End If

        If Sheets("tot_list").Cells(a, 7).Value = "2G4" And blsers = "htp" And blsstdperklas = Sheets("StdntKlas").Cells(16, 6).Value And blskoppeling = "" And blsAanwezig = "" Then
            If counterW <= blsstdperklas And WWW = 0 Then
                Sheets("tot_list").Cells(a, 9).Value = blsers
                counter_all = counter_all + 1

                If counterW = blsstdperklas Then
                    WWW = 1
                End If
            End If
        End If
next a
end sub

I tried to chop them into pieces but unfortunatly without success. I have done something like this:

Sub st_verdelen()
    Worksheets("totaallijst").Activate
    lastRtot = Sheets("totaallijst").Cells.SpecialCells(xlCellTypeLastCell).row
    counter_all = 0

For a = 1 To lastRtot
    If scan_slb("VCIT2A2", "Mvs", 15, 5, 0) Then

    End If
    If scan_slb("VCIT2A2", "htp", 16, 5, 0) Then

    End If
    If scan_slb("VCIT2A2", "htp", 16, 5, 0) Then

    End If
next a

end sub

Function scan_slb(value1, value2, row, col, noneactief) As Boolean
    scan_slb = (Sheets("totaallijst").Cells(a, 7).Value = value1 And SLBers = value2 And slbstdperklas = Sheets("StdntKlas").Cells(row, col).Value And slbkoppeling = "" And slbAanwezig = "")
    counter_all = counter_all + 1
    If counter_all = slbstdperklas Then
       noneactief = 1
    End If
End Function

I can't figure out what i am doing wrong. Any suggestions?

The counter_all is needed so that it will not repeat this section again. If I leave it out then I can't activate noneactief so that the if statement will not be activated after it has been activated ones.

Upvotes: 0

Views: 67

Answers (1)

klausnrooster
klausnrooster

Reputation: 560

Function scan_slb(value1, value2, row, col, noneactief) appears to have some problems, or not enough context. Using "row" as an argument name is not good practice as it is an property name. You are sometimes modifying the value of noneactief which you passed to the function. That is fine since excel-vba default is pass-by-reference; but the calling code isn't using noneactief anyway so you need to write code to do something with it. I'll make a few assumptions and have a shot at this:

counter_all = 0

For a = 1 To lastRtot
    slbkoppeling = Sheets("totaallijst").Cells(a, 8).Value
    slbAanwezig = Sheets("totaallijst").Cells(a, 9).Value

    If scan_slb(sheets("totaallijst").Cells(a, 7).Value, "2A2",  _ 
      SLBers, "Mvs", slbstdperklas, Sheets("StdntKlas").Cells(15, 5).Value, _ 
      slbkoppeling, "", slbAanwezig, "") Then
        If counter_all <= slbstdperklas And UUU = 0 Then
            Sheets("totaallijst").Cells(a, 9).Value = SLBers
            counter_all = counter_all + 1
            If counter_all = slbstdperklas Then
                UUU = 1
            End If
        End If
    End If

    If scan_slb(Sheets("totaallijst").Cells(a, 7).Value, "1B4C", _ 
      SLBers, "htp", slbstdperklas, Sheets("StdntKlas").Cells(16, 5).Value, _ 
      slbkoppeling, "", slbAanwezig, "") Then
        If counter_all <= slbstdperklas And VVV = 0 Then
            Sheets("totaallijst").Cells(a, 9).Value = SLBers
            counter_all = counter_all + 1
            If counter_all = slbstdperklas Then
                VVV = 1
            End If
        End If
    End If

    If scan_slb(Sheets("tot_list").Cells(a, 7).Value, "2G4", _ 
      blsers, "htp", blsstdperklas, Sheets("StdntKlas").Cells(16, 6).Value, _ 
      blskoppeling, "", blsAanwezig, "") Then
        If counterW <= blsstdperklas And WWW = 0 Then
            Sheets("tot_list").Cells(a, 9).Value = blsers
            counter_all = counter_all + 1

            If counterW = blsstdperklas Then
                WWW = 1
            End If
        End If
    End If
next a
End Sub

function scan_slb(a1,a2,b1,b2,c1,c2,d1,d2)
    scan_slb = (a1 = a2 and b1 = b2 and c1 = c2 and d1 = d2)  
End Function

Assuming you have global variable in addition to counter_all, and that variables you are testing for "" are string types. More of this can be factored to use functions, especially if you are comfortable mutating variables passed ByRef. Personally I'd put "ByVal " before all the variables in scan_slb.

Upvotes: 1

Related Questions