2009年12月06日

Excel(ユーザー定義関数の制限)

今日はユーザー定義関数の制限について
少し話したいと思う
ユーザー定義関数はそもそもVBA(VisualBasic for Applications)で作成し
ワークシート関数と同じように呼び出して使えるというもので
正式にMicroSoftのExcelサポートページにも載っているほどだ
しかしユーザー定義関数には制限がある
最初から用意されているワークシート関数と同様に
Excel自体の環境を変更するようなことはできないというものだ

実際にどの程度のことが制限されているかというと以下の通りになる
・スプレッドシートでのセルの挿入、削除、または書式の設定
・別のセルの値の変更
・ブックでのシートの移動、名前の変更、削除、または追加
・計算方法や画面表示など、環境オプションの変更
・ブックへの名前の追加
・プロパティの設定およびほとんどのメソッドの実行


サポートページ内ではこれらの制限がうたってあり
ユーザー定義関数の目的と作成後の機能を次のように定めている
・Excel に付属の関数に含まれていないユーザー設定の関数を作成すること
・Excel に含まれている関数でも環境は変更できない
・関数で実行できるのは入力元のセルに値またはテキストを返す計算
・環境の変更はVBAのサブルーチンを使用して行う必要がある


「なんのこっちゃ?」と・・・ちんぷんかんぷんな人もいるだろうが
これらをまとめると
作成した関数の扱いはワークシート関数に準じるということだろう
そしてVBAを利用して作成するといっても
ワークシート関数と同様の使用法となるということだ

これからユーザー定義関数の作成を覚えたいというのであれば
勉強するうちに嫌というほど制限の壁にぶちあたることだろう
その結果ユーザー定義関数とあわせてVBAでいろいろと制御することを
覚えていくことになるかもしれない

  


Posted by タカさん at 23:18Comments(0)ユーザー定義関数

2009年11月29日

ExcelVBA(変数の定義)

ExcelVBA(マクロ)とユーザー定義関数作成で
必ず覚えなければならないコードの記述として
変数の定義がある
難しいことを言うと覚えれなくなるので簡単に説明すると
変数とは文字や数値をいれておく箱のようなものと
今のところは覚えておいてもらって結構

VBA内でのコードの記述は次のようになる
Dim 変数名 as 変数の型

たとえば計算の答えをいれておく「Answer」という
変数を「Integer型」で用意するのであれば

Dim Answer as Integer
という記述になり

小数点を伴う答えをいれておくのであれば

Dim Answer as Single
または
Dim Answer as Double
という記述になる

別に覚えなくてもいいけど
DimはDimensionの略で「配列」などの意味をもつ
配列とは何かというのはそのうち必要に応じて説明するけど
変数を使いこなすのであれば
そのうち「配列」も使うようになるんで
頭の片隅にでもおいといてほしい
  


Posted by タカさん at 08:59Comments(0)ユーザー定義関数

2009年11月26日

ExcelVBA(データ型)

Excelでユーザー定義関数を作成していく上で
知っていたほうがいいデータ型について簡潔に記したいと思う
詳細な説明はその都度必要な時に行うので
今回は必要最小限知っていたほうがよいものを列挙する
昨日説明した「Integer型・Long型」についても
重複するがもう一度説明する

数値を扱う型
通常は値の正確差とパフォーマンスに優れる
「Integer型」を使用することが推奨される

[Byte バイト型]
「0 ~ 255」の数値を扱える

[Short 短整数型]
「-32768 ~ 32767」の数値を扱える

[Integer 整数型]
「-2147483648 ~ 2147483647」の数値を扱える

[Long 長整数型]
「-9223372036854775808 ~ 9223372036854775807」の数値を扱える

[Single 単精度浮動小数点型]
説明をすると長くなるので簡潔に説明すると
通常の小数点を伴う数値を扱い計算をするのであれば
この型を使用すれば問題なし
ただし正確な値を得れない場合がある

[Double 倍精度浮動小数点型]
「Single型」では計算できないような精度を必要とする
小数点を伴う数値を扱うのであればこちらを使用する
ただし正確な値を得れない場合がある

[Decimal 10進型]
整数部と小数部をあわせて約29桁までの数値を扱うことができ
「Single型・Double型」と違い正確な値を得ることができるので
細かい誤差を許容できない正確な計算が必要な場合に使用する


他の型(文字列・日付等)

[String 文字列型]
「0 ~ 20 億くらいの文字を扱うことができる

[Char 文字型]
「1文字(Unicode)」を数値として扱える
数値に変換すると「0 ~ 65535」で文字を表すことができる

[Date 日付型]
「0001年1月1日0:00:00 ~ 9999年12月31 日 23:59:59」までの
日付に対応し扱うことができる

[Boolean ブール型]
真または偽を値として扱う
値:「True」 または 「False」を値として扱う

[Object オブジェクト型]
任意の型を格納し扱うことができる

以上いろいろな型があるが
必要に応じ使い分ける必要もでてくるので
これからVBAのコードを載せていく時に
分からない場合はこれらを参照またはネット上で検索してほしい

  


Posted by タカさん at 16:59Comments(0)ユーザー定義関数

2009年11月25日

Excel(ユーザー定義関数・基本型)

前回はあまり詳細な作成方法を載せていないが
今回は実際に関数を作成する方法をやさしく残しておくことにする
Excelのマクロを作成したことがある人なら
どうということはないが
そうでない人も簡単なものを作成できると
作業効率がUPしたりするので覚えておいて損はないっす

[ユーザー定義関数の作成・Excel2007以前]
1.ツールバーから[ツール]→[マクロ]→[Visual Basic Editor]を実行

2.Projectエクスプローラの部分を右クリックして標準モジュールを挿入

3.挿入した標準モジュールを開きコードを記述する

[ユーザー定義関数の作成・Excel2007]
1.左上のOfficeボタンから[Excelのオプション]→
 →[開発タブをリボンに表示する]にチェックしOK

2.タブを[開発]にあわせて一番左の[Visual Basic]をクリック

3.Projectエクスプローラの部分を右クリックして標準モジュールを挿入

3.挿入した標準モジュールを開きコードを記述する

[ユーザー定義関数の基本構文]
下記のような構文になる
Function 関数名(引数1 As データ型) As データ型
  関数名 = 計算式
End Function


引数はカンマで区切ることで複数指定することもできる
Function 関数名(引数1 As データ型,引数2 As データ型) As データ型
  関数名 = 計算式
End Function
といった具合になる

[基本構文に基づいた関数の作成]
では実際のコードはどのようなものになるのか
簡単なものを作成してみることにする
馴染みの深い所で消費税5%を含んだ値を返す関数を作成してみる

Function ZEIKIN(Rng as Range)as Integer
  ZEIKIN = Rng * 1.05
End Function

[関数の説明]
まず最初の1行目
Function ZEIKIN(Rng as Range)as Integer

「ZEIKIN」という名前の関数を定義しているのがわかると思うが
その後に続く部分を詳しく説明したいと思う

「Rng as Range」という文字が見えるが
「Range」というのはVBAの中でセルをあらわす時に使用する関数になり
関数を使用した時にExcelのセルの値を「Rng」という箱に格納しなさい
という意味になる

続いて「Integer」という文字が見えるが
これは関数「ZEIKIN」を使用して扱う値のデータ型は「Integer型」ですよ
ということをあらわしている

「Integer型」とは何か?
数値には整数や小数などいろいろな型があるのはご存知かと思うが
「Integer型」は整数型で「-2147483648 ~ 2147483647」の数値を扱うことができる
これではたりないよという人には「Long型」というものがあり
「-9223372036854775808 ~ 9223372036854775807」の数値を扱うことができる

続いて2行目
ZEIKIN = Rng * 1.05

「Rng」に引数の値が格納されているのだから
それに「1.05」を掛けることで元の値に5%の消費税が加算された値を計算し
関数「ZEIKIN」の結果として返してやるという動作だ

最後の行の「End Function」は
ここまでで関数の定義が終わりですよという意味になり
最初の「Function」からはじまって「End Function」で終わるのが
ユーザー定義関数を作成する時のお約束となっている

[関数を使用する]
ではさっそく作成した関数を使用してみよう

Excelでいつも使用する関数と同じように使用でき
「A1」のセルに入力されている「1000」を引数に指定することで
「B1」のセルに「1050」という答えが返ってきた

このように簡単なものから複雑なものまで
作りこんでいくといろいろなユーザー定義関数を作成することができ
作業効率がUPしたりするので
ユーザー定義関数の作成にぜひ挑戦してみてほしい  


Posted by タカさん at 18:10Comments(0)ユーザー定義関数

2009年11月24日

文章を半角カタカナに変換する関数

某質問サイトへ答えた内容だが
文章をすべて半角カタカナに変換するというユーザー定義関数で
こちらで手を加えずに「GetPhonetic」で変換すると
100文字以内という制限が働くので
そこらへんに手を加えて100文字以上に対応させたものを残しておくことにする

[ユーザー定義関数の作成]

1.ツールバーから[ツール]→[マクロ]→[Visual Basic Editor]を実行

2.Projectエクスプローラの部分を右クリックして標準モジュールを挿入

3.挿入した標準モジュールを開き以下のコードを記述

Function KanaConv(strText As String) As String
Dim strLen As Integer
Dim strTexts(100) As String 'よくわからないので1万文字まで
Dim i As Integer
Dim strTmp As String

strLen = (Len(strText) / 100)

For i = 0 To strLen
strTexts(i) = Application.GetPhonetic(Mid(strText, (i * 100) + 1, 100))
strTmp = strTmp & StrConv(strTexts(i), vbNarrow)
Next i
KanaConv = strTmp
End Function


あとはセル部分へ普通にExcel関数と同様の使用ができるようになる
たとえばA1にある文字をA2へ半角カタカナで表示するようにするなら
A2のセルへ次のように使用する

=KanaConv(A1)

テスト環境はExcel2007だがExcel2002以降ならおそらく動くと思われる
Excel2000は保障なし・・・  


Posted by タカさん at 06:27Comments(0)ユーザー定義関数