重複を削除

[重複を削除]コマンドをVBAで実行して
重複するデータを削除する方法をみていきます。

Excelのコマンド[重複を削除]で重複を削除することができます。
手動で実行するには次のようにします。

範囲を選択した状態で
[データ]-[重複の削除]をクリックすると
次のウィンドウが表示されます。

重複しているかどうかを判定する列にチェックをいれます。
(重複の判定に使わない列はチェックをはずす。）
選択範囲の先頭行をデータとして扱わない場合は
[先頭行をデータの見出しとして使用する]にチェックをいれます。
[OK]を押すと重複が削除できます。

VBAでこのコマンドを実行するには
RemoveDuplicatesメソッドを使います。

コード例：

Range("A1:C10").RemoveDuplicates _
    Columns:=Array(1,2,3), _
    Header:=xlNo

上記コードは
Range("A1:C10")で"A1:C10"の範囲を指定します。

Columns引数には
どの列を重複の判定に使うかを指定します。

"A1:C10"の範囲には
A,B,C列の3列あります。
全ての列を重複の判定に使いたい場合は
Columns:=Array(1,2,3)と指定します。

1列目(A列)のみの場合，Columns:=Array(1)
1,2列目(A列・B列)のみの場合，Columns:=Array(1,2)とします。

Header引数には
指定範囲の先頭行を見出しとして扱うかを指定します。
見出しとして扱う場合，Header:=xlYes
見出しとして扱わない場合，Header:=xlNoにします。

RemoveDuplicatesメソッドを実行すると
重複があれば
選択範囲内の行が削除されて上方に詰められます。
このとき選択範囲外のデータに影響はありません。
行ごとでデータを扱いたい場合は，
重複の削除を実行するときの範囲を行全体にします。

どの列を重複の判定に使うかで
重複の削除後のデータが異なるので
次の画像でいくつか例を見てみます。

使用Ver：Win10, Excel For Office365

形式を選択して貼り付け

形式を選択して貼り付けについて
VBAで実行する方法を見ていきます。

ショートカットキーCtrl+C, Ctrl+Vでのコピぺは
数式，書式，フォーマットなどすべてがコピペされます。

値のみコピーしたい場合，
書式のみコピーしたい場合もあります。
そのような場合，
[形式を選択して貼り付け]をします。

[形式を選択して貼り付け]をするには
範囲を選択して右クリック-[形式を選択して貼り付け]をクリックします。
次のウィンドウが表示されます。

目的の形式を選択して[OK]をクリックします。

VBAで[形式を選択して貼り付け]をする方法を見ていきます。

貼り付けの前にコピーをします。
Copyメソッドで範囲をコピーしたり，手動でコピーをします。
コピーしたデータはクリップボードに貼り付けられます。

形式を選択して貼り付けは
PasteSpecialメソッドを使用します。
Paste引数に貼り付ける形式を指定します。
次の表に
Paste引数に指定する定数と貼り付け形式を表示します。

定数	貼り付け形式
xlPasteAll	すべて
xlPasteFormulas	数式
xlPasteValues	値
xlPasteFormats	書式
xlPasteComments	コメント
xlPasteValidation	入力規則
xlPasteAllUsingSourceTheme	コピー元のテーマを使用してすべて貼り付け
xlPasteAllExceptBorders	罫線を除くすべて
xlPasteColumnWidths	列幅
xlPasteFormulasAndNumberFormats	数式と数値の書式
xlPasteValuesAndNumberFormats	値と数値の書式
xlPasteAllMergingConditionalFormats	すべての結合されている条件付き書式

次のコードは
VBAでコピーして，値のみペーストします。

Sub macro20200616a()
'形式を選択して貼り付け
'貼り付け：値
   
    'コピー
    Range("A1").Copy
   
    '形式を選択して貼り付け
    Range("B1").PasteSpecial _
        Paste:=xlPasteValues
       
    'コピーモード解除
    Application.CutCopyMode = False
   
End Sub

次のコードは
ショートカットキーCtrl+Cでクリップボードに貼り付けたデータを
現在選択しているセルに
書式のみペーストします。

Sub macro20200616b()
'形式を選択して貼り付け
'貼り付け：書式

    '形式を選択して貼り付け
    Selection.PasteSpecial _
        Paste:=xlPasteFormats
   
    'コピーモード解除
    Application.CutCopyMode = False
   
End Sub

使用Ver：Win10, Excel For Office365

続きを読む "形式を選択して貼り付け"

セルの罫線の設定

セルの罫線の設定について
設定できる罫線の位置，線種，太さ，色の指定方法と
VBAで設定する場合の実例を見ていきます。

設定できる罫線の位置は
セルの上下左右+右下がりの斜め線・右上がりの斜め線です。

VBAで設定するには
Borderオブジェクトの引数に罫線の位置を指定します。
設定できる罫線の位置は下記です。

線種は線なしを含めて８種類です。
それぞれの罫線に
LineStyleプロパティで線種を指定できます。
設定できる線種は下記になります。

線の太さは4種類です。
それぞれの罫線にWeightプロパティで指定します。

色はColorIndexプロパティか
Colorプロパティで指定します。
特に指定しない場合はなしでOKです。
ColorIndexプロパティは1～56までの数値を指定，
ColorプロパティはRGB関数を使用して色を指定します。

TintAndShadeプロパティで
設定した色を明るく、または暗くすることができます。
-1から1までの範囲で小数の値を指定します。

VBAによる罫線の設定の実例として
下図のような罫線を設定します。

コードはこちら

Sub macro20200615a()
'罫線の設定
   
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
   
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
        .Color = RGB(255, 0, 0)
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
        .Color = RGB(255, 0, 0)
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
        .Color = RGB(255, 0, 0)
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
        .Color = RGB(255, 0, 0)
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
   
    '一行目のセルの下線を2重線にする
    With Selection.Rows(1).Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    '一列目のセルの右線を2重線にする
    With Selection.Columns(1).Borders(xlEdgeRight)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
   
    '左上のセルに右下がりの斜線を設定
    With Selection.Cells(1, 1).Borders(xlDiagonalDown)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

End Sub

使用Ver：Win10, Excel For Office365

続きを読む "セルの罫線の設定"

セルの塗りつぶしの設定

セルの塗りつぶしの設定について
どのような設定項目があるか，
VBAで設定する方法について見ていきます。

セルの塗りつぶしとは
セルの背景色や背景のパターンのことです。

手動で設定するには
右クリックメニュー[セルの書式設定]をクリックすると
表示されるウィンドウの
[塗りつぶし]タブでできます。

設定は大まかに
「背景色」と「パターン」に分かれています。
背景色は色，塗りつぶし効果の設定ができます。
パターンは色と種類を設定できます。

VBAでセルの塗りつぶしの設定をする方法を見ていきます。

セルの塗りつぶしは
Interiorオブジェクトが持つプロパティで設定します。

背景色は
ColorIndexプロパティかColorプロパティで指定します。
ColorIndexプロパティは1～56までの整数を指定，
ColorプロパティはRGB関数を使用して色を指定します。
TintAndShadeプロパティで
設定した色を明るく、または暗くすることができます。
-1から1の範囲で小数の値を指定します。

塗りつぶし効果はグラデーションの設定で
使用頻度は高くないので省略します。

パターンは
Patternプロパティで設定します。
設定できる種類は下記を参照ください。
記事「パターン一覧」

パターンの色は
PatternColorIndexやPatternColorで指定します。
PatternColorIndexプロパティは1～56までの整数を指定，
PatternColorプロパティはRGB関数を使用して色を指定します。
PatternTintAndShadeプロパティで
設定した色を明るく、または暗くすることができます。
-1から1の範囲で小数の値を指定します。

セルの塗りつぶしを設定するのは
主に表を見やすくする場合です。

一例として
表のタイトル行の区別，奇数行と偶数行の区別を付けるために
セルの塗りつぶしを設定します。

次のコードは選択範囲に対して
一行目に濃い水色，奇数行に薄い水色の背景色を設定します。

Sub macro20200614a()
'セルの背景色の設定

    Dim i As Integer
   
    '罫線の設定-------------------------------
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
   
    '左上のセルに右下がりの斜線を設定
    With Selection.Cells(1, 1).Borders(xlDiagonalDown)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
   
    '背景色の設定-------------------------------
    '1行目の設定
    With Selection.Rows(1).Interior
        '塗りつぶしの色
        .Color = RGB(100, 150, 220)
        .TintAndShade = 0
    End With
   
    '奇数行の設定
    For i = 3 To Selection.Rows.count Step 2
        With Selection.Rows(i).Interior
            '塗りつぶしの色
            .Color = RGB(100, 150, 220)
            .TintAndShade = 0.7
        End With
    Next i

End Sub

実行結果：

次はコードは選択範囲に対して
一行目と奇数行に異なるパターンを設定します。

Sub macro20200614b()
'セルのパターンの設定

    Dim i As Integer
   
    '罫線の設定-------------------------------
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
   
    '左上のセルに右下がりの斜線を設定
    With Selection.Cells(1, 1).Borders(xlDiagonalDown)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
   
    'パターンの設定-------------------------------
    '1行目
    With Selection.Rows(1).Interior
        '塗りつぶしの色
        .Pattern = xlPatternGray25
    End With
   
    '奇数行
    For i = 3 To Selection.Rows.count Step 2
        With Selection.Rows(i).Interior
            '塗りつぶしの色
            .Pattern = xlPatternGray8
        End With
    Next i

End Sub

実行結果：

使用Ver：Win10, Excel For Office365

続きを読む "セルの塗りつぶしの設定"

パターン一覧

セルの背景やグラフの背景に設定できるパターンの一覧を
VBAで作成します。
パターンの見本として使えます。

パターンに設定できる値は
次のMicrosoft内ページを参照してください。
「xlpattern 列挙 (Excel)」

文字の説明ではわかり難いので
実際にセルにパターンを設定してみます。

次のコードはパターン一覧を作成します。

Sub macro20200613a()
'パターン一覧を作成する

    Dim ptn()
    Dim ptn_str()
    Dim p
    Dim i
    i = 1
   
    Sheets.Add.Name = "パターン一覧"
   
    'パターンに設定する定数を格納する配列
    ptn() = Array(xlPatternAutomatic, xlPatternChecker, xlPatternCrissCross, _
        xlPatternDown, xlPatternGray16, xlPatternGray25, _
        xlPatternGray50, xlPatternGray75, xlPatternGray8, _
        xlPatternGrid, xlPatternHorizontal, xlPatternLightDown, _
        xlPatternLightHorizontal, xlPatternLightUp, _
        xlLightVertical, xlPatternNone, xlPatternSemiGray75, _
        xlPatternSolid, xlPatternUp, xlPatternVertical)
   
    'セルに表示するパターンの定数を表す文字列を
    '格納する配列
    ptn_str() = Array("xlPatternAutomatic", "xlPatternChecker", "xlPatternCrissCross", _
        "xlPatternDown", "xlPatternGray16", "xlPatternGray25", _
        "xlPatternGray50", "xlPatternGray75", "xlPatternGray8", _
        "xlPatternGrid", "xlPatternHorizontal", "xlPatternLightDown", _
        "xlPatternLightHorizontal", "xlPatternLightUp", _
        "xlLightVertical", "xlPatternNone", "xlPatternSemiGray75", _
        "xlPatternSolid", "xlPatternUp", "xlPatternVertical")
   
    'セルにパターンを設定
    For i = 0 To UBound(ptn())
        Cells(i + 1, 1).Interior.Pattern = ptn(i)
        Cells(i + 1, 2) = ptn_str(i)
    Next i
    Cells.EntireColumn.AutoFit
   
End Sub

実行結果：

使用Ver：Win10, Excel For Office365

続きを読む "パターン一覧"

シートをコピーする

VBAでシートをコピーする方法について以下の項目を見ていきます。
1. 元シートの前または後にシートをコピーする
2. 全シートの左端または右端にシートをコピーする
3. シートをコピーした後に名前を変更する
4. 新しいブックにシートをコピー
5.シートを既存ブックにコピーする
6. 複数のシートをコピーする

1. 元シートの前または後にシートをコピーする

   シートのコピーはCopyメソッドを使います。
   Copyメソッドの引数Beforeに既存シートを指定すると
   指定したシートの前にコピーされます。
   Copyメソッドの引数Afterに既存シートを指定すると
   指定したシートの後ろにコピーされます。
   引数Before，Afterはどちらか一方のみ指定します。

   次のコードはコピー元シートの前，後ろにシートをコピーします。

   Sub macro20200609a()
   'コピー元シートの前にシートをコピーする

       Sheets("Sheet1").Copy Before:=Sheets("Sheet1")

   End Sub
   Sub macro20200609b()
   'コピー元シートの後ろにシートをコピーする

       Sheets("Sheet1").Copy After:=Sheets("Sheet1")

   End Sub

2. 全シートの左端または右端にシートをコピーする

   ブックに含まれるすべてのシートは
   Sheetsオブジェクトに格納されています。
   シートは左端からSheet(1),Sheet(2),…で参照できます。
   すべてのシートの数はSheets.countで取得できます。

   左端のシートはSheet(1)，
   右端のシートはSheets(Sheets.count)で参照できます。

   次のコードは左端または右端にシートをコピーします。

   Sub macro20200609c()
   'シートを左端にコピーする

       Sheets("Sheet1").Copy Before:=Sheets(1)

   End Sub
   Sub macro20200609d()
   'シートを右端にコピーする

       Sheets("Sheet1").Copy After:=Sheets(Sheets.count)

   End Sub

3. シートをコピーした後に名前を変更する

   シートをコピーすると通常
   コピー元のシート名に(1),(2),…などと数字が付加されます。

   コピーしたシートの名前を付けたい場合は
   Nameプロパティで設定します。

   次のコードはシートをコピー後
   コピーしたシートの名前を変更します。

   Sub macro20200609e()
   'シートをコピーする

       Dim sh As Worksheet
      
       Sheets("Sheet1").Copy Before:=Sheets("Sheet1")
       Set sh = ActiveSheet
       sh.Name = "新しいシート名"

   End Sub

4. 新しいブックにシートをコピー

   CopyメソッドのBefore，After引数を指定しないと
   新しいブックが作成されてシートがコピーされます。

   次のコードは
   新しいブック(NewBook1.xlsx)にシートをコピーして
   ブックに名前を付けて閉じます。

   Sub macro20200609f()
   'シートを新しいブックにコピーする

       Dim wb As Workbook
      
       Sheets("Sheet1").Copy
       Set wb = ActiveWorkbook
       wb.SaveAs Filename:="C:\Users\username\Documents\NewBook1.xlsx"
       wb.Close

   End Sub

5. シートを既存ブックにコピーする

   次のコードは
   既存ブック(Book1.xlsx)を開いてシートをコピーして，
   コピー後にコピー先ブックを保存して閉じます。

   Sub macro20200609g()
   'シートを既存ブックにコピーする

       Dim wb1 As Workbook 'コピー元
       Dim wb2 As Workbook 'コピー先
      
       Set wb1 = ActiveWorkbook
      
       Workbooks.Open Filename:="C:\Users\username\Documents\Book1.xlsx"
       Set wb2 = ActiveWorkbook
      
       wb1.Sheets("Sheet1").Copy Before:=wb2.Sheets(1)
       wb2.Close SaveChanges:=True

   End Sub

6. 複数のシートをコピーする

   複数のシートを指定する場合は
   Array関数を使用します。

   シート名"Sheet5"を指定する場合に
   Sheets("Sheet5")としました。
   例えば，
   シート名が"Sheet1", "Sheet2", "Sheet4"の
   3枚のシートを指定したい場合
   Sheets(Array("Sheet1", "Sheet2", "Sheet4"))のようにします。

   Sub macro20200609h()
   '複数のシートをコピーする

       Dim wb1 As Workbook 'コピー元
       Dim wb2 As Workbook 'コピー先
       Dim sh_copy As Sheets
      
       Set wb1 = ActiveWorkbook
      
       Workbooks.Open Filename:="C:\Users\username\Documents\Book1.xlsx"
       Set wb2 = ActiveWorkbook
      
       Set sh_copy = wb1.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
       sh_copy.Copy Before:=wb2.Sheets(1)
       wb2.Close SaveChanges:=True

   End Sub

使用Ver：Win10, Excel For Office365

続きを読む "シートをコピーする"

VBAで名前の定義を扱う

VBAで名前の定義を扱う方法に関して以下の項目を見ていきます。
1. 名前の定義とは？
2. 名前の定義を追加する
3. 名前の定義を削除する
4. 名前の定義の一覧を作成する
5. 名前の定義を変更する
6. 名前の定義の存在チェック
7. 名前の定義を使って範囲を指定する

1. 名前の定義とは？

名前とは
セル範囲に付けられている名前のことです。

手動で名前を付けるには
数式バーの左端にある [名前] ボックスをクリックして
名前を直接入力します。

デフォルトの設定では作成した名前は
ブック内であればどのシートからでも参照できます。

数式などで値を参照する際に
セルのアドレスで指定できますが，
名前を付ければ
どのシートのどの範囲という情報は不要になり
名前で範囲を参照することができます。

2. 名前の定義を追加する

名前を追加するにはAddメソッドを使用します。
次のコードはVBAで名前を追加して，
名前を付けた範囲に移動します。

Sub macro20200607a()
'名前の定義を追加する

    Dim sh_name As String
    Dim rng As String
   
    sh_name = ActiveSheet.Name
    rng = ActiveSheet.Range("A1:C10").Address
   
    '名前の定義を追加
    ActiveWorkbook.Names.Add _
        Name:="name1", _
        RefersTo:="=" & sh_name & "!" & rng
   
    '名前の範囲に移動
    Application.Goto Reference:="name1"

End Sub

3. 名前の定義を削除する

名前の定義を削除するにはDeleteメソッドを使います。
次のコードは名前"name1"を削除します。

Sub macro20200607b()

    '名前の定義を削除
    ActiveWorkbook.Names("name1").Delete

End Sub

名前を定義したシートを削除すると
シートは削除されても名前は削除されずに残っています。
そのような名前は参照元がなくなった部分はREFになります。

次のコードは
参照元がなくなりエラーが起こっている名前を
REFが含まれているかで判定して削除します。

Sub macro20200607c()
'参照元がなくなった名前を削除する

    Dim n
   
    For Each n In ActiveWorkbook.Names
        If InStr(n.RefersTo, "REF") <> 0 Then
            n.Delete
        End If
    Next n

End Sub

4. 名前の定義の一覧を作成する

定義された名前はNamesオブジェクトに格納されています。
名前の定義のそれぞれにアクセスするには
Names(1)，Names(2),…のようにします。

次のコードは
Namesオブジェクトを変数nmsに格納してから個々の名前を参照して
名前の定義の一覧を作成します。

Sub macro20200607d()
'名前の定義一覧を作成する

    Dim i As Integer
    Dim nms As Names
   
    Sheets.Add
    Cells(1, 1) = "名前の定義一覧"
    Cells(4, 1) = "名前"
    Cells(4, 2) = "範囲"
   
    Set nms = ActiveWorkbook.Names
    For i = 1 To nms.count
        Cells(i + 4, 1) = nms(i).Name
        Cells(i + 4, 2) = Replace(nms(i).RefersTo, "=", "")
    Next
   
    ActiveSheet.Cells.EntireColumn.AutoFit
   
End Sub

5. 名前の定義を変更する

名前の名前を変更するにはNameプロパティ，
名前の参照を変更するにはRefersToプロパティに
変更したい値を指定します。

次のコードは"name1"という名前の定義を変更します。

Sub macro20200607e()
'名前の定義を変更する
    Dim n As Name
   
    Set n = ActiveWorkbook.Names("name1")
    n.Name = "変更後name1"
    n.RefersTo = "=Sheet5!A3"

End Sub

6. 名前の定義の存在チェック

次のコードは
ブックのすべての名前が格納されているNamesオブジェクトを参照して
定義されている名前と
存在を確認したい名前を比較して
同じであれば名前は存在すると判定します。

Sub macro20200607f()
'名前の定義の存在チェックする

    Dim n_str As String
    Dim n As Name
    Dim nms As Names
   
    n_str = "name4"
   
    Set nms = ActiveWorkbook.Names
    For Each n In nms
        If n.Name = n_str Then
            MsgBox n_str & "は存在します。"
            Exit Sub
        End If
    Next n
   
    MsgBox n_str & "は存在しません。"
   
End Sub

7. 名前の定義を使って範囲を指定する

RefersToRangeプロパティを使うと
名前の定義がされている範囲をRangeオブジェクトとして扱えます。

次のコードは"name1"で定義された範囲に1を入力します。

Sub macro20200607g()
'名前の定義を使って範囲を指定する

    Dim rng As Range
   
    Set rng = ActiveWorkbook.Names("name1").RefersToRange
    rng = 1
   
End Sub

使用Ver：Win10, Excel For Office365

続きを読む "VBAで名前の定義を扱う"

同じマクロを全てのブックで使用したい場合

PERSONAL.XLSBという個人用ブックに
マクロを保存すれば
同じマクロを全てのブックで使用できるようになります。
個人用ブックを作成する方法について見ていきます。

通常，マクロは特定のブックに保存されます。
特定のブックに保存したマクロは
そのブックを開いて実行します。

PERSONAL.XLSBという個人用ブックを作成して
マクロを保存すれば
同じマクロを全てのブックで使用できるようになります。

個人用ブックに保存されているマクロは，
同じPCであれば
どのブックでも使用できるようになります。

個人用ブックを作成する方法について見ていきます。

例として
ショートカットキーCtrl+Shift+Aを押すと選択範囲に〇が入力される
マクロを作成します。

まず，〇を入力するマクロを
マクロの自動記録で作成します。

[開発]タブ-[マクロの記録] をクリックして
表示されたウィンドウの[マクロ名] に名前を適宜入力します。

ショートカットキーの入力ボックスを選択して
Shift+Aを押します。
押したキーが自動で入力されます。

[マクロの保存場所] を，[個人用マクロ ブック] にします。
[OK] をクリックします。

A1セルに〇を入力して
[開発]-[記録終了]をクリックして記録を終了します。

VBEを開いて個人用ブックを確認します。
VBAProject(PERSONAL.XLSB)が個人用ブックです。
Module1が自動で挿入されているのでクリックすると
先ほど記録したマクロがみれます。

自動記録したマクロを次のように修正します。

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Selection.FormulaR1C1 = "〇"

End Sub

これで選択範囲に〇が入力されるマクロになりました。

VBEの保存ボタンを押して個人用ブックを保存します。

下記ファルダに
C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART
PERSONAL.XLSBが保存されます
このPERSONAL.XLSBが個人用ブックです。

これで
いま保存したマクロは
同じPCでマクロ有効であればいつでも使用できます。

別のPCで使用したい場合は
PERSONAL.XLSBを
別のPCの下記ファルダに
C:\Users\username\AppData\Roaming\Microsoft\Excel\XLStart
保存します。

使用Ver：Win10, Excel For Office365

続きを読む "同じマクロを全てのブックで使用したい場合"

リストの入力規則を設定する

VBAでリストの入力規則を設定する方法について
以下の項目を見ていきます。
1. リストの入力規則とは？
2. VBAでリストの入力規則を設定する

1. リストの入力規則とは？

セルの値を"リスト"で入力できる設定（＝入力規則）があります。

リストを作成するには
[データ]タブ-[データの入力規則]をクリックして
表示されるウィンドウで設定をします。

[入力値の種類]を[リスト]にして，
[元の値]に「1,2,3,4,5」と入力します。

[OK]ボタンで入力規則の設定を終了します。
設定後は次の画像のように
ドロップダウンリストが表示されるようなり
このリストからセルに入力する値を選択できます。

[元の値]には
セルの範囲や名前を指定することもできます。

リストの[元の値]に指定する範囲や名前は
単一の行，列の参照でないと設定できません。

[元の値]に列全体を指定すると
値が入っている最後のセル以降の空白セルは無視されます。
リストを設定後
列の内容を変更すると
変更内容に合わせてリストの内容も変更されます。

リストを多数設定したい場合は
リストの項目用のシートを作成して
そのシートの列を参照すれば管理がしやすいです。

デフォルトの設定では
入力規則以外の値が入力されたとき
アラートが表示されて停止する設定になっています。

[エラーメッセージ]タブにある
[無効なデータが入力されたときエラーメッセージを表示する]の
チェックを外すとこのアラートは出なくなり
リストに指定した値以外が入力できるようになります。

2. VBAでリストの入力規則を設定する

次のコードは選択しているセルに
「1,2,3,4,5」のリストを入力規則として設定します。

Sub macro20200601a()
'リストの入力規則を設定する

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, _
            Formula1:="1,2,3,4,5"
        .InCellDropdown = True
        .IMEMode = xlIMEModeNoControl
        .ShowInput = False
        .ShowError = False
    End With

End Sub

入力規則を追加するには
ValidationオブジェクトのAddメソッドを使用します。
Addメソッドを実行する前にDeleteをすることで
既存の入力規則を削除してから新しい入力規則を追加します。
既存入力規則がある状態で追加しようとすると下
記のエラーになります。

実行時エラー'1004':
アプリケーション定義またはオブジェクト定義のエラーです。

入力規則にはいくつかの種類がありますが
AddメソッドのType引数をxlValidateListにすると
入力規則の種類がリストになります。

AddメソッドのFormula1引数に
リストに表示したい内容を指定します。
上記コードではカンマで区切った文字列を指定しています。

Formula1引数にセルの範囲や名前を指定する場合については
次にいくつかの例を示します。

参照範囲	Formula1引数
同じシート内の範囲O1:O10	Formula1:="=$O$1:$O$10"
別のシートSheet1のA列	Formula1:="=Sheet1!$A:$A"
名前name	Formula1:="=name"
同じシート内の範囲O1:O10をRangeで指定	Formula1:="=" & Range("O1:O10").Address
別のシートSheet1のA列をRangeで指定	Formula1:="=" & Sheets("Sheet1").Name & "!" & Range("A:A").Address

InCellDropdown = Trueにすると
ドロップダウン形式で表示されます。
Falseにするとドロップダウンリストが表示されません。

ShowInput = Falseで
入力規則が設定されたセルを選択したときに表示するメッセージを
表示させないようにします。

ShowError = Falseで
リストで指定した以外の値が入力されたときに
エラーを表示させないようにします。

使用Ver：Win10, Excel For Office365

続きを読む "リストの入力規則を設定する"