More than 5 years have passed since last update.

VBA Accessを始めるまえに簡単にパパッと作る関数xlRnd2にさらにLOG10とStaticと変数の宣言

Last updated at 2018-07-17Posted at 2018-07-05

#基本 AccessのRoundはExcel.WorksheetFunction.Roundとは違う
Accessを始めるまえに簡単にパパッと作る関数xlRnd（おまけ付き）
ここでも述べた通り、ExcelのWorksheetFunctionのRoundとAccess(とExcel）のVBAのRoundは別物です。Excelはいままでセル内で処理していたので気づかないのですが（すいません、いままで知りませんでした。
そこでExcelのWorksheetfunctionのRoundのように正負の桁数指定が扱えるように改造し、Excelの参照設定が不要なものを開発しました。

コード

xlRnd2

'必ずモジュールの最初に記述し、この関数を使うものはすべて同じプロシージャ内に記述すること
Static Function Log10(X) As Double
'For Access VBA
Log10 = Log(X) / Log(10#)
End Function
Function xlRnd2(X, intflo As Integer)
Dim xDegit As Integer

' IsNumeric Check
If X * 0 <> 0 Then GoTo ERR_Hndl
On Error GoTo ERR_Hndl
  If intflo < 0 Then
  If CInt(Log10(Abs(X))) + 1 <= Abs(intflo) Then
    xlRnd2 = X
    Exit Function
  Else
    xlRnd2 = Int((Abs(X) * 10 ^ (intflo)) + 0.5) / (10 ^ intflo) * Sgn(X)
    Exit Function
  End If
Else
  xlRnd2 = Int((Abs(X) * 10 ^ (intflo)) + 0.5) / (10 ^ intflo) * Sgn(X)
  Exit Function
End If
Exit Function
ERR_Hndl:
xlRnd2 = 0
End Function

解説

Static Function Log10(X)

Log

Log関数
指定した数値の自然対数を、倍精度浮動小数点数型 (Double) で返します。
###構文
Log( number )

number引数。必須。0を超えるDouble倍精度の数または有効な数値式です。

###数学的解説
自然対数は、e を底とする対数です。定数e は約 2.718282 です。
任意の数値 x の base-n 対数を計算するには、次のように x の自然対数を n の自然対数で割ります。
Logn(x) = Log(x) / Log(n)
次の例は、base-10 対数を計算するカスタム関数を示します。
Static Function Log10(X)
Log10 = Log(X) / Log(10#)
End Function

Log(10#)の#ナンバー記号

####正体はリテラルこれをつけることで変数または数字の型または文字型を示す

3-1-3 VB .NET でのデータ型
すでに触れたように、変数宣言では「As データ型」のように、As キーワードの後ろには、その変数のデータ型がきます。前項の例では、「As Integer」と指定したことによって、4 バイトの整数である Integer 型のデータ領域が変数として確保されました。ここでは、改めて Visual Basic .NET にはどのようなデータ型が用意されているか説明します。
Visual Basic .NET には、.NET Framework の CTS（Common Type System）に基づいた、以下のデータ型が用意されています（表 3-1）。
Byte 数値整数符号なし 1 バイト 0 〜 255
Short 符号あり 2 バイト −32768 〜 32767
Integer 符号あり 4 バイト −231 〜 231−1
Long 符号あり 8 バイト −263 〜 263−1
Single 浮動小数点 IEEE754 準拠 4 バイト ±1.5−45 〜 ±3.438
Double IEEE754 準拠 8 バイト ±5.0−324 〜 ±1.7308
Decimal 10 進数データ型 ±1.0−28 〜 ±7.928
Char 文字 Unicode文字（1 文字、2 バイト）常に 1 文字
String Unicode 文字列 0 文字以上の可変長
Boolean その他論理型値は True か False
Date 8 バイト長の日付/時間データ西暦 1 年〜 9999 年
Object 汎用データ型

文字の扱い Char

Char 型は、1 文字データを扱うデータ型です。このデータは Unicode 文字であり、みた目が全角・半角に限らず、常に 2 バイト長です。このデータを表記するときは、"A"C のようにダブルコーテーションで囲った文字の後ろに、C を加えます。

文字の扱い String

String 型は、可変長の文字列を扱うときのデータ型で、これも Unicode 文字列です。このデータを表記するときは、単純にダブルコーテーション（" "）を使います。
この文字列データも .NET Framework の Common Type System に準拠したものですが、.NET Framework では、実行時の文字列操作は Unicode として扱います。ただし、プログラムのソースコードは、その OS のネイティブな文字セットを使うことができます（日本語 Windows 環境では、ShiftJIS）。例えば、以下のようなプログラムも、日本語 Windows 環境では、既定では ShiftJIS の文字セットとして保存されます。このソースプログラムを VB .NET コンパイラでコンパイルすると、文字列「"Hello,world!"」は、Unicode 文字列に変換されます。また、メモ帳などで Unicode としてソースプログラムを保存し、それをコンパイルすることもできます。

#####VB6と.NETの違いと固定長文字列の宣言方法

Visual Basic .NET のデータ型は、.NET Framework に基づくので、整数型以外にも、Visual Basic 6.0 といくつか異なる点があります。VB6 にあった Variant 型は、VB .NET にはありません。その代替が、Object 型にあたります。通貨の扱いについては、VB6 の Currency 型にあたるのが、Decimal 型になります。また、以下のような VB6 の固定長文字列については、VB .NET ではサポートしません。
Dim S As String * 128
※この宣言方法はVBA、マクロでは有効

#####3-1-4 VB .NET でのリテラル表記

変数は、任意の値を代入でき、文字通り値が変化するデータですが、それ以外に、計算式では数値などの固定的なデータが直接コードに登場します。このようなデータを「リテラル」といいます。以下の例では、数字の 2 や文字列 "abc" はリテラルです。リテラルも、それ自身のデータ型によって表記が異なります。
Byte 通常の 10 進数整数表記が可能
先頭に &H をつけて 16 進数表記
先頭に &O をつけて 8 進数表記 — —
Short 末尾に S 1000S
Integer 末尾に I か % 30000I 30000%
Long 末尾に L か & 50000L 50000&
Single 明示的に小数点をつけて表記末尾に F か ! 50.3F 107.5!
3.14E+50F
Double 末尾に R か # 317.5R 385.2#
3.15E−21R
Decimal — 末尾に D か @ 115.71D 2980@
Char 必ず二重引用符で囲む
（そのまま書くと変数名になってしまう）末尾には C AC
String — ABC
Boolean 真偽を表す二種類の値のみ — True Flase
Date 必ず # で囲む — #01/22/2001#

#####リテラルにつけられた接尾辞で変数にも使えるものがある

リテラルにつけられた接尾辞のうち、「%」、「&」、「!」、「#」、「@」はリテラルだけでなく、変数につけることもできます。この接尾辞をつけることで、その変数がどんなデータ型かを表すことができます。

#####~~実はVBAでも十進型宣言できる~~VBAでは@は通貨型になる
この@で十進型を表すのはVBAでもエラーになりません。数字でも変数でも使えます。しかし小数点4桁を伴う通貨型になります。固定小数点型十進なのです。
つまりVBAでもCDec以外に十進型を表す方法~~があったのです~~はないのですが、通貨型をDecimalの代わりにすることが推奨されているのでこれを用います。なお利率、利息などにおいて、1より小さく小数点が5桁から小数点6桁までの数は単精度浮動小数点型(Single)を使うと4バイト程度で精度が高くなります。VBAの仕様では7桁程度としていますが、実務上6桁をよく使いますのでそう覚えておきましょう。小数点4桁以下であれば常に通貨型を用いることができます。

#####ただしリテラルで型を指定するのは遅いみたい
未検証ですが、型を宣言した場合より遅いようです。

Static

Static ステートメント

　変数を宣言して、記憶域を割り当てるために、プロシージャレベルで使用されます。 Static ステートメントで宣言された変数は、コードが実行されている間はその値を保持します。
Staticvarname [ ( [ subscripts ] ) ] [ As [ New ] type ] [ ,varname [ ( [ subscripts ] ) ] [ As [ New ] type ]] . . .
####解説
モジュールのコードが実行されると、 Staticステートメントで宣言された変数は、モジュールがリセットまたは再実行されるまで、その値を保持します。 class モジュールでは、 Static ステートメントで宣言された変数が、インスタンスが破棄されるまで、各クラスのインスタンスの値を保持します。 form モジュールの静的変数は、フォームが閉じられるまで、その値を保持します。プロシージャ内だけで表示される変数を明示的に宣言するには、静的でないプロシージャの中で Static ステートメントを使用します。ただしこの変数の存続期間は、プロシージャが定義されているモジュールと同じになります。

####メモ

Static ステートメントと Static キーワードは似ていますが、得られる効果は違います。( Static Sub CountSales () のように) Static キーワードを使用してプロシージャを宣言すると、プロシージャ内のローカル変数すべての記憶域は一度だけ割り当てられ、変数の値はプログラムを実行している間はずっと保持されます。静的でないプロシージャの場合、変数の記憶域は、プロシージャが呼び出されるたびに割り当てられ、プロシージャが終了するときに解放されます。 Static ステートメントは、静的でないプロシージャの中で特定の変数を宣言するときに使用され、プログラムの実行中は変数の値を保持します。

###Function StatementのOption Static
https://msdn.microsoft.com/ja-jp/vba/language-reference-vba/articles/function-statement
オプション。プロシージャ内で Function プロシージャのローカル変数が保持されることを示します。 Static 属性は、 Function 外で宣言された変数には、たとえその変数がプロシージャで使用されていても影響を及ぼしません。

Static Function Log10(X) まとめ

Static は関数のオプション。モジュール内のプロシージャの実行中は少なくとも値が保持される。
Staticにはこのほかにキーワード(変数の修飾子)としてのStaticがあり、働きが異なる。
10#は十進数の10であることを意味するリテラル文字が付加されている
あとでこれは独自に取り上げたいがVBAは実は十進型がCdec以外で矯正できる
ただしリテラルの付加は遅い。
対数の公式でLog10を導いている
なのでMicrosoftは今後ともAccessでLog10を使えるようにする気はないと思われる。
もともとこの表記方法は、Visual Basic 以前の、まだ GUI 環境がなかったころの Basic 言語にその起源がある。
変数の型宣言がなかったので、D2# と表記することで、その変数が Double 型であることを表していた。
公式と異なり As Doubleとしているのは返り値を明示的に型宣言すると早いときいたことがあるため
このLog10は数字が何桁か、整数位の桁数を求めるために使用する。
整数位の桁数を求めるのはxlRnd2がExcel.Worksheetfunction.Roundと同じ動きにするため

##xlRnd2

xlRnd2

Function xlRnd2(X, intflo As Integer)
Dim xDegit As Integer

' IsNumeric Check
If X * 0 <> 0 Then GoTo ERR_Hndl
On Error GoTo ERR_Hndl
  If intflo < 0 Then
  If CInt(Log10(Abs(X))) + 1 <= Abs(intflo) Then
  xlRnd2 = X
  Exit Function
  Else
  xlRnd2 = Int((Abs(X) * 10 ^ (intflo)) + 0.5) / (10 ^ intflo) * Sgn(X)
  Exit Function
End If
  Else
  xlRnd2 = Int((Abs(X) * 10 ^ (intflo)) + 0.5) / (10 ^ intflo) * Sgn(X)
  Exit Function
End If
Exit Function
ERR_Hndl:
xlRnd2 = 0
End Function

intflo As Integerで高速化

　正負で四捨五入できるのは15桁が常識的な限界。そうしないとExcelでセルに入れるとおかしくなる。
　これにあう変数はIntegerで４バイトしか使わないのでこれを採用。ShortはVBAでは使えない。

IsNumericっぽいエラーチェック

数であれば0を掛ければどんな数でも0になるので、変数に0を掛けて、0にならなければエラー。

四捨五入自体はありふれたもの

小数点1位まで10の倍数をかけ、0.5を足して割り戻す

###マイナスの場合にも対応
これは小数点1位になるまで10で割り、0.5を足して割り戻す。

Xがマイナスの場合にもSgn関数で対応

絶対値で上記のことを行ってからSgn関数で元の符号をつける

###マイナスの場合はエラーチェックが必要
1555の5桁目は存在しない。そういう場合はXをそのまま返す。
このためintfloがマイナスの時は桁数を超えないかをチェックする。

###Log10は桁数を求める関数
Log10(100)=2
Log10(1000)=Log10(10^3)=3
つまり100から999は2以上3未満である。
この整数位に1を足すと桁数が求められる。

xlRnd2を使うと上位3桁で四捨五入ができる

たとえば
-1,545,000 を上位三桁で端数整理すると -1,550,000
7桁の数ならxlRnd2( X, -4)となる
これは 2 - log10(x) = -4となる
この関係は実は絶対値が1000以上なら常に成立する。
よって
xlRnd2(-1545000,2-int(log10(ABS(-1545000))))

xlRnd2(X,2--int(log10(ABS(x))))

とするとよい。また上位4桁は3-にするとよい。
###xlRnd2まとめ

Log10を使うとマイナスに対応できる
桁数指定はInteger宣言で高速化
変数xはIsnumericっぽいアルゴリズムでエラーチェック
マイナスの場合はエラーチェックが必要
これでexcel.worksheetfunction.Roundを模倣
ただし丸める数の整数位+小数位+絶対値(端数整理する桁数)が15桁程度が上限と想定。丸める数の整数位+小数位+絶対値(端数整理する桁数)が15以下にする方が安全。Excelのワークシートに落とすと15桁が限界のため。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up