こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

4桁の数字を、千の位・百の位・十の位・一の位に分解する関数ですが
例えば
A1に4桁の数字があり、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に置き換えてから行っています。
よろしくお願いします

投稿日時 - 2009-02-16 05:14:30

QNo.4721681

すぐに回答ほしいです

質問者が選んだベストアンサー

こんにちは。

確かに、他の方たちのおっしゃるように、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
--------------------------------

投稿日時 - 2009-02-16 10:27:16

お礼

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

投稿日時 - 2009-02-16 11:58:34

ANo.5

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

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

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

投稿日時 - 2009-02-16 11:50:31

お礼

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

投稿日時 - 2009-02-16 11:58:58

ANo.4

No.3 です。
Sub test2() の4行目の 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

です。

投稿日時 - 2009-02-16 10:23:07

お礼

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

投稿日時 - 2009-02-16 11:57:31

ANo.3

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

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

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

さて、本題ですが、VBAで 「4桁の数字を、千の位・百の位・十の位・一の位に分解する」 方法は色々考えられます。
4桁用のサンプルです。
あなたの式、
 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つずつ取り出し数値に変換し直す方法です。
一見難しそうですが、VBAには 数値→文字列、文字列→数値 の各変換は、エクセルが自動でやってくれます。 これを利用します。
For … Next 文を使ったり、R1C1参照形式を使用したりで、式がより簡単になったり、汎用性のあるコードになったりと、いろいろバリーエーションが出来ます。
サンプル。

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

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

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"の4桁の数値(数列)が各位別に、"B1"~"E1"に入ります。 

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

投稿日時 - 2009-02-16 10:08:21

お礼

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

投稿日時 - 2009-02-16 11:57:10

ANo.2

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

投稿日時 - 2009-02-16 09:59:29

お礼

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

投稿日時 - 2009-02-16 11:56:46

ANo.1

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

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

投稿日時 - 2009-02-16 06:08:35

お礼

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

投稿日時 - 2009-02-16 11:56:30

あなたにオススメの質問