文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >"Procedure too Large“错误

问"Procedure too Large“错误
EN

Stack Overflow用户
提问于 2015-07-31 03:03:48
回答 1查看 66关注 0票数 1
代码语言：javascript
复制
Sub Create_Mail_From_List_Exams()

Dim OutApp  As Object
Dim OutMail As Object
Dim cell As Range
Dim bodymessage  As String
Dim bodymessage1 As String
Dim bodymessage2 As String
Dim bodymessage3 As String
Dim bodymessage4  As String
Dim bodymessage5 As String
Dim Bodymessage6 As String
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
Dim i As Integer
Dim j As Integer
For i = 3 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    bodymessage = ""
    bodymessage1 = ""
    bodymessage2 = ""
    bodymessage3 = ""
    bodymessage4 = ""
    bodymessage5 = ""
    ex6 = ""
    ex7 = ""
    ex8 = ""

    fr1 = ""
    fr2 = ""
    fr3 = ""
    fr4 = ""
    fr5 = ""
    fr6 = ""
    fr7 = ""
    fr8 = ""
    fr9 = ""

  'ActiveSheet.Cells(1, 12) = ActiveSheet.Cells(1, 12) & "(" & cell.Row & "," & cell.Column & "), "

    If Sheets("Exams-email results").Cells(i, 3).Text Like "?*@?*.?*" And _
    LCase(Cells(i, "M").Value) = "dnm" Then

    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail

    .To = ActiveSheet.Cells(i, 3).Text



    .Subject = Sheets("Exams-email results").Range("T2") & " / " & Sheets("Exams-email results").Range("T5")


    '& "Groupe  " & ActiveSheet.Cells(i, 10).Text & "  / Niveau " & ActiveSheet.Cells(i, 11).Text'

    'A'

    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "Educ" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B7").Text
    End If


    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "A1" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B26").Text

    End If

      If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "A2" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B27").Text

    End If

           If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "A3" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B28").Text

    End If
        If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "A4" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B29").Text

    End If
    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "A5" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B30").Text

    End If

         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "A6" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B31").Text

    End If

         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "A7" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B32").Text

    End If

         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "A8" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B33").Text

    End If

         'k'

         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "K1" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B20").Text

    End If

         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "K2" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B21").Text

    End If
            If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D2").Text = "K3" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B22").Text

    End If


            If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "K4" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B23").Text

    End If


            If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "K5" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B24").Text

    End If



           'PS'




     If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "PS1" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B35").Text
    End If




     If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "PS2" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B36").Text
    End If



         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "PS3" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B37").Text
    End If

         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "PS4" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B38").Text
    End If

         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "PS5" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B39").Text
    End If

         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "PS6" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B40").Text
    End If

         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "PS7" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B41").Text
    End If

         If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("D3").Text = "PS8" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B42").Text
    End If





    'EXAM2'



        If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "Educ" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B7").Text
    End If


        If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "A1" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B26").Text
    End If


        If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "A2" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B27").Text
    End If


        If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "A3" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B28").Text
    End If


        If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "A4" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B29").Text
    End If


        If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "A5" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B30").Text
    End If


        If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "A6" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B31").Text
    End If
        If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "A7" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B32").Text
    End If
     If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "A8" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B33").Text
    End If






    'k'


        If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "K1" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B20").Text
    End If





     If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "K2" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B21").Text
    End If



    If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "K3" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B22").Text
    End If


    If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "K4" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B23").Text
    End If

    If LCase(Cells(i, "E").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "K5" Then

        bodymessage1 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B24").Text
    End If

    'ps1'


    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS1" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B35").Text
    End If

    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS2" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B36").Text
    End If

    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS3" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B37").Text
    End If

    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS4" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B38").Text
    End If

    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS5" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B39").Text
    End If

    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS6" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B40").Text
    End If

    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS7" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B41").Text
    End If

    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS8" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B42").Text
    End If





    'Exam3'



        If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F3").Text = "Educ" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B7").Text
    End If


        If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F3").Text = "A1" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B26").Text
    End If


        If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F2").Text = "A2" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B27").Text
    End If


        If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F2").Text = "A3" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B28").Text
    End If


        If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F2").Text = "A4" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B29").Text
    End If


        If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F2").Text = "A5" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B30").Text
    End If


        If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F2").Text = "A6" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B31").Text
    End If

        If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F2").Text = "A7" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B32").Text
    End If

     If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F2").Text = "A8" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B33").Text
    End If





    'K'


        If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F3").Text = "K1" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B20").Text
    End If

    If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F3").Text = "K2" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B21").Text
    End If
    If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F3").Text = "K3" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B22").Text
    End If

    If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F3").Text = "K4" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B23").Text
    End If

    If LCase(Cells(i, "F").Text) = "dnm" And Sheets("Exams-email results").Range("F3").Text = "K5" Then

        bodymessage2 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B24").Text
    End If

    'PS'


    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS1" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B35").Text
    End If
    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS2" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B36").Text
    End If
    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS3" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B37").Text
    End If
    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS4" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B38").Text
    End If
    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS5" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B39").Text
    End If
    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS6" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B40").Text
    End If
    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS7" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B41").Text
    End If

    If LCase(Cells(i, "D").Text) = "dnm" And Sheets("Exams-email results").Range("E3").Text = "PS8" Then

        bodymessage = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B42").Text
    End If




    'EXam'



        If LCase(Cells(i, "G").Text) = "dnm" And Sheets("Exams-email results").Range("G3").Text = "Educ" Then

        bodymessage3 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B7").Text
    End If


        If LCase(Cells(i, "G").Text) = "dnm" And Sheets("Exams-email results").Range("G3").Text = "A1" Then

        bodymessage3 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B26").Text
    End If


        If LCase(Cells(i, "G").Text) = "dnm" And Sheets("Exams-email results").Range("G3").Text = "A2" Then

        bodymessage3 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B27").Text
    End If


        If LCase(Cells(i, "G").Text) = "dnm" And Sheets("Exams-email results").Range("G3").Text = "A3" Then

        bodymessage3 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B28").Text
    End If


        If LCase(Cells(i, "G").Text) = "dnm" And Sheets("Exams-email results").Range("G3").Text = "A4" Then

        bodymessage3 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B29").Text
    End If


        If LCase(Cells(i, "G").Text) = "dnm" And Sheets("Exams-email results").Range("G3").Text = "A5" Then

        bodymessage3 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B30").Text
    End If


        If LCase(Cells(i, "G").Text) = "dnm" And Sheets("Exams-email results").Range("G3").Text = "A6" Then

        bodymessage3 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B31").Text
    End If
        If LCase(Cells(i, "G").Text) = "dnm" And Sheets("Exams-email results").Range("G3").Text = "A7" Then

        bodymessage3 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B32").Text
    End If

     If LCase(Cells(i, "G").Text) = "dnm" And Sheets("Exams-email results").Range("G3").Text = "A8" Then

        bodymessage3 = vbNewLine & "    **    " & Sheets("SOMC-Legend").Range("B33").Text
End If
excel
vba
EN

回答 1

Stack Overflow用户

发布于 2015-07-31 03:22:38

这样做，一切都会好起来的。

  1. ，而不是有这么多变量，如下所示

Dim bodymessage As String Dim bodymessage1 As String Dim bodymessage2 As String Dim bodymessage3 As String Dim bodymessage4 As String Dim bodymessage5 As String Dim Bodymessage6 As String

使用数组。例如

Dim comments.

  • These

(1 to 7) as String

  • 删除不必要的空行和删除不必要的bodymessage例如6行

fr1 = "“fr2 = "”fr3 = "“fr4 = "”fr5 = "“fr6 = "”fr7 = "“fr8 = "”fr9 = "“

可以写成2行

fr1 = ""：fr2 = ""：fr3 = ""：fr4 = ""：fr5 = "“fr6 = ""：fr7 = ""：fr8 = ""：fr9 = "”

这只是一个例子。在上面的例子中，我将完全按照我在第一点中提到的那样做。

还有一点。不需要逐个清除数组的每个元素。您可以使用Erase MyAr。下面是一个例子

子采样() Dim MyAr(1 To 5) For i=1 To 5 MyAr(i) =1 Next i For i=1 To 5 Debug.Print MyAr(i) Next I Erase MyAr For i=1 To 5 Debug.Print MyAr(i) '<~~ Nothing there Next I Debug.Print UBound(MyAr) End Sub

  • 您正在重复If LCase(Cells(i, "D").Text) = "dnm"。只使用它一次，将其余的if语句放在其中，并将它们转换为Select Case。例如

如果LCase(单元格(i，"D").Text) = "dnm“然后选择案例页(”检查-电子邮件结果“).Range(”D3“).Text案例"Educ"：bodymessage = vbNewLine &”**“&Sheets(”SOMC-图例“).Range(”B7“).Text案例"A1"：bodymessage = vbNewLine &”**“&Sheets(”SOMC-图例“).Range(”B26“).Text案例"A2"：bodymessage = vbNewLine &“**”&Sheets(“SOMC-图例”).Range(“B27”).Text‘'~~>等’End选择End If

如果你应用了我上面提到的所有内容，那么你的错误就会消失:)总是试着写出清晰而精确的代码:)

票数 6
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/31732031

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档