VBA フィルタを操作する

複数の人が操作（読み書き）をするようなファイルをVBAを使うとフィルタがかけられて思わぬエラーに遭遇したり書き込み消し込みが正常に機能しなかったり不具合が発生することがある。

一番シンプルで簡単なものはVBA操作の最初に3行を記入しておけば不具合は回避できる。

If ActiveSheet.FilterMode = True Then

         ActiveSheet.ShowAllData

End If

FilterModeはオートフィルタが絞り込まれているかどうか絞り込まれていればTrueを

絞り込まれていなければFalseが返されるので絞り込まれているときは

ShowAllDataで絞り込み解除するやり方である。

AutoFilterModeでオートフィルタが表示されているかどうかを判定したあとの処理でもよいがオートフィルタが表示されていない場合は上記の構文で判定はFalseが返されるのでオートフィルタの表示の有無にかかわらずこの3行で対処は可能である。

※オートフィルタを解除してVBA作業をしてあとでオートフィルタを表示を戻すやり方も考えられるが構文が複雑になり労多くして効果薄なので通常作業では避けたほうが無難である。

※すべてデータをクリアにしたい場合など絞り込みがされた状態では範囲を指定してクリアすると絞り込まれた箇所はクリアされないのでセルごとにクリアする手段もあるが非常に時間がかかり効率的でない。

VBA Modを使う（割り算の余りを求める）

１０進法で作業が進めにくい場合もあります。意外と使い道があるので覚えてください。

まず、Ｅｘｃｅｌでは　

答え「1」のところの数式は、

=MOD(A2,B2)　　　＜Ｒ１Ｃ１参照形式：=MOD(RC[-2],RC[-1])　＞

割られる数	割る数	余り
10	3	1

 ＶＢＡでは下のように記述します。

Sub Macro931()

Cells(2, 3) = Cells(2, 1) Mod Cells(2, 2)

End Sub

Ａ１参照形式で記述すると下のようになります。

Range("C2") = Range("A2") Mod Range("B2")

Ｅｘｃｅｌの数式とちょっと違うので気をつけてください。

セルに計算式を入れるなら

Sub Macro932()

Cells(2, 3) = "=MOD(RC[-2],RC[-1])"

End Sub

高校数学では＜定義:合同式＞

2つの整数a,bを自然数nで割った余りが等しいとき a≡b（mod n) と表しaとbはnを法として合同であるという。

a≡b (mod n) , c≡d (mod n)

→

a+c≡b+d (mod n)

a-c≡b-d (mod n)

ac≡bd (mod n)

a^p≡b^p (mod n)

問題1

 を7で割った余りを求めよ。

問題2

の下２桁を求めよ。(2018昭和大学　医）

VBA R1C1参照形式からA1参照形式に変換

Excelは通常、A1参照形式で扱うことが多いがそれ以外にA列、B列ではなく列表示も数字のR1C1参照形式がある。

昭和世代にはロータス形式、マルチプラン形式のほうが馴染みやすいが今では通じない死語かも知れない。

VBAでは変数を扱う場合、数字のほうが変数処理をしやすいのでR1C1形式を使うことが多いがA1形式に変換したい場合がある。

例えば　53列をA1形式に変換に変換するには

Cells(1,1)= Cells(1,53).Address

$BA$1

知りたいのは「BA」なので$や1はいらない。

「BA」のみを抽出する方法は

Dim n As String
n = Cells(1, 53).Address(True, False)
Cells(1, 1) = Left(n, (InStr(n, "$") - 1))　　①
Cells(2, 1) = Left(n, (Len(n) - 2))　　　　 ②

BA

BA

または

Dim n As String
n = Cells(1, 53).Address(False, False)
Cells(1, 1) = Left(n, (Len(n) - 1))

BA

※　VBAの処理で行も可変の場合は①を使う必要がある。

Addressプロパティは

 expression.Address (RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

RowAbsolute・・行の部分True:絶対参照、False:相対参照

ColumnAbsolute・・列の部分True:絶対参照、False:相対参照

ReferenceStyle・・既定値は xlA1 

External・・既定値は False 

RelativeTo・・RowAbsolute、ColumnAbsolute両方Falseの場合でReferenceStyle が xlR1C1 である場合は、相対参照の開始点を含める必要がある

必要になる場面に遭遇することはあまりないがA1参照形式で処理した場合は使える。

VBA 空白（””）を含む数式を入れるときの処理

計算式が無効にならないように空白を考慮した計算式例

	1	2	3	4
1	出身県	人数	年俸	平均年俸（万円）
2	岐阜	1	1,800	=IF(RC[-2]<>"",RC[-1]/RC[-2],"")
3	静岡	4	32,770	=IF(RC[-2]<>"",RC[-1]/RC[-2],"")
4	愛知			=IF(RC[-2]<>"",RC[-1]/RC[-2],"")
5	三重	1	550	=IF(RC[-2]<>"",RC[-1]/RC[-2],"")
6	合計	=SUM(R	=SUM(R	=IF(RC[-2]<>"",RC[-1]/RC[-2],"")

	1	2	3	4
1	出身県	人数	年俸合計（万円）	平均年俸（万円）
2	岐阜	1	1,800	1,800
3	静岡	4	32,770	8,193
4	愛知
5	三重	1	550	550
6	合計	6	35,120	5,853

この空白を考慮した計算式をVBAでそのまま記述し実行すると

Cells(2, 4) = "=IF(RC[-2]<>"",RC[-1]/RC[-2],"")"

エラーとなってしまいます。

VBA記述で文字列を括る（””）と空白（””）が判別できないからです。

計算式内での（””）とVBA記述での（””）を区別させるために計算式を以下のように分割します。

①=IF(RC[-2]<>

②””

③,RC[-1]/RC[-2],

④””

⑤)

さらに計算式内での（”）を認識させるために（”）を（””）にします。

ですから空白（””）は（””””）でやっと認識しくれるというわけです。

そして

”①” & "②" & "③" & "④" & "⑤"してしまえばいいわけです。

Cells(2, 4) = "=IF(RC[-2]<>" & """""" & ",RC[-1]/RC[-2]," & """""" & ")"

例えば

=IF(RC[-2]<>"",RC[-1]/RC[-2],"対象外")　の場合

Cells(2, 4) = "=IF(RC[-2]<>" & """""" & ",RC[-1]/RC[-2]," & """対象外""" & ")"

VBA DateAdd関数

VBA DateAdd関数

指定した時間間隔を日付に加算してその日付を含む Variant (Date) を返します。

使用例

・販売予測と在庫数から在庫がなくなる日を予測します。

・在庫が無くなる予測日から補充発注をする日を割り出します。

DateAdd(interval, number, date)

DateAdd( 加減算する単位、加減算する数値、元になる日時 )

例：DateAdd("m", 1, Date)

interval引数の設定

設定　　説明

"yyyy" 年

"q" 四半期

"m" 月

"y" 通年での日数

"d" 日

"w" 平日

"ww" 週

"h" 時

"n" 分

"s" 秒

"y"、"d"、"w"は同じ値が返ります。

VBA データベースの最終行を調べる

Excel VBAを使ってデータベースを集計するとき、レコードの最終行を知る必要がある。

私が最初に覚えたのがCurrentRegionプロパティで、例えば

GYO = Sheets("Data").Cells(1, 1).CurrentRegion.Rows.Count

Dataシートの１行目からデータが入っているものに有効な最終行調査である。

ところが、データベースを作る人によっては視覚的に見やすくするため空白行を挿入していたり、下記のように表題、凡例が説明されているようなものでは不都合がおきる。

	A	B	C
1	統計名：	基礎データ
2	凡例	***	未調査
3		-	データなし
4		X	非公表
5
6	地域	教育	幼稚園数
7	北海道		422
8	青森県		88
9	岩手県		97
10	宮城県		242
11	秋田県		40
12	山形県		74
13	福島県		264
14	茨城県		268
15	栃木県		100
16	群馬県		155
17	埼玉県		544
18	千葉県		510

gyo1 = Sheets("Data").Cells(1, 1).CurrentRegion.Rows.CountSub Macro4()

gyo2 = Sheets("Data").Cells(6, 1).CurrentRegion.Rows.Count

gyo3 = Sheets("Data").Cells(6, 2).CurrentRegion.Rows.Count

gyo4 = Sheets("Data").Range("A65536").End(xlUp).Row

gyo5 = Sheets("Data").Range("B65536").End(xlUp).Row

gyo6 = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row

        gyo6 = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row

gyo7 = Sheets("Data").Cells(Rows.Count, 2).End(xlUp).Row

       gyo7 = Sheets("Data").Cells(Rows.Count, "B").End(xlUp).Row

End Sub

	実行結果
gyo1	4	表題案例部分をDBと判断
gyo2	13	データ部分の行数 6行～18行
gyo3	13
gyo4	18	A列の最終行（※）
gyo5	6	B列の最終行（※）
gyo6	18	A列の最終行
gyo7	6	B列の最終行

（※）Excel2003まではExcelの行数は65,536行であったがExcel2007以降では
1,048,576行まで拡張しているため65,536行を超えるようなデータの場合は使えない（65536行がデータ内のためデータの最上行を表示）。

データベースの構成、レコード（行）フィールド（列）などを見極めて使う必要がある。

＜参考＞6行目がデータのKey列の場合の最終列を調べる

retu1 = Sheets("Data").Cells(6, Columns.Count).End(xlToLeft).Column

VBA セルを色塗りする

データの内容に応じてセルの色を変えたい場合、VBAで処理をしなくてもExcelの「条件付き書式」で設定をしておけばいい。とは言えExcelデータで作業をしていると「条件付き書式」ではないほうがいいこともある。そんなときに活用したい。

例えば、VBAでデータ加工をして特に注意をはらってほしいデータがあるとする。そこに色塗りをしておき詳細なチェックを手作業で行い問題がなければ色塗りを消したい場合などに活用できる。

Sub Macro930()
Dim n As Integer
Dim m As Integer
Dim p As Integer

      For n = 0 To 56
           m = Int(n / 10) + 1
            p = n Mod 10 + 1
                   Cells(p, m).Interior.ColorIndex = n
      Next n
End Sub