エクセルの使い方: VBA

ラベル VBA の投稿を表示しています。すべての投稿を表示

2016年12月11日日曜日

マクロ（VBA）でプルダウンを作成する（選択セル・指定セル・範囲指定の3種類作成）

Excel2013のマクロ（VBA）でプルダウンを作成する方法です。

選択セル・指定セル・範囲指定の3種類を作成します。

リボンの［開発］～コントロールグループの［コードの表示］をクリックします。

≪選択セルにプルダウンを作成する≫

下記のコードを入力します。

Option Explicit

Sub MyMakeList()
Selection.Cells.Validation.Add Type:=xlValidateList, _
Formula1:="見積書, 請求書, 納品書, 領収書"
End Sub

１．リボンの［開発］～コードグループの［マクロ］をクリックします。
２．作成した「MyMakeList」を選択します。
３．実行します。

これで選択セルにプルダウンが作成できました。

しかし、このままですと問題があります。
リストを変更したい場合など、作成済みセルに再作成しようとすると、下画像のようなエラーが発生します。

このエラーが発生しないよう、初めに作成済みリストを削除してから作成するように変更します。

Sub MyMakeList()
    Selection.Cells.Validation.Delete
    Selection.Cells.Validation.Add Type:=xlValidateList, _
        Formula1:="見積書, 請求書, 納品書, 領収書, 受領書"
End Sub

これで下画像のようにエラーを発生させないで、リストを再作成できました。

≪指定セルにプルダウンを作成する≫

下コードのようにRangeでセルを指定します。

Sub MyMakeList()
    Range("B4").Validation.Delete
    Range("B4").Validation.Add Type:=xlValidateList, _
        Formula1:="見積書, 請求書, 納品書, 領収書, 受領書"
End Sub

≪範囲指定したセルにプルダウンを作成する≫

下のようにRangeで範囲指定し実行します。

Sub MyMakeList()
    Range("B4:D5").Validation.Delete
    Range("B4:D5").Validation.Add Type:=xlValidateList, _
        Formula1:="見積書, 請求書, 納品書, 領収書, 受領書"
End Sub

これで指定したセル全体にプルダウンが作成できました。

≪セルに入力したデータからリストを作成する≫

入力済みデータからプルダウンを作成するには、下記のコードになります。

Sub MyMakeList()
    Range("D2").Validation.Delete
    Range("D2").Validation.Add Type:=xlValidateList, _
        Formula1:=Range("B2") & "," & Range("B3") & "," & _
        Range("B4") & "," & Range("B5")
End Sub

2016年12月10日土曜日

VLookup関数をVBA（マクロ）で使い検索する（見つからなければエラー処理を行う）

Excel2013で、VLookup関数をVBA（マクロ）で使い検索し、結果をセルに表示します。

下画像のようなシートを作成しました。

１：検索するコードを入力します。
２：VLookup関数を使い検索を開始します。
３：検索元の製品一覧表です。
４：検索結果の「製品名」と「単価」を表示します。

コードを入力しないで［検索］ボタンをクリックすると、下のエラーメッセージを表示します。

ボタンのクリックイベント内に下のVBAコードを入力します。

Option Explicit

Private Sub CommandButton1_Click()
    Dim s As Variant

    If Range("C2") = "" Then
        Beep
        MsgBox "検索するコードを入力してください。"
        Exit Sub
    End If

    s = WorksheetFunction.VLookup(Range("C2"), Range("E3:G13"), 2, False)
    Range("C8") = s

    s = WorksheetFunction.VLookup(Range("C2"), Range("E3:G13"), 3, False)
    Range("C9") = s

End Sub

このようにVlookup関数をVBAで使うには、WorksheetFunction.VLookupのように使用します。

VLookup(Range("C2"), Range("E3:G13"), 2, False) ：コードを製品一覧表から検索し、見つかれば2列目（製品名）のデータを返します。

VLookup(Range("C2"), Range("E3:G13"), 3, False) ：コードを製品一覧表から検索し、見つかれば3列目（単価）のデータを返します。

■エラー処理を行う

もし入力したコードが見つからない場合、「実行時エラー’1004’ WorksheetFunctionクラスのVLookupプロパティを取得できません。」とエラーが発生します。

On Error Gotoを使い、このエラーに対処したVBAコードです。

Private Sub CommandButton1_Click()
    Dim s As Variant

    If Range("C2") = "" Then
        Beep
        MsgBox "検索するコードを入力してください。"
        Exit Sub
    End If

On Error GoTo ErrSub
    s = WorksheetFunction.VLookup(Range("C2"), Range("E3:G13"), 2, False)
    Range("C8") = s

    s = WorksheetFunction.VLookup(Range("C2"), Range("E3:G13"), 3, False)
    Range("C9") = s

    Exit Sub

ErrSub:
    Beep
    MsgBox "入力されたコードは見つかりませんでした。"

End Sub

実行すると下画像のようにメッセージが表示されます。

■マクロで実行するには

マクロで実行するには、標準モジュール内に入力します。
Rangeなどのセル指定には、Sheetsでシート名を指定します。

Sub MyVLookup()
    Dim s As Variant

    If Sheets("Sheet1").Range("C2") = "" Then
        Beep
        MsgBox "検索するコードを入力してください。"
        Exit Sub
    End If

On Error GoTo ErrSub
    With Sheets("Sheet1")
        s = WorksheetFunction.VLookup(.Range("C2"), .Range("E3:G13"), 2, False)
        Range("C8") = s

        s = WorksheetFunction.VLookup(.Range("C2"), .Range("E3:G13"), 3, False)
        Range("C9") = s
    End With
    Exit Sub

ErrSub:
    Beep
    MsgBox "入力されたコードは見つかりませんでした。"
End Sub

１．作成するマクロを実行するには、リボンの［開発］～［マクロ］をクリックします。
２．マクロダイアログボックスから、上で作成した「MyVLookup」を選択します。
３．［実行］ボタンをクリックします。　これで検索できます。

2016年11月16日水曜日

ボタンをクリックするとVBAでマクロを実行させる

Excel2013で、ボタンをクリックするとVBAでマクロを実行させる方法です。

下のMacro1は1から10まで自動的に入力するマクロです。
このマクロをVBAで実行させます。

コマンドボタンをシートに配置し、ボタンをダブルクリックします。

VBEの画面になるので、マクロのコードを確認します。
マクロのコードは、標準モジュールに登録されています。

ボタンをダブルクリックし表示される、Clickイベント内にマクロ名を入力します。

Private Sub CommandButton1_Click()
Macro1
End Sub

ボタンをクリックすると、マクロが実行され1から10まで入力されました。

2016年6月28日火曜日

Findで見つかればそのセルへ、見つからなければエラー処理するVBA

Excel2013のVBAで、Findで見つかればそのセルへ、見つからなければエラー処理をします。

下記のVBAコードで実行します。

Option Explicit

Private Sub MyFindData()
    Dim rng As Range

    Set rng = Range("B:B").Find(Range("C2"))

    If rng Is Nothing Then
        MsgBox "見つかりませんでした。"
    Else
        Range(rng.Address).Activate
    End If

End Sub

Set rngでFindの結果を、rngオブジェクト変数に代入します。

is nothingで検索結果のセルが見つかったかどうか判定します。

見つかればActivateでそのセルに移動します。

検索し見つかった場合の実行結果です。

検索し見つからなかった場合の実行結果です。

列を指定しその中からFindで検索するVBA

Excel2013のVBAで、列を指定しその中からFindで検索します。

列を指定するには、Range("B:B")のようにします。

Ｂ列の中からＣ2セルのデータをFindで検索し、見つかった行番号を表示するコードです。

Private Sub MyFindData()

MsgBox Range("B:B").Find(Range("C2")).Row

End Sub

実行結果です。
検索し見つかった行番号が表示されました。

Range("B").Find(Range("C2")).Rowのように、Range("B").とした場合は「アプリケーション定義またはオブジェクト定義のエラーです」と表示されエラーになります。

今いるセルの位置を取得するVBA

Excel2013のVBAで、今いるセルの位置を取得します。

下記のVBAコードで取得しました。

Private Sub MyGetActiveCell()
    Range("B2") = ActiveCell.Address
    Range("B3") = ActiveCell.Column
    Range("B4") = ActiveCell.Row
End Sub

下はＣ6セルにカーソルを移動し実行した結果です。

ActiveCell.Addressでセル番地が絶対番地で取得できています。
ActiveCell.Columnでセルの列位置が取得できています。
ActiveCell.Rowでセルの行位置が取得できています。

上を利用しCellsでセル位置を指定したVBAコードです。

Private Sub MyGetActiveCell()
    Range("B2") = ActiveCell.Address
    Range("B3") = ActiveCell.Column
    Range("B4") = ActiveCell.Row

    Cells(ActiveCell.Row, ActiveCell.Column).Interior.Color = RGB(255, 0, 0)
End Sub

下はＣ7セルにカーソルを移動し実行した結果です。

Cells(ActiveCell.Row, ActiveCell.Column)でセルを指定し、Interior.Colorで背景色を設定しています。

2016年6月27日月曜日

ブレークポイントを設定し、変数の値を確認する

Excel2013のVBEでブレークポイントを設定し、変数の値を確認する方法です。

ここでは下記のFor～Nextを使ったVBAを使います。

Sub fornext()
    For i = 1 To 10
        Cells(i, 2) = i
    Next
End Sub

コードが入力された行の左側のグレイ部分をクリックします。
するとそのコードの行が茶色に反転表示されます。

１．ツールバーの［Sub／ユーザーフォームの実行（Ｆ5）］をクリックします。
２．ブレークポイントの行でプログラムが停止するので、ブレークポイントの行より上の行の変数位置にマウスポインターを移動します。
ここでは「For i = 1 To 10」の「i」の部分にマウスポインターを移動します。
これで変数に入っている値が表示されます。

さらにツールバーの［Sub／ユーザーフォームの実行（Ｆ5）］をクリックするとプログラムは継続され、ブレークポイントで停止し、変数の値を確認することができます。

2015年5月12日火曜日

ColorIndexの色の一覧

Word2013のVBAで使う、ColorIndexの色の一覧です。

ColorIndex=1
&H000000
RGB(0,0,0)
■■■■■■■■■■

ColorIndex=2
&HFFFFFF
RGB(255,255,255)
■■■■■■■■■■

ColorIndex=3
&HFF0000
RGB(255,0,0)
■■■■■■■■■■

ColorIndex=4
&H00FF00
RGB(0,255,0)
■■■■■■■■■■

ColorIndex=5
&H0000FF
RGB(0,0,255)
■■■■■■■■■■

ColorIndex=6
&HFFFF00
RGB(255,255,0)
■■■■■■■■■■

ColorIndex=7
&HFF00FF
RGB(255,0,255)
■■■■■■■■■■

ColorIndex=8
&H00FFFF
RGB(0,255,255)
■■■■■■■■■■

ColorIndex=9
&H800000
RGB(128,0,0)
■■■■■■■■■■

ColorIndex=10
&H008000
RGB(0,128,0)
■■■■■■■■■■

ColorIndex=11
&H000080
RGB(0,0,128)
■■■■■■■■■■

ColorIndex=12
&H808000
RGB(128,128,0)
■■■■■■■■■■

ColorIndex=13
&H800080
RGB(128,0,128)
■■■■■■■■■■

ColorIndex=14
&H008080
RGB(0,128,128)
■■■■■■■■■■

ColorIndex=15
&HC0C0C0
RGB(192,192,192)
■■■■■■■■■■

ColorIndex=16
&H808080
RGB(128,128,128)
■■■■■■■■■■

ColorIndex=17
&H9999FF
RGB(153,153,255)
■■■■■■■■■■

ColorIndex=18
&H993366
RGB(153,51,102)
■■■■■■■■■■

ColorIndex=19
&HFFFFCC
RGB(255,255,204)
■■■■■■■■■■

ColorIndex=20
&HCCFFFF
RGB(204,255,255)
■■■■■■■■■■

ColorIndex=21
&H660066
RGB(102,0,102)
■■■■■■■■■■

ColorIndex=22
&HFF8080
RGB(255,128,128)
■■■■■■■■■■

ColorIndex=23
&H0066CC
RGB(0,102,204)
■■■■■■■■■■

ColorIndex=24
&HCCCCFF
RGB(204,204,255)
■■■■■■■■■■

ColorIndex=25
&H000080
RGB(0,0,128)
■■■■■■■■■■

ColorIndex=26
&HFF00FF
RGB(255,0,255)
■■■■■■■■■■

ColorIndex=27
&HFFFF00
RGB(255,255,0)
■■■■■■■■■■

ColorIndex=28
&H00FFFF
RGB(0,255,255)
■■■■■■■■■■

ColorIndex=29
&H800080
RGB(128,0,128)
■■■■■■■■■■

ColorIndex=30
&H800000
RGB(128,0,0)
■■■■■■■■■■

ColorIndex=31
&H008080
RGB(0,128,128)
■■■■■■■■■■

ColorIndex=32
&H0000FF
RGB(0,0,255)
■■■■■■■■■■

ColorIndex=33
&H00CCFF
RGB(0,204,255)
■■■■■■■■■■

ColorIndex=34
&HCCFFFF
RGB(204,255,255)
■■■■■■■■■■

ColorIndex=35
&HCCFFCC
RGB(204,255,204)
■■■■■■■■■■

ColorIndex=36
&HFFFF99
RGB(255,255,153)
■■■■■■■■■■

ColorIndex=37
&H99CCFF
RGB(153,204,255)
■■■■■■■■■■

ColorIndex=38
&HFF99CC
RGB(255,153,204)
■■■■■■■■■■

ColorIndex=39
&HCC99FF
RGB(204,153,255)
■■■■■■■■■■

ColorIndex=40
&HFFCC99
RGB(255,204,153)
■■■■■■■■■■

ColorIndex=41
&H3366FF
RGB(51,102,255)
■■■■■■■■■■

ColorIndex=42
&H33CCCC
RGB(51,204,204)
■■■■■■■■■■

ColorIndex=43
&H99CC00
RGB(153,204,0)
■■■■■■■■■■

ColorIndex=44
&HFFCC00
RGB(255,204,0)
■■■■■■■■■■

ColorIndex=45
&HFF9900
RGB(255,153,0)
■■■■■■■■■■

ColorIndex=46
&HFF6600
RGB(255,102,0)
■■■■■■■■■■

ColorIndex=47
&H666699
RGB(102,102,153)
■■■■■■■■■■

ColorIndex=48
&H969696
RGB(150,150,150)
■■■■■■■■■■

ColorIndex=49
&H003366
RGB(0,51,102)
■■■■■■■■■■

ColorIndex=50
&H339966
RGB(51,153,102)
■■■■■■■■■■

ColorIndex=51
&H003300
RGB(0,51,0)
■■■■■■■■■■

ColorIndex=52
&H333300
RGB(51,51,0)
■■■■■■■■■■

ColorIndex=53
&H993300
RGB(153,51,0)
■■■■■■■■■■

ColorIndex=54
&H993366
RGB(153,51,102)
■■■■■■■■■■

ColorIndex=55
&H333399
RGB(51,51,153)
■■■■■■■■■■

ColorIndex=56
&H333333
RGB(51,51,51)
■■■■■■■■■■

2014年8月20日水曜日

色番号に対応する色を表示する

Excel2013で、VBAを使い色番号に対応する色を表示します。

「番号」と「色」をセルに入力し、リボンの［開発］－［コードの表示］をクリックします。

下のVBAコードを入力します。

Sub setcolorindex()
  For i = 0 To 56
      Cells(i + 2, 2).Value = i
      Cells(i + 2, 3).Interior.ColorIndex = i

  Next
End Sub

リボンの［マクロ］をクリックし、一覧から作成したマクロ setcolorindexを実行します。

色番号に対応する色が表示されました。

2014年6月19日木曜日

VBAで For Next を使ってみる

Excel2013のVBAで For Next を使ってみます。

For Nextを使うと指定した回数、繰り返し処理を行うことができます。
■構文
For カウンタ変数 = 初期値 To 繰り返し数
処理内容
Next

リボンの[開発]－コントロールグループの[コードの表示]をクリックします。
コードの表示のヒントです「コントロールの Visual Basic コードを編集します。」

下記のコードを入力します。
Sub fornext()
    For i = 1 To 10
        Cells(i, 2) = i
    Next
End Sub

Excel画面に戻り、リボンの[開発]－コードグループの[マクロ]をクリックします。

マクロダイアログボックスが開くので、VBAで入力した先頭行の名前を選択し[実行]をクリックします。

1行目B列のセルに1から10を入力できました。
For i = 1 To 10
Cells(i, 2) = i
Next
で i が1から10になるまで繰り返します。
１． i が 1の場合： Cells(1,2)=1 になるので　B1セルに1が入力されます。
２． i が 2の場合： Cells(2,2)=2 になるので　B2セルに2が入力されます。
３． i が 3の場合： Cells(3,2)=3 になるので　B3セルに3が入力されます。
～
１０． i が 10の場合： Cells(10,2)=10 になるので　B10セルに10が入力されます。

通常、For Nextは1づつ増えますが、加算する数を指定することもできます。

■構文
For カウンタ変数 = 初期値 To 繰り返し数 Step 加算値
    処理内容
Next

下記のコードを入力します。
Sub fornext()
    For i = 1 To 10 Step 2
        Cells(i, 2) = i
    Next
End Sub

入力値もセル位置も＋２されました。

2014年5月10日土曜日

セルを指定するCellsプロパティ - Excel2013

Excel2013でセルを指定するCellsプロパティの使い方です。

CellsプロパティをＶＢＡで使います。VBAの使い方は、VBA入門を参照してください。
使用方法：Cells(行番号,列番号)
ここではCellsプロパティを使い、最終セルに移動するVBAコードを作成しました。

Sub cellslast()
'最終セルに移動
Cells(1048576, 16384).Select
End Sub

マクロを実行します。

最終セルに移動しました。

登録: 投稿 ( Atom )

クッキー及びWebビーコンについて

当ウェブサイトはクッキー、ビーコンと呼ばれる技術を使用しています。
クッキーとは、コンピュータに置かれた小容量のユーザー情報で、これによりWebサイトはユーザーを識別できるようにしたり、各サービスの提供を行ったりします。このクッキーにより、ユーザー個人を特定したり、プライバシーを侵害することはありません。
ビーコンとは、特定のページのアクセス状況を知るための技術です。
クッキー機能はブラウザの設定で解除することができます。（ただし、クッキーを無効化したり削除すると、当サイトで一部の機能が使用できなくなることがあります。）