Excelで(1234)を文字として警告なしに登録

質問

Excel2013です。
文字列から"(1234)"というIDを切り出してセルに文字として書き込みたいのですが、
警告(数値が文字列として書かれている)が出てしまいます。
処理は問題ないのですが、全体に挙動が重たくなってしまいます。

' をつけたり" ～ " でくくったりと試してみましたがうまくいきません。
今は"ID(1234)"と接頭語をつけて回避しています。

できればExcel自体の設定はそのままに、このセル内容は明示的に文字列とする
(警告を出さない)方法は有りますでしょうか？

回答No.4

real beatin（@realbeatin） · ベストアンサー

こんにちは。

> 文字列から"(1234)"というIDを切り出してセルに文字として書き込みたいのですが、
> 警告(数値が文字列として書かれている)が出てしまいます。
> 処理は問題ないのですが、全体に挙動が重たくなってしまいます。

"(1234)"を文字列値として書き込むことは出来ている、ということですよね。
その上で、
　[エラーチェック]の警告アイコンを非表示にしたい、
　但し、Excelの設定は変更したくない
ということだとすると、
【文字列書き込み処理後】
のお話でしょうか？
[エラーチェック]の対象が
「文字列形式の数値、またはアポストロフィで始まる数値」
である場合は、
手順として、
●まず、
　[エラーチェック]（数値文字列）の警告アイコンが表示されている
　　連続したセル範囲全体を選択
　　（一連の単矩形範囲を
　　　同種の警告アイコンが表示されているセル以外は含まないように
　　　選択しないとヤバイ（落ちるかも）です）
　　（行数・列数が多い時は選択後にEnter Shift+Enterの順にキーを押せば
　　　警告アイコンのある先頭セルにスクロールできます。）
●次に、
　[エラーチェック]（数値文字列）の警告（プップアップ）アイコン
　　をクリック→ポップアップ（メニュー）が表示されたら
●最後に、
　[エラーを無視する]
　　をクリック
のように手当てしておけば、
（Excelのオプション[エラーチェック]を変更せずに）
警告アイコンを消すことが出来ます。
そのまま保存すれば、警告アイコンなしで保存できます。
しかし、再度数値文字列を書き込んだ場合は、
再び、[エラーチェック]に掛かります。

> 全体に挙動が重たくなってしまいます。
この一文の意味する所が、ちょっと気に掛かっているのですが、
【文字列書き込みをＶＢＡで行うループ処理が重たい】
ということを問題にされているのであれば、
For文やFor Each文でセル値を書き換える場合には、
確かにビックリするぐらい重くなります。
　[エラーチェック]（数値文字列）
　Application.ErrorCheckingOptions.NumberAsText = True
が設定されている場合は、
単セル毎にXMLソースのNodeを書き直すことになる筈ですから
時間が掛かったりハングするのも避けようがないかな、と思います。
ただ、問題の本質は、表計算アプリで数値文字列を扱うこと
であって、[エラーチェック]は福次的なものでしょうね。
"(1234)"というのは、表計算アプリでは数値文字列として扱うよりありませんから、
内部的なエラーを無視することは出来ても、
エラーを消せる訳ではないです。
[エラーチェック]を切ったとしてもそれは、
喩えるならVBAでプロシージャ丸ごと On Error Resume Next で強引に実行するみたいな？
可能なら数値文字列以外のフォーマットを検討なさった方が、
保守の面で安心できるとは思います。

重さ（処理が遅くなる原因）を回避する意味では、
なるべく配列で出力するのが最も効果あります。
後は、上書きする時は、一旦.ClearContentsしてから出力するとか。

以下、テスト用のソース挙げておきます。
★マークしたところは、遅さを回避する為の工夫です。
Forループ版（数十秒）と配列変数版（コンマ数秒）と
新規のブックで試してみると'重さ'の違いが感じられると思いますので、
参考になることが少しでもあれば、と。

' ' /// 数値文字列出力　Forループ版
Sub Re9173129a()
Dim i As Long
Dim t As Single: t = Timer
　　With Application
　　　　.ScreenUpdating = False　'　★
　　　　.EnableEvents = False　'　★
　　　　.Calculation = xlCalculationManual　'　★
　　End With
　　With Range("B2:B65536")
　　　　.NumberFormat = "@"　'　★
　　　　.ClearContents　'　★
　　　　For i = 1 To 65535
　　　　　　.Cells(i, 1) = Format(i, "!(0000!)")
　　　　Next i
　　　　Call IgnErr(.Cells)　'　[エラーチェック]（を無視して）アイコンを消す
　　End With
　　With Application
　　　　.Calculation = xlCalculationAutomatic　'　★
　　　　.EnableEvents = True　'　★
　　　　.ScreenUpdating = True　'　★
　　End With
Debug.Print "testB:"; Timer - t; ".Sec"
End Sub

' ' /// 数値文字列出力　配列版
Sub Re9173129c()
Dim v() As String　'　★
Dim i As Long
Dim t As Single: t = Timer
Dim def As Boolean
　　def = Application.ErrorCheckingOptions.NumberAsText　'　★
　　If def Then Application.ErrorCheckingOptions.NumberAsText = False　'　★
　　With Range("D2:D65536")
　　　　ReDim v(1 To .Rows.Count, 1 To 1)　'　★
　　　　.NumberFormat = "@"　'　★
　　　　.ClearContents　'　★
　　　　For i = 1 To 65535
　　　　　　v(i, 1) = Format(i, "!(0000!)")　'　★
　　　　Next i
　　　　.Value = v　'　★
　　　　Call IgnErr(.Cells)　'　[エラーチェック]（を無視して）アイコンを消す
　　End With
　　If def Then Application.ErrorCheckingOptions.NumberAsText = True　'　★
Debug.Print "testD:"; Timer - t; ".Sec"
End Sub

' ' /// [エラーチェック]（を無視して）アイコンを消す
Private Sub IgnErr(Target As Range)
Dim c As Range
　　For Each c In Target
　　　　With c.Errors(xlNumberAsText)
　　　　　　If .Value Then .Ignore = True
　　　　End With
　　Next
End Sub
' ' ///

お礼

以前からお世話になっていたとは…慌てて掘り起こしてみました。
以前頂いた一気に消すソースは今も元気に動いています。

今回ですが、なんと言いますか、ご賢察の通りです。
というか怖いくらい状況を当てられてます。

当方かなり素人なので、ソースも非常に勉強になりました。
配列の使い方サンプルもかゆいところに手が届いてます。
テスト時に画面非表示しても遅いから本番を思うと途方に暮れてました。

回答6にBAすると変な感じになるのでこちらに返信＆BAさせて頂きます。
ありがとうございました。

回答No.6

real beatin（@realbeatin）

回答No.4、訂正です。

誤）　... Format(i, "!(0000!)")
正）　... Format(i, "(0000)")

回答文を書く前に修正済ませていたつもりが漏れていました。
（説明文にもタイプミスありますが）すみません。

以前にも私は回答したことがあるのですが、
過去に見かけた質問者さんのご質問はＶＢＡに関するものが多かったように
私は記憶していたので、ＶＢＡのサンプルを挙げてみたのですが、
これも思い違いでしたら、適当に流してやってください。
回答No.4全体としての趣旨は、
[エラーチェック]の[エラーを無視する]
を適用することと、それに纏わる説明です。
因みに、[エラーチェック]の警告アイコンが出たり出なかったり、
細かな環境因で異なる結果になるということもありますが、
その違いがどのようなものなのかは、こちらでは特定できていません。
Officeが64ビット/32ビット、Excelで設定するマルチスレッドの多少、
が影響するような気もしていますが、これは、確証のない話です。
[エラーを無視する]に関しては、決定的な対策とは呼べないでしょうけれども、
確かめた限りでは汎用性があるようです。

以上、訂正と補足でした。
失礼しました。

回答No.5

m_and_dmp

NO.1 です。
調べてみましたが、シート上で処理するのではなく、エクセル全体でエラーチャックオプションの設定が可能であることがわかりました。
ファイル→オプション→数式　にエラーチェック関係の設定があります。

お礼

ありがとうございます。
前回のレスではよく見ていませんでしたが、改めてみると、
警告時に横に出るオプション設定の画面で、Excel全体設定
だけで無く、個別に警告を抑止する設定も存在することに
気がつきました。

回答No.3

kagakusuki

Excel2013では確認しておりませんが、少なくともExcel2010では「'」を付けて
'(1234)
とする事で文字列になりますし、セルの書式設定の表示形式を[文字列]とする方法も有効でした。
　後、別の方法として

="(1234)"

と入力するのでは駄目なのでしょうか？

補足

文字列項目に「文字として(数字)を書いたら」数値扱いされて警告がでる
という状態でして、どうもこれはExcel2013のエラーオプションに
そのものずばりの抑止項目があるので規定の挙動っぽいです。
="(1234)"
これも標準項目ではOKですが、文字項目に書き込むとそのまま
この文字が出てしまうようです。

回答No.2

nananotanu

手元にエクセルがないのでうろ覚えでごめんなさい。

セルの書式設定⇒表示形式　で当該セルを文字列にしておけば良くなかったでしたっけ？

補足

すいません、その状態で出るっぽいので困ってます。

回答No.1

m_and_dmp

警告が出た時、そのセルを選択すると、左側に何かアイコンのようなものがあると思います。その上にマウスポインタを持って行くと、アイコンの右に▼が現れますので、それを押してプルダウンメニューを開いて、エラーチェックオプションの「文字列として入力されている数値」のチェックを外してみてください。
たぶん、以後、警告がでなくなると思います。
次のページにピッタリの記事があります。（Office のバージョンが記述されていませんが、）
http://ciao.aoten.jp/ciao/2013/11/post-9b55.html

補足

やはりExcel自体の設定で警告を殺すしか無いのでしょうか…

他の所では警告が出て欲しいけど、使用者が意図的に設定している
場合は警告をセル単位で解除できても良さそうなもんですが。