複数の IF ステートメントを使用してコードを高速化する方法

debugcn 投稿 Dev

ココベリー2526

私は一日中このコードに取り組み、ついにすべてが完全に機能するようになりました。唯一の問題は、コードの実行がかなり遅いことです。数千行のワークブックで使用されることを考慮して、それを変更したいと思います。私は vba に非常に慣れていないため、間違っているか、ショートカットのように見えるものがここにある可能性があります。速度を上げる方法をいくつか追加したと思いますが、他に何かできるかどうかはわかりませんでした。

 Sub Degree_Workboook_Names_major1()

 Application.ScreenUpdating = False
 Application.DisplayStatusBar = False
 Application.EnableEvents = False

'Inserts a new column after column H named department names
 range("I1").EntireColumn.Insert
 range("I1").Value = "DeptName"


 Dim abbrRange As range              'range to hold the columns with the department names
 Set abbrRange = range("H:H")

'Writes the department name in the the department name column next to the associated abbr

 For Each cell In abbrRange


            If cell.Value = "ACC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Accounting"
            End If

            If cell.Value = "ACS" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Adolescent, Career and Special Education"
            End If

            If cell.Value = "AES" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Animal and Equine Science"
            End If

            If cell.Value = "AGR" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Agricultural Science"
            End If

            If cell.Value = "AHS" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Applied Health Sciences"
            End If

            If cell.Value = "AHT" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Veterinary Technology and Pre-Veterinary Medicine"
            End If

            If cell.Value = "Art" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Art and Design"
            End If

            If cell.Value = "BIO" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Biology"
            End If

            If cell.Value = "BPA" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Management, Marketing and Business Administration"
            End If

            If cell.Value = "CCD" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Center for Communication Disorders"
            End If

            If cell.Value = "CEAO" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Bachelor of Integrated Studies Program"
            End If

            If cell.Value = "CHE" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Chemistry"
            End If

            If cell.Value = "CLH" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Community Leadership and Human Services"
            End If

            If cell.Value = "COM" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Organizational Communication"
            End If

            If cell.Value = "CSC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Computer Science and Information Systems"
            End If

            If cell.Value = "ECO" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Economics and Finance"
            End If

            If cell.Value = "ELE" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Early Childhood and Elementary Education"
            End If

            If cell.Value = "ENPH" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of English and Philosophy"
            End If

            If cell.Value = "ELSC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Educational Studies, Leadership and Counseling"
            End If

            If cell.Value = "GSC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Geosciences"
            End If

            If cell.Value = "HFA" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Liberal Arts"
            End If

            If cell.Value = "HIS" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of History"
            End If

            If cell.Value = "INDC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Institute of Engineering"
            End If

            If cell.Value = "IOE" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Institute of Engineering"
            End If

            If cell.Value = "JMC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Journalism and Mass Communications"
            End If

            If cell.Value = "MAT" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Mathematics and Statistics"
            End If

            If cell.Value = "MLA" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Modern Languages"
            End If

            If cell.Value = "MMB" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Management, Marketing and Business Administration"
            End If

            If cell.Value = "MSP" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Military Science Program"
            End If

            If cell.Value = "MUS" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Music"
            End If

            If cell.Value = "NUR" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Nursing"
            End If

            If cell.Value = "OSH" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Occupational Safety and Health"
            End If

            If cell.Value = "POL" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Political Science and Sociology"
            End If

            If cell.Value = "PSY" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Psychology"
            End If

            If cell.Value = "THR" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Theatre"
            End If

 Next



 'Inserts a new column after column H named department names
range("M1").EntireColumn.Insert
range("M1").Value = "DeptName"


'Dim abbrRange As range              'range to hold the columns with the   dpeartment names
Set abbrRange = range("L:L")

'Writes the department name in the the department name column next to the associated abbr

For Each cell In abbrRange


            If cell.Value = "ACC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Accounting"
            End If

            If cell.Value = "ACS" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Adolescent, Career and Special Education"
            End If

            If cell.Value = "AES" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Animal and Equine Science"
            End If

            If cell.Value = "AGR" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Agricultural Science"
            End If

            If cell.Value = "AHS" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Applied Health Sciences"
            End If

            If cell.Value = "AHT" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Veterinary Technology and Pre-Veterinary Medicine"
            End If

            If cell.Value = "Art" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Art and Design"
            End If

            If cell.Value = "BIO" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Biology"
            End If

            If cell.Value = "BPA" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Management, Marketing and Business Administration"
            End If

            If cell.Value = "CCD" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Center for Communication Disorders"
            End If

            If cell.Value = "CEAO" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Bachelor of Integrated Studies Program"
            End If

            If cell.Value = "CHE" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Chemistry"
            End If

            If cell.Value = "CLH" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Community Leadership and Human Services"
            End If

            If cell.Value = "COM" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Organizational Communication"
            End If

            If cell.Value = "CSC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Computer Science and Information Systems"
            End If

            If cell.Value = "ECO" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Economics and Finance"
            End If

            If cell.Value = "ELE" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Early Childhood and Elementary Education"
            End If

            If cell.Value = "ENPH" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of English and Philosophy"
            End If

            If cell.Value = "ELSC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Educational Studies, Leadership and Counseling"
            End If

            If cell.Value = "GSC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Geosciences"
            End If

            If cell.Value = "HFA" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Liberal Arts"
            End If

            If cell.Value = "HIS" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of History"
            End If

            If cell.Value = "INDC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Institute of Engineering"
            End If

            If cell.Value = "IOE" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Institute of Engineering"
            End If

            If cell.Value = "JMC" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Journalism and Mass Communications"
            End If

            If cell.Value = "MAT" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Mathematics and Statistics"
            End If

            If cell.Value = "MLA" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Modern Languages"
            End If

            If cell.Value = "MMB" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Management, Marketing and Business Administration"
            End If

            If cell.Value = "MSP" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Military Science Program"
            End If

            If cell.Value = "MUS" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Music"
            End If

            If cell.Value = "NUR" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Nursing"
            End If

            If cell.Value = "OSH" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Occupational Safety and Health"
            End If

            If cell.Value = "POL" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Political Science and Sociology"
            End If

            If cell.Value = "PSY" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Psychology"
            End If

            If cell.Value = "THR" Then
            cell.Activate
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = "Department of Theatre"
            End If

Next



range("I:I").HorizontalAlignment = xlLeft
range("M:M").HorizontalAlignment = xlLeft

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True


End Sub

Subodh Tiwari sktneer

このシナリオでは、辞書が適切なオプションですが、配列を好みます。サンプルコードは次のようになります....

Sub Degree_Workbook_Names_major1()
Dim abbrRange As Range
Dim Abbr, Dept()
Dim lr As Long, i As Long

With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

lr = Cells(Rows.Count, "H").End(xlUp).Row
Columns("I").Insert
Range("I1").Value = "DeptName"

Set abbrRange = Range("H2:H" & lr)
Abbr = abbrRange.Value
ReDim Dept(1 To lr)

For i = 1 To UBound(Abbr, 1)
    Select Case UCase(Abbr(i, 1))
        Case "ACC"
            Dept(i) = "Department of Accounting"
        Case "ACS"
            Dept(i) = "Department of Adolescent, Career and Special Education"
        Case "AES"
            Dept(i) = "Department of Animal and Equine Science"
        Case "AGR"
            Dept(i) = "Department of Department of Agricultural Science"
        Case "AHS"
            Dept(i) = "Department of Applied Health Science"
        Case "AHT"
            Dept(i) = "Department of Veterinary Technology and Pre-Veterinary Medicine"
        Case "ART"
            Dept(i) = "Department of Art and Design"
        Case "BIO"
            Dept(i) = "Department of Biology"
        'similarly add rest of the Abbreviations with Case statement and set the array Dept as shown above
    End Select
Next i
Range("I2").Resize(UBound(Dept)).Value = Application.Transpose(Dept)

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

End Sub

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集2021-07-30

コメントを追加

サインイン

分類Dev

Related 関連記事

記事

複数の IF ステートメントを使用してコードを高速化する方法

複数の IF ステートメントを使用してコードを高速化する方法

R：複数のifステートメントを高速化します

式を使用したIfThenステートメントのVBAコードの高速化

Juliaで複数のブロードキャストを高速化する方法

Numba を使用して次のコードを高速化する

Http.Clientを使用してPythonで複数のシーケンシャルhttpリクエストを高速化する方法

libclangを使用してC ++コードの解析を高速化する方法は？

1対多のアソシエーションのHibernateDMLステートメントを高速化する方法

このコードを高速化する方法。ストリーミングオーディオAndroid

JOINの2つのORステートメントを使用して、データ品質を維持し、クエリを高速化します

マングースの一括ドキュメント更新を高速化する方法

2つのネストされたforループと複数のifステートメントを高速化します

複数のネストされたforループを使用してPython2プログラムを高速化する方法

iOS / MacOSのメタルコードを高速化する方法

Pythonの高速化 'for'ネストされた 'if'ステートメントを使用したループ

テスト用のmysqldockerコンテナを高速化する方法は？

ラムダを使用して複数のifステートメントを簡略化する方法

ラムダを使用して複数のifステートメントを簡略化する方法

ラムダを使用して複数のifステートメントを簡略化する方法

ラムダを使用して複数のifステートメントを簡略化する方法

ブロードキャストを使用してこのコードを高速化するにはどうすればよいですか？

複数のCREATEステートメントを実行する高速な方法

複数のスレッドを使用して計算を高速化する方法は？

このMySQLステートメントを高速化するインデックス

コードを高速化するためのオブジェクトのインスタンス化

複数のifステートメントを使用して関数の複雑さを軽減する方法

LabelEncoderを高速化してカテゴリ変数を整数に再コーディングする方法

1つのホットエンコーダコードを高速化する方法

Phrets（Bright MLS）を使用してMLSからこのRetsフィードのインポートを高速化する方法

MySQLデータベースからの複数の結合を高速化するためのヒント