● Excel2010:「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています」というグリーンの隅っこ三角エラーマークを消して、セルの値を明示的に数値化するプログラム
※2019/03/19追記:
先にこちらのWebページを読んでください。
本記事を書いたかなり後にみつけた神サイトです。
『Excel VBA セルのエラーチェックマークを削除する Errorオブジェクト』
※関連記事
『Excel2010で、開かれていない閉じたままのブック・Excel(xls)データをできるだけ速く読み込む方法(DAOにて)』
『ADO:Excel2010で、開かれていない閉じたままのブック・Excel(xls)データをできるだけ速く読み込む方法ーその2(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と同じプログラムコードのコピペでユーザー設定ボタンを生成する。(一応ツールバー単位で』
以下の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 セルには、空白セルを参照する数式が入力されています。