Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18

    Question Unanswered: Compile Error: Procedure too long

    Hello all,

    My problem is fairly straight forward, I need more efficient code as I get: Compile Error: Procedure too long.

    I have a button click procedure on my excel spreadsheet which first looks at a checkbox on sheet2,

    If the checkbox on sheet2 is selected it goes back to sheet1 and looks at a series of 10 checkboxes.

    If these checkboxes are not selected it enters in "NA" or "not applicable" in a range of cells.

    The code I have written for this procedure is much too long and I continue to get the compile error mentioned above.

    What can I do to shorten my VB code (see small excerpt of code below) to make it more efficient within my Excel spreadsheet.

    Thanks for any help I can get. This is definitely a programming question, out of my league.

    Code begins:

    Private Sub CommandButton1_Click()

    IF CheckBoxSpecies1 = True Then

    'Bedrock
    If Sheets(“Step 1”).CheckBox1 = True Then

    Else
    Sheets("Step 2").Range("C11:G11") = "NA"
    Sheets("Step 2").Range("J11:N11") = "NA"
    Sheets("Step 2").Range("C27:G27") = "NA"
    Sheets("Step 2").Range("J27:N27") = "NA"
    End If

    'Boulder
    If Sheets(“Step 1”).CheckBox2 = True Then

    Else
    Sheets("Step 2").Range("C12:G12") = "NA"
    Sheets("Step 2").Range("J12:N12") = "NA"
    Sheets("Step 2").Range("C28:G28") = "NA"
    Sheets("Step 2").Range("J28:N28") = "NA"
    End If

    'Rubble
    If Sheets(“Step 1”).CheckBox3 = True Then

    Else
    Sheets("Step 2").Range("C13:G13") = "NA"
    Sheets("Step 2").Range("J13:N13") = "NA"
    Sheets("Step 2").Range("C29:G29") = "NA"
    Sheets("Step 2").Range("J29:N29") = "NA"
    End If

    'Cobble
    If Sheets(“Step 1”).CheckBox4 = True Then

    Else
    Sheets("Step 2").Range("C14:G14") = "NA"
    Sheets("Step 2").Range("J14:N14") = "NA"
    Sheets("Step 2").Range("C30:G30") = "NA"
    Sheets("Step 2").Range("J30:N30") = "NA"
    End If

    'Gravel
    If Sheets(“Step 1”).CheckBox5 = True Then

    Else
    Sheets("Step 2").Range("C15:G15") = "NA"
    Sheets("Step 2").Range("J15:N15") = "NA"
    Sheets("Step 2").Range("C31:G31") = "NA"
    Sheets("Step 2").Range("J31:N31") = "NA"
    End If

    'Sand
    If Sheets(“Step 1”).CheckBox6 = True Then

    Else
    Sheets("Step 2").Range("C16:G16") = "NA"
    Sheets("Step 2").Range("J16:N16") = "NA"
    Sheets("Step 2").Range("C32:G32") = "NA"
    Sheets("Step 2").Range("J32:N32") = "NA"
    End If

    'Silt
    If Sheets(“Step 1”).CheckBox7 = True Then

    Else
    Sheets("Step 2").Range("C17:G17") = "NA"
    Sheets("Step 2").Range("J17:N17") = "NA"
    Sheets("Step 2").Range("C33:G33") = "NA"
    Sheets("Step 2").Range("J33:N33") = "NA"
    End If

    'Clay
    If Sheets(“Step 1”).CheckBox8 = True Then

    Else
    Sheets("Step 2").Range("C18:G18") = "NA"
    Sheets("Step 2").Range("J18:N18") = "NA"
    Sheets("Step 2").Range("C34:G34") = "NA"
    Sheets("Step 2").Range("J34:N34") = "NA"
    End If

    'Muck
    If Sheets(“Step 1”).CheckBox9 = True Then

    Else
    Sheets("Step 2").Range("C19:G19") = "NA"
    Sheets("Step 2").Range("J19:N19") = "NA"
    Sheets("Step 2").Range("C35:G35") = "NA"
    Sheets("Step 2").Range("J35:N35") = "NA"
    End If

    'Pelagic
    If Sheets(“Step 1”).Sheets(“Step 1”).CheckBox10 = True Then

    Else
    Sheets("Step 2").Range("C20:G20") = "NA"
    Sheets("Step 2").Range("J20:N20") = "NA"
    Sheets("Step 2").Range("C36:G36") = "NA"
    Sheets("Step 2").Range("J36:N36") = "NA"
    End If

    ELSEIF CheckBoxSpecies2 = True Then

    'Bedrock
    If Sheets(“Step 1”).CheckBox1 = True Then

    Else
    Sheets("Step 2").Range("C49:G49") = "NA"
    Sheets("Step 2").Range("J49:N49") = "NA"
    Sheets("Step 2").Range("C65:G65") = "NA"
    Sheets("Step 2").Range("J65:N65") = "NA"
    End If

    'Boulder
    If Sheets(“Step 1”).CheckBox2 = True Then

    Else
    Sheets("Step 2").Range("C50:G50") = "NA"
    Sheets("Step 2").Range("J50:N50") = "NA"
    Sheets("Step 2").Range("C66:G66") = "NA"
    Sheets("Step 2").Range("J66:N66") = "NA"
    End If
    .
    .
    .
    [Continues thru to CheckBoxSpecies29, so 1- 29]

    END IF

    End Sub

  2. #2
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Any chance you can post the xls? Chances are you need to do more work in the spreadsheet and less in VBA. You'll have to zip it before you post.

  3. #3
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18

    Question

    Can't post the xls, for obvious reasons, but I can post the entire code procedure. Not sure if this will help. But worth a try.

    Thanks
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi

    how about using your code like this

    Code:
    Private Sub CommandButton1_Click()
        Dim MyVal1 As Integer, MyVal2 As Integer
        
        If CheckBoxSpecies1.Value = True Then
            MyVal1 = 11
            MyVal2 = 27
        ElseIf CheckBoxSpecies2 = True Then
            MyVal1 = 49
            MyVal2 = 65
        ElseIf CheckBoxSpecies3 = True Then
            MyVal1 = 87
            MyVal2 = 103
        'etc
        End If
        
        With Worksheets("Step2")
            If Not Worksheets("Step1").CheckBox1.Value = True Then
                Union(.Range("C" & MyVal1 & ":G" & MyVal1), .Range("C" & MyVal2 & ":G" & MyVal2)).Formula = "NA"
                Union(.Range("J" & MyVal1 & ":N" & MyVal1), .Range("J" & MyVal2 & ":N" & MyVal2)).Formula = "NA"
            End If
            If Not Worksheets("Step1").CheckBox2.Value = True Then
                Union(.Range("C" & MyVal1 + 1 & ":G" & MyVal1 + 1), .Range("C" & MyVal2 + 1 & ":G" & MyVal2 + 1)).Formula = "NA"
                Union(.Range("J" & MyVal1 + 1 & ":N" & MyVal1 + 1), .Range("J" & MyVal2 + 1 & ":N" & MyVal2 + 1)).Formula = "NA"
                'etc
            End If
        End With
    End Sub
    ive done the first 3 species checkboxes and the first 2 checkboxes
    for the further species checkboxes myVal1 and myVal2 are just the rows on which the first lines of "NA" are Supposed to go
    in The Subsequent Checkboxs Checkbox3 etc. keep adding to the value after myval and myval2 in such a way that checkboxN add N-1

    Hope this Helps

    David

  5. #5
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18
    Hello David,

    Thanks for your help, the code did work out. I also have an alternative method for anybody visiting this post sometime in the future.

    This code works as well:

    Dim WS1 As Worksheet, WS2 As Worksheet
    Dim Rng As Range, i As Integer

    Set WS1 = Sheets("Step 1")
    Set WS2 = Sheets("Step 2")
    Set Rng1 = WS2.Range("C11:G11, J11:N11, C27:G27, J27:N27")
    ...
    Set Rng29 = WS2.Range("C1075:G1075, J1075:N1075, C1091:G1091, J1091:N1091")


    If CheckBoxSpecies1 = True Then
    For i = 1 To 12
    If WS1.OLEObjects("CheckBox" & i).Object _
    .Value = False Then _
    Rng1.Offset(i - 1 + IIf(i > 10, 12, 0)).Value = "N/A"
    Next
    End If
    ...
    If CheckBoxSpecies29 = True Then
    For i = 1 To 12
    If WS1.OLEObjects("CheckBox" & i).Object _
    .Value = False Then _
    Rng29.Offset(i - 1 + IIf(i > 10, 12, 0)).Value = "N/A"
    Next
    End If

    Thanks again!

    Cheers

Posting Permissions

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