エクセル：大きい数字を簡単に入力できるマクロ

質問

いつも大変御世話になっております。
仕事柄比較的桁の大きい丸まった数値を入力することが多いため、以下のことが出来るマクロ・VBAがあれば教えてください。
数値の最後に「m」と入力すると自動的に百万倍の数値に変換される。ただし最後に「m」をつけなければそのまま入力される。
例）10m⇒10,000,000
　　8.5m⇒8,500,000
　　0.5m⇒500,000
　　500,000⇒500,000

宜しくお願いいたします。

回答No.5

BLUEPIXY · ベストアンサー

>Target.NumberFormat = "#,##0"
でエラーですか、なぜでしょう？
私の使っている環境(2002)ではエラーにならないです。
でも、単に書式の設定をしているだけなので、必要ないといえば必要ないですので削除すればいいと思います。
＞状況に応じてチェックを増やすべきかもしれません
自分で言っておいてなんですが、適切な例を思いつかないのですが＾＾；
これを試していてすぐに、では、１２ｍ（１２メートルとかの意）と入力したい場合はどうすりゃいいのかとか思いました。
そのために、「Not Target.HasFormula 」のチェックを付けてあります。
なので、先述のような場合は
="12m"
と入力すればいいことになります。
ただ、それでも通常文字列として入力する場合に使う'（アポストロフィを頭に付ける）を使っても変換されてしまいます。
そのような場合でなくても、最後がｍでその前が数値として解釈できるが、そう解釈して貰ってはこまるという場合もあるかもしれません。
例えば、
&heemとか入力した時に、数値に変換されてしまいます。（通常だと文字列として評価されます）
あと、逆に10Mの様に大文字の場合も変換して欲しいということもあるかもしれません。

お礼

早速の丁寧なご回答ありがとうございます。「チェック」の意味はよくわかりました（ような気がします）。
ちなみに手元に２台のPCがあり、2000と2002両方があるので試してみたところ、先ほどのエラーは2002で出たのですが、2000でやってみたところエラーなしでいけました。BLUEPIXYさんとは逆です、不思議です。(?_?)

回答No.7

Wendy02

こんにちは。

私個人は、100万ギリの数字は、予算組みぐらいですから、年に１、２度ですね。

一通り、みなさんのスレッドを読んで、私も作ってみました。もう食傷気味でしょうから、さらに、みなさんのを押しのけるつもりはありませんので、良かったら試してみてください。

イベント・ドリブン型のように、マクロが外れる心配はありません。しかし、本来は、ワンストロークキーに割り当てたいと思うのですが、それは、入力中のマクロのWaiting 状態では、マクロは利きません。

以下のマクロで、使ってみた感触では、#3さんでおっしゃるようなスピード面で遅くなる、というような印象は感じません。マクロの種類にもよるかと思います。これは、Enterを入れたとたんに変換されています。文字列や文字列として認識されるものは、変換されません。

また、Target.NumberFormat = "#,##0"　は、興味深いです。英語版では、ほとんど違いは出ないはずですが、日本語で使用する場合、通常、書式のプロパティは、NumberFormatLocal の方が安全のはずです。

ただ、今回は、書式そのものはワークシート上のユーザー側の設定の範疇ですから、それ必要はありませんね。

'<標準モジュールのみ>
'-----------------------------------------------------
Sub SettingKeys()
'キーの設定
　Application.OnKey "{ENTER}", "MegaReplace"
　Application.OnKey "~", "MegaReplace"
End Sub
Sub SettingKeysOff()
'キーの解除
　Application.OnKey "{ENTER}"
　Application.OnKey "~"
End Sub

Private Sub MegaReplace()
　Dim myFormula As String
　 If ActiveCell.HasFormula Then Exit Sub
　 If ActiveCell.PrefixCharacter = "'" Or ActiveCell.NumberFormatLocal = "@" Then Exit Sub
　　　If ActiveCell.Value Like "*#m*" Then
　　　　 myFormula = WorksheetFunction.Substitute(ActiveCell.Value, "m", "*1000000")
　　　　 On Error Resume Next
　　　　 myFormula = Evaluate(myFormula)
　　　　 If Err.Number = 0 Then
　　　　　 ActiveCell.Value = myFormula
　　　　 End If
　　 End If
　''ただし、入力後のセルの移動は、下方に行くように設定されていること
　ActiveCell.Offset(1).Select
End Sub
'-----------------------------------------------
Sub Auto_Open()
　'ブック起動用
Dim Dummy As Long
　Call SettingKeys
End Sub
Sub Auto_Close()
　'ブック終了用
　Call SettingKeysOff
End Sub

.

お礼

ありがとうございます。こちらも試してみてぜひ勉強させていただきます。それにしてもいろいろとやりかたがあるんですね、奥が深いです。ポイントつけられず申し訳ありません。確かWendy02さんには以前も何かで助けていただいた記憶があります。また宜しくお願いいたします。
PS　金融機関という仕事柄、自分の私生活には縁のないやたら大きな数字を扱っています。(;^_^A

回答No.6

takibo

No.4 takibo です。こちらは Excel2002 で確認済みです。
以下の点を確認してください。

●先程のコードではイベント発生範囲をＡ列のみに限定しています。Ａ列で試してみてください。
　>If Intersect(Target, Range("A:A")) Is Nothing Then
　　　　Target → 入力されたセル
　　　　Range("A:A") → 指定範囲
　必要に応じて指定範囲を書き換えてください。
　表が B5:H30 だとしたら Range("B5:H30")
　または、B5:H30 に「入力エリア」と名前を定義して、 Range("入力エリア")

●先程のコードで「m」は半角小文字に限定しています。大文字や全角の場合は文字と認識します。

●何かのタイミングでイベントが止まっているかもしれません。
　VBEの画面からイミディエイトウィンドウを開いて（Ctrl + G）
　Application.EnableEvents=True
　と入力して Enter を押してからやってみてください。

お礼

できました！！！ありがとうございます。書き込む場所を取り違えていたみたいです。半日もしないで解決できました。皆さんいろいろとありがとうございました。全員の方にポイントをつけたいのですが、ご容赦ください。

回答No.4

takibo

処理をするセル範囲を限定させています。多少は負担を軽減できるかと思います。
Range("A:A") の部分は Range("入力範囲") のように名前を使うことができますので範囲が可変の場合はその方が便利かもしれません。

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err1
　　Application.EnableEvents = False
　　If Intersect(Target, Range("A:A")) Is Nothing Then
　　Else
　　　　With Target
　　　　If Right(.Value, 1) = "m" Then
　　　　　　.Value = Left(.Value, Len(.Value) - 1) * 1000000
　　　　End If
　　　　End With
　　End If
　　Application.EnableEvents = True
err1:
　　Application.EnableEvents = True
End Sub

【今回の意図とは逸れているかもしれません。参考までに。】
「小数点位置を固定する」機能を切り替える方法 （ショートカットキーに割り当てて使う用に考えてみました）
[ツール]－[オプション]－[編集] の「小数点位置を固定する」設定をオン／オフ切り替えします。設定状況はステータスバーで確認できます。
テンキー以外のキーを使う必要が無いのですが小数点による操作の為、当然のことながら 0.5 → 500,000 とはなってくれません。
その場合は上記イベントと併用するか、設定と入力の桁数を変更するなどの必要があります。

Sub 入力切替()
　　If Application.FixedDecimal = False Then
　　　　Application.StatusBar = "×1,000,000"
　　　　Application.FixedDecimal = True
　　　　Application.FixedDecimalPlaces = -6
　　Else
　　　　Application.FixedDecimal = False
　　　　Application.StatusBar = False
　　End If
End Sub

お礼

ありがとうございます、takiboさんのも試してみたのですが、残念ながらうまくいきません。10mといれるとそのまま10mという文字になってしまいます。

回答No.3

banker_U

こんにちは

私も、大きな数字を入力することがよくあります。
私は以下のようにしています。これで、目的はおおむね達せられると思います。VBAとかを使用しているのではありません。最初からこうなっています(多分仕様です)。

例)1e6→1,000,000
　 1e7→10,000,000
　 1.5e8→150,000,000

要するに、あeい(あ,いは任意の数値)と入力すると、あ×10^い(10のい乗)が入力されると言うことです。eは全角でも半角でも大文字でも小文字でもOKです。

(注意)
　Excelのバージョンによっては上記の方法で入力すると、入力セルの表示形式が「標準」の場合、勝手に「指数」となってしまうことがあります。ただ、どうせコンマ区切り等の書式を設定しているでしょうから関係ないですよね。

マクロを使用しておっしゃる機能を実現することはできますが、セルに何かを入力するたびにマクロが起動する格好となるので、処理速度等の面からお勧めできません。

お礼

おぉ～（感動）、本当ですね、できます。ありがとうございます。エクセルをもう10年以上使っていますが全く知りませんでした。この方法もぜひ役立てたいと思います。

回答No.2

BLUEPIXY

簡略的には、ThisWorkbookのSheetChangeイベントで以下の様にすればいいと思います。（状況に応じてチェックを増やすべきかもしれません）
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim x
x = Target.Value
If Not Target.HasFormula Then
If Right(x, 1) = "m" Then
x = Left(x, Len(x) - 1)
If IsNumeric(x) Then
Target.Value = Val(x) * 1000000
Target.NumberFormat = "#,##0"
End If
End If
End If
End Sub

お礼

その後「Target.NumberFormat = "#,##0"」を削除してみたところ、エラーは出なくなりました。ありがとうございました。ちなみに（状況に応じてチェックを増やすべきかもしれません）というのは、何をすべきなのか教えていただけますか？VBA・マクロは見よう見真似でやっているもので、スイマセン。そろそろちゃんと勉強しようと考えてはいるのですが。。。m(__)m

補足

ありがとうございます。できるようなのですが、エラーメッセージが出ます。

「実行時エラー’1004’：
Range クラスのNumber Formatプロパティを設定できません」

と出て、デバッグを押すと、
Target.NumberFormat = "#,##0"
にハイライトがかかります。

何かまだ設定が必要なのでしょうか？宜しくお願いいたします。

回答No.1

s___o

入力用のセルと出力用のセルを分けるのはダメですか？

例えば、A列を入力セル、B列を出力セルにして、
B列に計算式を入れるとか。
B1に設定する計算式：
=IF(A1="","",IF(RIGHT(A1)="m",LEFT(A1,LEN(A1)-1)*1000000, A1))

お礼

その方法も考えたのですが、何とか直接入力でできないものかと思いまして。どうしても無理であれば、アドバイスいただいたようにします。ありがとうございました。