エクセルVBA ワークシート関数の使用について

質問

エクセル2003VBA　ワークシート関数の使用についての質問です
よろしくお願いします。

４桁の数字を、千の位・百の位・十の位・一の位に分解する関数ですが
例えば
A1に４桁の数字があり、B1に千の位を表示する場合ですが
ワークシート関数の場合
B1=MOD(INT($A$1/1000),10)　となりますが
VBAにてそのまま使用し
Range("B1").value=Application.WorksheetFunction.MOD(INT(Range("A1")/1000),10)とするとエラーになります。
エラーになる原因は、いろいろ調べたら分かったのですが
この様に、一発でB1に計算結果を入れたいのですが、どうしたらよろしいでしょうか

現在は、
Range("C1").Formula = "=MOD(INT(A1/1000),10)"
Range("B1").value=Range("C1").value
一旦、C1に置き換えてから行っています。
よろしくお願いします

回答No.5

Wendy02 · ベストアンサー

こんにちは。

確かに、他の方たちのおっしゃるように、VBA に、MOD関数はありませんね。MOD 演算子です。

Range("B1").Value = INT(Range("A1").Value/1000) Mod 10

という形には、一応はなるのですが、正数のみの扱いです。小数点を含む数字も負数も使えません。

ワークシートのMOD関数が使えないわけではないのですが、通常は、以下のような方法はつかいません。ただし、MOD関数のほうが優れている部分があります。

ワークシート関数を使う場合：

Range("B1").Value = Evaluate("MOD(INT(" & Range("A1").Value & "/ 1000), 10)")

---------------------------------

Sub Test3()
'整数のみ
Dim num As Variant
num = Range("A1").Value * Sgn(Range("A1").Value)
Range("B1").Value = Int(num / 1000) Mod 10
End Sub
-----------------------------------
Sub Test4()
'整数のみ
　　Dim num As Variant
　　Dim i As Integer
　　Dim j As Integer
　　Const K As Integer = 4 '千の位から以下

　　num = Range("A1").Value * Sgn(Range("A1").Value)
　　num = CStr(num)
　　j = Len(num):　 n = j - K
　　If n < 1 Then Exit Sub
　　For i = 1 To j
　　　　'i+1 ...2列目
　　　　Cells(1, i + 1).Value = Mid(num, i + n, 1)
　　Next i
End Sub
--------------------------------

お礼

ご回答ありがとうございました
大変参考になりました

回答No.6

Wendy02

#5 の修正
Sub Test4() は、エラーは出ませんから、以下のようにしたほうがよいです。

×　For i =1 To j
　　↓
For i = 1 To n + 1

お礼

ご回答ありがとうございました
大変参考になりました

回答No.4

KonnaMonde

No.3　です。
Sub test2()　の４行目の　n = 1　は不要です。
ほかのコードで書いていたのを変更したとき消し忘れました。

短いコードを書くのなら、すこし解りにくいのですが次のようになります。

Sub test3()
　　Dim n
　　For n = 1 To 4
　　　　Cells(1, n + 1).Value = Mid(Cells(1,1).Value, n, 1)
　　Next
End Sub

です。

お礼

ご回答ありがとうございました

回答No.3

KonnaMonde

まず、VBA関数、ワークシート関数、ヘルプについて。

エクセルのヘルプには２通り(正確には３通り)あって、ワークシート関数についてはエクセルのシートのヘルプを、VBA関数についてはＶＢＡエディター（コードが書かれているところ）のヘルプを見ましょう。

シートのヘルプで　MOD を検索し、ＭＯＤ関数をクリックすると書式が書いてあります。
　　ＭＯＤ（数値，除数）
とありますね。　
ＶＢＡヘルプで　MOD を検索すると、Ｍｏｄ演算子　というのが出てきます。　書式は、
　　result　=　number1　Mod　number2
とあります。
つまり、書式が全く違うわけです。　この様な例は他にもあります。
ただし、ＭＯＤ　の場合、ＶＢＡ では演算子として使用できるので、わざわざ関数として使う必要はありませんので、エクセルシートのＭＯＤ関数は使えないような仕様になっています。
VBAで使用できるワークシート関数一覧、およびVBA演算子については、No.1　さまの通りです。

さて、本題ですが、ＶＢＡで　「４桁の数字を、千の位・百の位・十の位・一の位に分解する」　方法は色々考えられます。
４桁用のサンプルです。
あなたの式、
　Range("B1").value=Application.WorksheetFunction.MOD(INT(Range("A1")/1000),10)
を生かし　100　の位の数値を出すのなら、
　Range("B1").value=Int(Range("A1").Value / 100) Mod 10
となります。　なお、　Application.WorksheetFunction　の　Application.　は通常、省略可能です。

簡単だと思えるのは、数字を一旦文字列にして1つずつ取り出し数値に変換し直す方法です。
一見難しそうですが、ＶＢＡには　数値→文字列、文字列→数値　の各変換は、エクセルが自動でやってくれます。　これを利用します。
For　…　Next 文を使ったり、Ｒ1Ｃ1参照形式を使用したりで、式がより簡単になったり、汎用性のあるコードになったりと、いろいろバリーエーションが出来ます。
サンプル。

Sub test()
　　Dim a As Integer
　　a = Range("A1").Value
　　Range("B1").Value = Mid(a, 1, 1)
　　Range("C1").Value = Mid(a, 2, 1)
　　Range("D1").Value = Mid(a, 3, 1)
　　Range("E1").Value = Mid(a, 4, 1)
End Sub

上記コードを　Ｒ1Ｃ1参照形式を使用した場合

Sub test2()
　　Dim a As Integer, c As Integer, n As Integer
　　a = Cells(1,1).Value
　　n = 1
　　For n = 1 To 4
　　　　c = n + 1
　　　　Cells(1, c).Value = Mid(a, n, 1)
　　Next
End Sub

これで、"A1"の４桁の数値（数列）が各位別に、"B1"～"E1"に入ります。　

そのうち勉強してください。

お礼

ご回答ありがとうございました

回答No.2

web2525

Range("B1") = Mid(Range("A1"), 1, 1)
こんな感じでいかがでしょうか。

お礼

ご回答ありがとうございました

回答No.1

xls88

MODワークシート関数はVBAでは使えないようです。
VBAで使用できるワークシート関数一覧
http://www.relief.jp/itnote/archives/001974.php

VBAでは演算子になります。
演算子
http://www.officepro.jp/excelvba/basic/index6.html

お礼

ご回答ありがとうございました