【VBA】【関数】効率化のためのメモ

全般的なこと

ボタン

リボン＞開発を表示する
挿入＞選ぶ
マクロ名選択
移動とか編集とかするときは、開発＞デザインモード

VBEditor ショートカット

VBEditorを開く：Alt + F11
ヒント表示：Ctrl + I
入力補完：Ctrl + Space
変数一覧(ローカルウィンドウ)：Alt → V → S
エディタ設定： Alt → T → O
　キーワードをピンクにするのがオススメ。
実行：F5
一行ずつ：F8
ブレークポイント：F9

具体的なコード

インプットボックス

Application.InputBox(Prompt:="必要な列数は？(1～8)", Type:=1)
Type:=1は数値。2は文字列。4はTrue/False

列の非表示

Range(Columns(Honsu + 3), Columns("J")).Hidden = True
+3は、1列目(D)までの固定列

一列目・一番下のデータの次のセル

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

選択範囲の可変値(=関数以外)を削除する

Sub 可変値削除()
Dim ra As Range
Range("C3", "J53").Select  '対象範囲を選択。範囲が増えたらここも増やす必要あり
For Each ra In Selection
    If ra.HasFormula Then
        ra.Interior.Color = RGB(220, 220, 220) '関数だったら灰色にする
    Else
        ra.ClearContents '関数じゃなかったら値を消去する
    End If
Next
Cells(3, 3).Select
End Sub

```vb
この中にVBAのマクロを書くと、上のようにスタイリッシュになるなんて知らなかった。
```

関数

横方向に並んだデータを、縦方向に並べ替える

=OFFSET($A$1,0,ROW()-2)
元データは、A1、B1、C1…と並んでいて、それをA2、A3、A4…に転記する場合の例。
末尾の2は、並べ替え先の始点となるセル(ここではA2)の行番号。始点セルだと2-2=0になり、A1の値を返す。並べ替え先の次のセルでは、ROWが1つ増えて3-2=1でOFFSETの列指定が1つ増え、B1の値を返す。
元データの始点を絶対参照にすることで、A2の関数をA3、A4…にコピーしても動作する。

縦方向に並んだデータを、横方向に並べ替える

=OFFSET($A$1,COLUMN()-2,0)
縦横(行・列)を逆にしたもの。OFFSETの行指定のところに、列＝COLUMNを参照した値を入れることで、列の移動を行の移動に置き換えることができる。

関数の置換

A1セルに「=元データ!\$C$1」という関数が入っていて、それを上記の関数に置換したい時。
="=OFFSET("&SUBSTITUTE(FORMULATEXT(A1),"=","")&",0,ROW()-2)"
FORMULATEXT関数で、関数の結果ではなく関数自体を参照できる。
あとはコピーして値貼り付けして、F2→Enterを繰り返す