● Excel2010：「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています」というグリーンの隅っこ三角エラーマークを消して、セルの値を明示的に数値化するプログラム
※2019/03/19追記：
先にこちらのWebページを読んでください。
本記事を書いたかなり後にみつけた神サイトです。
『Excel VBA セルのエラーチェックマークを削除する Errorオブジェクト』
※関連記事
『Excel2010で、開かれていない閉じたままのブック・Excel（xls）データをできるだけ速く読み込む方法（DAOにて）』
『ADO：Excel2010で、開かれていない閉じたままのブック・Excel（xls）データをできるだけ速く読み込む方法ーその２（ADOにて）』
DAOやADOにて、他のファイルやデータベースから何らかのデータを吸い込んだ時に、下図のようなグリーンの三角のエラー表示が出てしまう場合（「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています」というエラーが表示されてしまう場合）、そのセルを全部数値に変更するプログラムです。
一回実行してしまうと、Ctrl＋Zなどでは戻せませんので、必ずシートもしくはファイル自体のバックアップコピーを使ってから試してみてください。
データがめちゃめちゃに破壊されてしまっても自己責任ですみませんがよろしくお願いいたします。ご自分の環境でうまくいくまで、何度でもバックアップしてご検証ください。
なお、「数字が1文字もないセル」、つまり「完全に日本語や英語や日付などの文字しかないセル」にも、もし、グリーンの三角マークが出てしまっていた場合・・・、その場合には本プログラムは対応してませんので、その際はそれにも対応できるコードを追記する必要があります。
基本、「UsedRange」にて、シート内の使われたセルの周辺範囲だけ（空白セルも含む）を見に行きます。
そして、「xlNumberAsText」でグリーン三角マークのあるセルのうち「テキストとして格納された数値が入力されたセル」だけを処理の対象としています。
その他の判別方法は、プログラムのあとに書いた「Errors.Item プロパティ (Excel)」のところを読んでみてください。
また、「Rng.Value = Replace(Rng.Value, "%", "", , , vbBinaryCompare)」のところでひっかからない文字があったら、必要に応じて追加してください。
いったんグリーンマークのセルが正常化されてしまうと、次からは何度実行しても何も処理はされません。（もともとグリーンエラーのセルしか処理対象にしていないため）
それがもし都合が悪い場合は、その場合も状況に応じたプログラムの追記が必要です。
ADOやDAOのとき以外にも出ることがあるグリーンマークなので、もしこれを消す処理が多いなら、「アドイン」のタブやクイックツールバーなどに「ボタン」として追加してしまうとよいかもしれません。
参考記事
『ExcelVBA:WordVBA:他のVBA：ビジネス基礎：VBAプログラム（マクロ）をクイックツールバーにボタンとして組み込む方法』
『Excel2010のリボンに、Excel2000と同じプログラムコードのコピペでユーザー設定ボタンを生成する。（一応ツールバー単位で』
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
' ' '##################################################################################### 'セルの左上の隅にグリーンの三角が表示されてしまい、 '「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています」 'というエラーになってしまったときに、それを解消するプログラムです。 '「UsedRange」にて、シート内の使われたセルの周辺範囲だけ（空白セルも含む）を見に行きます。 '「Rng.Errors.Item(xlNumberAsText).Value = True」でグリーンの三角のあるセルだけを '処理の対象としています。 '「Rng.Value = Replace(Rng.Value, "%", "", , , vbBinaryCompare)」のところで 'ひっかからない文字があったら、必要に応じて追加してください。 'いったんグリーンセルが正常化されてしまうと、次からは何度実行しても '何も処理はされません。（グリーンエラーのセルしか処理対象にしていないため） '##################################################################################### Sub GreenTriangleErrErase01() Dim Rng As Range For Each Rng In ActiveSheet.UsedRange '今開いているシート内の「使用されたセル範囲のすべてのセル」に対して、 '以下の処理をします。 If Rng.Errors.Item(xlNumberAsText).Value = True Then 'もしセルに緑色の三角（エラー表示）があったら次の処理 'もし「文字列の値だと誤判別されてしまう原因っぽい文字」が 'セルの中に混ざっていた場合、それを消して数字だけ残す。 '必要に応じて、消したい文字を増やします。 Rng.Value = Replace(Rng.Value, "%", "", , , vbBinaryCompare) Rng.Value = Replace(Rng.Value, "％", "", , , vbBinaryCompare) Rng.Value = Replace(Rng.Value, "円", "", , , vbBinaryCompare) Rng.Value = Replace(Rng.Value, "\", "", , , vbBinaryCompare) Rng.Value = Replace(Rng.Value, "￥", "", , , vbBinaryCompare) Rng.Value = Replace(Rng.Value, ",", "", , , vbBinaryCompare) '数字に１をかけて数値化 Rng.Value = Rng.Value * 1 '今のセルのもともとのセルの値に１をかけて、今のセルに代入（要するに上書き）する。 Rng.NumberFormatLocal = "G/標準" '数値にした際にセル書式が「数値」になってしまうので「標準」にする Else 'もしセルに緑色の三角（エラー表示）が無かったら次の処理 '何もしないで次へ End If Next End Sub ' ' |
以下のURL先の情報もご参考に・・・
『Errors.Item プロパティ (Excel)』
https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/errors-item-property-excel?f=255&MSPPError=-2147217396
セルの内容によって処理を考えるときに役立つと思います。
今回のサンプルでは「xlNumberAsText セルには、テキストとして格納された数値が入力されています。」を使っています。
Errors.Item プロパティ (Excel)
Error オブジェクトの単一のメンバーを返します。
構文
式 . Item( Index )
式Errors オブジェクトを表す変数。
パラメーター
注釈
Index には、次のいずれかの定数も使用できます。
xlEvaluateToError セルはエラー値に評価されます。
xlTextDate セルには、年が 2 桁のテキスト日付が入力されています。
xlNumberAsText セルには、テキストとして格納された数値が入力されています。
xlInconsistentFormula セルには、領域内の矛盾した数式が入力されています。
xlOmittedCells セルには、領域内のセルを除いた数式が入力されています。
xlUnlockedFormulaCells ロック解除されたセルには数式が入力されています。
xlEmptyCellReferences セルには、空白セルを参照する数式が入力されています。
