ExcelVBA 自分の備忘録 | なんで勉強するの

列幅を適正化する

ActiveSheet.Columns.AutoFit

1	ActiveSheet.Columns.AutoFit

テキストを中央揃え

Range("A1").HorizontalAlignment = xlCenter  '中央揃え
Range("A1").HorizontalAlignment = xlLeft　　'左揃え
Range("A1").HorizontalAlignment = xlRight   '右揃え

Range("A1").HorizontalAlignment = xlCenter '中央揃え

Range("A1").HorizontalAlignment = xlLeft　　'左揃え

Range("A1").HorizontalAlignment = xlRight '右揃え

ワークシートを作る（既存のシートの最終に）

Worksheets.Add after:=Sheets(Sheets.Count)

1	Worksheets.Add after:=Sheets(Sheets.Count)

シート名を変更する

ActiveSheet.Name = "好きな名前"

1	ActiveSheet.Name = "好きな名前"

文字を切り出す

MsgBox Mid("ABCDEF",1,3)   '答えはABC
MsgBox Mid("ABCDEF",3,2)   '答えはCD

1 2	MsgBox Mid("ABCDEF",1,3) '答えはABC MsgBox Mid("ABCDEF",3,2) '答えはCD

最終行を取得

LastRow = Range("A65535").End(xlup).Row

1	LastRow = Range("A65535").End(xlup).Row

上記はA列のケツから上に見に行ってデータがあった行を取得している。B列の方が行が多い表の場合はB65535にしてあげること。

文字列として認識する

Range("A1").NumberFormatLocal = "@"

1	Range("A1").NumberFormatLocal = "@"

CSVからExcelのシートに移すとき、コードのように先頭が０が入るもの（例えば0123）は０が消されて123と表示される。これは数値として認識されるから。これを防ぐためには、事前に文字列として認識させておく必要がある。よって代入前に上記コードを実行してから、代入するとうまくいく。

桁区切りで表示する

Range("A1").NumberFormatLocal = "#,###"

1	Range("A1").NumberFormatLocal = "#,###"

金額などを1,353,245のように、３桁で区切るようにするとき。代入前に設定しておく。

％で表示する

Range("A1").NumberFormatLocal = "0.0%"

1	Range("A1").NumberFormatLocal = "0.0%"

利益÷売りを利率として別のセルに代入するとき、％で表示したいが、そのままでは、0.12222…で表示される。これも事前に代入しておくこと。尚上記例は小数点１桁。

アラート表示を無しにする

Application.DisplayAlerts = False
'必要な処理例えば
'ActiveSheets(2).delete
Application.DisplayAlerts = True

Application.DisplayAlerts = False

'必要な処理例えば

'ActiveSheets(2).delete

Application.DisplayAlerts = True

マクロでシートを削除するとき、削除してよろしいですか？の表示はウザいので、表示しないようにする。DisplayAlerts = Falseだ。ただ、処理後は戻しておく必要があるので、末尾のTrueも忘れずに。

全シートに対して処理

'全シートに対して処理
For Each e In WorkSheets
   If e.index <> 1 then   'シート枚数が１以外は
      e.Delete　　　　　　'削除
   End If
Next e

'全シートに対して処理

For Each e In WorkSheets

If e.index <> 1 then 'シート枚数が１以外は

e.Delete　　　　　　'削除

End If

Next e

一番左のシートが原紙でそれ以外を削除したいときなど用途はさまざま。

アクティブセルに対して処理

'変数定義
Dim c as Range
Dim Target As String
'アクティブセルをセット
Target = ActiveSheet.Range(Selection,ActiveCell).Address
'アクティブセルの範囲を対象とし検索
For Each c In Range(Target)
  '例えば空白を除去
  c.Value = Replace(c.Value," ","")
  c.Value = Replace(c.Value,"　","")
Next c

'変数定義

Dim c as Range

Dim Target As String

'アクティブセルをセット

Target = ActiveSheet.Range(Selection,ActiveCell).Address

'アクティブセルの範囲を対象とし検索

For Each c In Range(Target)

'例えば空白を除去

c.Value = Replace(c.Value," ","")

c.Value = Replace(c.Value,"　","")

Next c

よくやるパターンが人間がドラッグでセルを選んで、ボタンを押すと、アクティブセルだけに対して処理を行うなど。まずTargetにアクティブセルのアドレスを取得して、ForEachでぶん回す。その際の変数cも必要。

ヘッダフッタを除去

With ActiveSheet.PageSetup
   .CenterHeader = ""
   .CenterFooter = ""
End With

With ActiveSheet.PageSetup

.CenterHeader = ""

.CenterFooter = ""

End With

先頭行を固定する

With ActiveWindow
   .FreezePanes = False
   Range("A2").Select
   .FreezePanes = True
End With

With ActiveWindow

.FreezePanes = False

Range("A2").Select

.FreezePanes = True

End With

２行目で固定する場合は、Range(“A3”)にするとよい。

印刷のとき先頭行を固定して２枚目以降も表示する

With ActiveSheet.PageSetup
   .PrintTitleRows = "$1:$1"
End With

With ActiveSheet.PageSetup

.PrintTitleRows = "$1:$1"

End With

ウインドウのスクロールを戻す

ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 1

1 2	ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollRow = 1

これで一番左上に表示が戻る。

用紙の向きを変更する

ActiveSheet.PageSetup.Orientation = xlLandscape ' 横
ActiveSheet.PageSetup.Orientation = xlPortrait '縦

1 2	ActiveSheet.PageSetup.Orientation = xlLandscape ' 横 ActiveSheet.PageSetup.Orientation = xlPortrait '縦

印刷余白を変更する

With ActiveSheet.PageSetup
    .LeftMargin = Application.InchesToPoints(0.31)
    .RightMargin = Application.InchesToPoints(0.31)
    .TopMargin = Application.InchesToPoints(0.55)
    .BottomMargin = Application.InchesToPoints(0.31)
End With

With ActiveSheet.PageSetup

.LeftMargin = Application.InchesToPoints(0.31)

.RightMargin = Application.InchesToPoints(0.31)

.TopMargin = Application.InchesToPoints(0.55)

.BottomMargin = Application.InchesToPoints(0.31)

End With

アクティブセルの値をファイル名にしてPDF保存

Dim FName As String '保存するファイル名
Dim Nam As String 'ログインユーザー名

'アクティブセルを複数選択している場合は終了
If Selection.Rows.Count <> 1 And Selection.Columns.Count <> 1 then
   MsgBox "セルを複数選択している状態では使用できません。"
   End
End If

'アクティブセルの文字を取得
Fname = ActiveCell.Value

'ログインユーザー名を取得
Nam = Environ("username")

'デスクトップにPDF保存
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\" & Nam & "\Desktop\" & FName & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

Dim FName As String '保存するファイル名

Dim Nam As String 'ログインユーザー名

'アクティブセルを複数選択している場合は終了

If Selection.Rows.Count <> 1 And Selection.Columns.Count <> 1 then

MsgBox "セルを複数選択している状態では使用できません。"

End

End If

'アクティブセルの文字を取得

Fname = ActiveCell.Value

'ログインユーザー名を取得

Nam = Environ("username")

'デスクトップにPDF保存

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"C:\Users\" & Nam & "\Desktop\" & FName & ".pdf", Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _

True

アクティブシートを複数選択しているか確認する

If ActiveWindow.SelectedSheets.Count <> 1 Then
   MsgBox "複数シートを選択しています"
End If

If ActiveWindow.SelectedSheets.Count <> 1 Then

MsgBox "複数シートを選択しています"

End If

シートが保護されているか確認する

If ActiveSheet.ProtectContents = True Then
   MsgBox = "シートが保護されています"
End If

If ActiveSheet.ProtectContents = True Then

MsgBox = "シートが保護されています"

End If

ダブルクォーテーションを文字列として判別させる

VBAでは文字列はダブルクォーテーションでくくる決まりがある。「ありがとう」という文字があるとするが、これは数値にはなり得ないし、別に”ありがとう”などとしなくてもいいのでは？と思うかもしれないが、「ありがとう」という変数かもしれない。ダブルクォーテーションを付けるのはそういう意味だ。しかし、”を文字列として認識させたい場合は、直前にダブルクォーテーションを重ねてエスケープする必要がある。

hoge = “““ありがとう”““

これは、”ありがとう”という文字をhogeに代入したことになるが、緑が上記で説明したお決まりのダブルクォーテーション。青がエスケープ文字、赤が実質の文字となる。

On Errorを解除する

On Error Goto 0

1	On Error Goto 0

利率計算で利益÷売上*100のロジックを使うが、０で割れずオーバーフローを起こすので、On Error Resume Nextでエラー回避するが、同一プロシージャ内はずっと適用されるのでは不都合がある。それをこれで解除できる。

On Errorを解除する

On Error Goto 0

1	On Error Goto 0

行を挿入する

Rows(1).Insert

1	Rows(1).Insert

これで１行目に空白行を挿入でき、もともとの行は２行目に移動する。

複数行を選択する

Rows("1:2").HorizontalAlignment = xlCenter

1	Rows("1:2").HorizontalAlignment = xlCenter

Rows(“1:2”)で、１行目から２行目を主語として指定できる。

改ページを挿入する

Rows(2).PageBreak = xlPageBreakManual

1	Rows(2).PageBreak = xlPageBreakManual

項目ごとに改ページしたいときは、PageBreakを行で指定するが、指定した行の上部で切れることになる。ようするに、指定した行から次のページになる。Rows(2)とすると、１行目と２行目以降は別のページになる。

カンマ区切りで表示する

cells(1,1).NumberFormatLocal = "#,##0_ ;[赤]-#,##0"
cells(1,1).NumberFormatLocal = "0.0_ ;[赤]-0.0"

1 2	cells(1,1).NumberFormatLocal = "#,##0_ ;[赤]-#,##0" cells(1,1).NumberFormatLocal = "0.0_ ;[赤]-0.0"

単価や金額は普通にカンマ区切りにしてマイナスは赤字で表示する。利益率などは％なのでマイナスは赤字だが、小数点１桁あたりで表示したいそのときは、NumberFormatlocalでこのように指定する。