項目

全てのブック名とシート名を取得する

内容説明

開いている全てのブック名とシート名を取得する方法です。


'Book名やシート名を取得するサンプル
'この関数を実行すると開いている全てのBook名やシート名を表示します
Sub a_E97017()
  Dim ii&, jj&, nn&, cc$
  MsgBox "[" + ActiveWorkbook.Name + "] " + ActiveSheet.Name, , "アクティブシート名"
  For jj = 1 To Workbooks.Count 'ファイルの数
    cc = "[" + Workbooks(jj).Name + "]"
    cc = cc & vbCrLf & "(Worksheet)"
    For ii = 1 To Workbooks(jj).Worksheets.Count 'シートの数
      cc = cc & vbCrLf & Workbooks(jj).Worksheets(ii).Name
    Next
    cc = cc & vbCrLf & "(Chart)"
    For ii = 1 To Workbooks(jj).Charts.Count
      cc = cc & vbCrLf & Workbooks(jj).Charts(ii).Name
    Next
    cc = cc & vbCrLf & "(DialogSheet)"
    For ii = 1 To Workbooks(jj).DialogSheets.Count
      cc = cc & vbCrLf & Workbooks(jj).DialogSheets(ii).Name
    Next
    cc = cc & vbCrLf & "(Module)"
    For ii = 1 To Workbooks(jj).Modules.Count
      cc = cc & vbCrLf & Workbooks(jj).Modules(ii).Name
    Next
    MsgBox cc, , "シート名"
  Next
End Sub
'ActiveWindow.Caption 'アクティブブック(ﾌｧｲﾙ)名
'ActiveWorkbook.Name  'アクティブブック(ﾌｧｲﾙ)名
'ActiveWorkbook.FullName'アクティブブック(ﾌｧｲﾙ)のフルパス名
'  FullNameプロパティの値はPathプロパティの後に円記号(\)とNameプロパティをつなげたもの
'ActiveSheet.Name     'アクティブシート名
'Windows.Count        '開いているブック(ﾌｧｲﾙ)の数
'Windows(jj).Caption  '開いているjj番目のブック(ﾌｧｲﾙ)名
'Workbooks(jj).Name   '開いているjj番目のブック(ﾌｧｲﾙ)名
'Workbooks(jj).Worksheets.Count      'jj番目のブック(ﾌｧｲﾙ)のワークシート数
'Workbooks(jj).Worksheets(ii).Name   'jj番目のブック(ﾌｧｲﾙ)のii番目のワークシート名
'Workbooks(jj).Charts.Count          'jj番目のブック(ﾌｧｲﾙ)のチャート数
'Workbooks(jj).Charts(ii).Name       'jj番目のブック(ﾌｧｲﾙ)のii番目のチャート名
'Workbooks(jj).DialogSheets.Count    'jj番目のブック(ﾌｧｲﾙ)のダイアログシート数
'Workbooks(jj).DialogSheets(ii).Name 'jj番目のブック(ﾌｧｲﾙ)のii番目のダイアログシート名
'Workbooks(jj).Modules.Count         'jj番目のブック(ﾌｧｲﾙ)のﾓｼﾞｭｰﾙｼｰﾄ数
'Workbooks(jj).Modules(ii).Name      'jj番目のブック(ﾌｧｲﾙ)のii番目のﾓｼﾞｭｰﾙｼｰﾄ名
'Workbooks(jj).Sheets.Count    'シートの数(ワークシート,チャート,ダイアログシート,モジュールシート 全てのシートを含む)
'Workbooks(jj).Sheets(ii).Name 'シート名  (〃)

項目

選択されたセル範囲を取得する

内容説明

VBAで、複数選択された個々のワークシートのセル範囲を取得する方法を示します。
Selectionプロパティで選択セルのRangeオブジェクトが取得出来ます。何も選択していないときは Nothing が返されます。
また、Range,Columns,Rowsソメッドでのセル範囲を数値で代入する方法も示しています。

'全ての選択範囲を処理する例
  Dim rg As Range
  For Each rg In Selection
    'Debug.Print rg
    '処理
  Next

'複数選択範囲を取得するサンプル
'ﾜｰｸｼｰﾄで複数範囲を選択してこの関数を実行して下さい
Sub a_areas016()
  Dim ii&, jj&, x1&, x2&, y1&, y2&, cc$, are as Range
  cc = ""
  For ii = 1 To Selection.Areas.Count '選択範囲の数
    Set are = Selection.Areas(ii)
    x1 = are.Columns.Column '選択範囲ii番目の左端列番号
    x2 = are.Columns(are.Columns.Count).Column '選択範囲ii番目の右端列番号
    y1 = are.Rows.Row '選択範囲ii番目の上端行番号
    y2 = are.Rows(are.Rows.Count).Row '選択範囲ii番目の下端行番
    cc = cc & "選択範囲" & ii & " " & kRang(x1%, y1%, x2%, y2%) & vbCrLf
  Next
  MsgBox cc, , "複数選択範囲を取得するサンプル"
End Sub
Function kColumn(x0&) As String ' 1->"A" 2->"B" 27->"AA" Column(列)変換
  kColumn = IIf(x0 > 26, Chr(Asc("@") + x0 \ 26), "") & Chr(Asc("@") + x0 Mod 26)
End Function
Function kRang(x1&, y1&, x2&, y2&) As String '数値範囲を "A1:B5" 形式に変換
  kRang = kColumn(x1) & y1 & ":" & kColumn(x2) & y2
End Function
'Range("A1").Select    'セルの選択　　-> Range(kColumn(x1) & y1)
'Range("A1:C4").Select 'セル範囲の選択-> Range(kRang(x1, y1, x2, y2))
'Columns("D:E").Select '列の選択　　　-> Columns(kColumn(x1) & ":" & kColumn(x2))
'Rows("7:9").Select    '行の選択　　　-> Range(CStr(y1) & ":" & y2)
'Selection.Areas.Count '選択範囲の数
'Selection.Areas(ii).Columns.Column '選択範囲ii番目の左端列番号
'Selection.Areas(ii).Columns(Selection.Areas(ii).Columns.Count).Column '選択範囲ii番目の右端列番号
'Selection.Areas(ii).Rows.Row '選択範囲ii番目の上端行番号
'Selection.Areas(ii).Rows(Selection.Areas(ii).Rows.Count).Row '選択範囲ii番目の下端行番号

項目

砂時計マウスポインタを使う

内容説明

マクロで時間のかかる処理をする時にマウスポインタを砂時計型ポインタにします。

砂時計型ポインタにするには
Application.Cursor = xlWait

標準のポインタに戻すには
Application.Cursor = xlNormal

但し、この記述はExcel7.0以降で使えます。

項目

マクロの実行中に画面の更新をしないようにする

内容説明

マクロ実行中はマクロの動作に合わせて画面表示が更新されます。
この時に問題になるのは、途中過程を見せたくないのに表示される、画面がチラチラする、速度が遅い...等です。
次の１行をマクロの始めに挿入すれば画面の更新を行わないようにする事が出来ます。

Application.ScreenUpdating = False '画面の更新を行わない

ScreenUpdatingプロパティにFalseを設定して画面の更新を行わないようにすると、マクロ処理の過程を隠匿し実行速度を向上する事が出来ます。
但し、ScreenUpdatingプロパティに頼ってはいけません。 ActivateメソッドやSelectメソッドを用いないようにして画面がちらつかないよう記述して下さい。
なお、マクロの終了時にはScreenUpdatingプロパティの値を True に戻して下さい。

項目

モジュールシートでVisualBasicキーワードのヘルプを簡単に参照する

内容説明

モジュールシートでマクロ記述時に、VisualBasicキーワードの意味を調べたい場合が頻繁に発生します。
普通にヘルプを開いてキーワード検索するのは面倒です。

最速の方法は調べたいキーワードを選択して[F1]キーを押します。又は、メニュー[ヘルプ]-[キーワードのヘルプ]をクリックします。
即、選択キーワードのVisual Basicヘルプが開きます。 複数のヘルプトピックがある場合は選択するダイアログボックスが開きます。

項目

マクロのコードを記述途中でカーソルを他の場所に移動すると構文エラーのメッセージボックスが表示されて困る

内容説明

マクロでVBAコードを記述途中に、他の部分を参照するためカーソルを移動すると構文エラーのメッセージボックスが表示されます。
これは気分を害します。構文の記述が完了してないのは分かっているわけですから大きなお世話です。

モジュールでVBAコードを記述しているときに構文の自動チェック機能をオフにするにはVBE-[ツール]メニュー-[オプション]を開き、[モジュール設定]タブの[構文エラーを表示する]のチェックを外します。
これで 構文エラーのメッセージボックスは表示されません。構文エラーの部分は赤色表示に変わるだけです。

注) VBE-[ツール]-[オプション]にていろいろVBEのカスタマイズが出来ます。
[編集]タブ
[変数の宣言を強制する][自動メンバ表示]等はチェックしておきましょう。
タブ間隔は筆者の場合2に設定しています。
[全般]タブ
エラートラップは[クラスモジュールで中断]にしておきましょう。

項目

マクロでシートを削除するとき確認のメッセージボックスが表示されて困る

内容説明

マクロ(VBA)で次の様にシート削除を行う命令を実行すると、削除を確認するメッセージボックスが表示されて困ります。

Sheets("Sheet1").Delete

マクロを実行している間にユーザーに入力を求めるメッセージや警告メッセージを表示させたくないときはDisplayAlertsプロパティにFalseを設定します。

Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True

注) DisplayAlertsプロパティは色々な場面で使うことができます。
次の使用例は ブックの変更の保存を確認するメッセージを表示せず、内容の変更は保存せずにブックを閉じます。

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

項目

ワークシートまたはグラフの背景に画像を表示する

内容説明

SetBackgroundPictureソメッドによりワークシートやグラフの背景に画像を表示できます。(デスクトップの壁紙のように)
このメソッドはExcel97/95で使えます。

構文
Object.SetBackgroundPicture (fileName)

Object　：対象となるWorksheet又はChartオブジェクトを指定します。
fileName：表示する画像ファイルを指定します｡(bmp,jpg,gifなどのExcelで扱えるファイル)
　　解除はfileNameを "" に指定します。
　　画像データはExcelファイル(xls)に保存されます。

使用例：アクティブワークシートの背景に花見.bmpを設定します。

Sub a_e04()
  ActiveSheet.SetBackgroundPicture "c:\windows\花見.bmp"
End Sub

項目

Subプロシージャでは正常に動作するのにユーザー定義ワークシート関数(Functionプロシージャ)にすると動作しない

内容説明

Subプロシージャでは正常に動作するのにユーザー定義ワークシート関数(Functionプロシージャ)にするとエラーが発生する、バグでは、という質問が結構あります。
これはバグではなくExcelの仕様です。

ユーザー定義ワークシート関数は、数式や組み込みワークシート関数およびVisual Basicコードを組み合わせて作成しますが、プロパティを設定したり、メソッドを実行したりする次のような動作は実行できません。

• セルの値の変更、セルの挿入、削除、または書式の設定
• シートの移動、名前の変更、削除、または追加
• 計算方法または画面表示の変更 等...

ワークシート関数では、引数や入力してある数値やExcelで算出できる数値を基にした計算だけを定義するように作る必要があります。

奥義：E00M095 セルの値や書式を変更するユーザー関数？

項目

JISの規定による数値の丸め方

内容説明

JIS Z 8401に規定された数値の丸めを行う関数を示します。
数値を丸めて指定した桁数にするROUNDJ関数と、数値を丸めて指定した有効桁数にするROUND2J関数です。

規定では、｢ある数値を有効桁数n桁の数値に丸める場合、または小数点以下n桁の数値に丸める場合には(n+1)桁目以下の数値をつぎのように整理する｣となっており、基本的には四捨五入の方法であり異なるのは次の場合です。
(n+1)桁目以下の数値が5であるか、または(n+1)桁目以下の数値が切り捨てたものか切り上げたが分からない場合で、n桁目の数値が偶数(0,2,4,6,8)ならば切り捨てるというものです。(規則A)

'ROUNDJ 関数
'数値を丸めて指定した桁数にします
'書式　ROUNDJ(数値, 桁数)
'数値　丸めの対象となる数値を指定します
'桁数　数値を丸めた結果の桁数を指定します
'例 =ROUNDJ(0.0625,3) → 0.062   =ROUNDJ(125.1,-1) → 130
'   =ROUNDJ(0.095,2)  → 0.10    =ROUNDJ(125,-1)   → 120
Function ROUNDJ(aa As Currency, nn As Long) '数値を丸めて指定した桁数にする
  ROUNDJ = CLng(aa * 10 ^ nn) * 10 ^ -nn
End Function

'ROUND2J 関数
'数値を丸めて指定した有効桁数にします
'書式　ROUND2J(数値, 有効数字の桁数)
'数値　　　　　　丸めの対象となる数値を指定します
'有効数字の桁数　数値を丸めた結果の有効数字の桁数を指定します
'例 =ROUND2J(0.105,2)  → 0.10
'   =ROUND2J(0.0955,2) → 0.096
Function ROUND2J(aa As Currency, nn As Long) '数値を任意の有効桁数に丸める
  ROUND2J = ROUNDJ(aa, -Int(Application.Log(Abs(aa))) - 1 + nn)
End Function

その2)Excel2000で実装されたJISの丸めを行うRound関数
Excel2000からJISの丸めを行うRound関数が追加されました。
Round 関数
　機能：指定された小数点位置で丸めた数値を返します｡
　構文：Round(数値, [桁数])
　　数値：必ず指定します｡丸めを行う数式を指定します｡
　　桁数：省略可能です。丸めを行う小数点以下の桁数を表す数値を指定します。省略するとRound 関数は整数値を返します。
構文はROUNDワークシート関数と同様ですが、Round関数では[桁数]で負数の指定が出来ません。

追記：数値を任意の有効桁数に四捨五入する
数値を任意の有効桁数に四捨五入するROUND2関数です。
また、数値を任意の有効桁数に切上げるROUNDUP2関数、数値を任意の有効桁数に切下げるROUNDDOWN2関数もあわせて示します。
以下のコードを標準モジュールにコピーしてお使い下さい。


'ROUND2 関数
'数値を四捨五入して指定した有効桁数にします｡
'書式　ROUND2(数値, 有効数字の桁数)
'数値　　　　　　四捨五入の対象となる数値を指定します｡
'有効数字の桁数　数値を四捨五入した結果の有効数字の桁数を指定します。
Function ROUND2(aa, nn) '数値を任意の有効桁数に四捨五入する
  ROUND2 = Application.Round(aa, -Int(Application.Log(Abs(aa))) - 1 + nn)
End Function

'ROUNDUP2 関数  '数値を任意の有効桁数に切上げる
Function ROUNDUP2(aa, nn)
  ROUNDUP2 = Application.RoundUp(aa, -Int(Application.Log(Abs(aa))) - 1 + nn)
End Function

'ROUNDDOWN2 関数  '数値を任意の有効桁数切下げる
Function ROUNDDOWN2(aa, nn)
  ROUNDDOWN2 = Application.RoundDown(aa, -Int(Application.Log(Abs(aa))) - 1 + nn)
End Function

なお、数式で入力したい場合は次のようになります。
B2が数値で()内の4が有効桁数になりますので、実状に合わせて入力して下さい。

=ROUND(B2,-INT(LOG(ABS(B2)))-1+(4))
=ROUNDUP(B2,-INT(LOG(ABS(B2)))-1+(4))
=ROUNDDOWN(B2,-INT(LOG(ABS(B2)))-1+(4))