Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2016)
セルの関数式を上書きした場合に上書きしたことがわかる印をつけたい
投稿日時: 21/03/18 15:21:48
投稿者: sundayyonday

 
 
あらかじめ関数式が入っているExcelの表において、関数式を上書きして、時刻、数字、文字列を入力した場合、あとからどのセルを上書きしたのかわかるようにしたいと考えています。
 
 
そのために、以下のサイトを参考にしてコードを書いてみました。
 
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1036552930
(この中の「その他の回答」)
 
以下のコードをシートモジュールに入れる
ーーーーーーーーーーーーーーーー
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCel As Range
Dim buf
 
Set myCel = Application.Intersect(Target, Me.Cells)
buf = TypeName(myCel.Value)
If buf = "Double" Then
Target.Interior.ColorIndex = 3
End If
End Sub
 
例では入力が数値の場合セルを赤くします。
文字列の場合"Double"="String"にします。
ただし、TargetをCellsから指定の範囲に限定しないと
そのシートのどこに数値を入力しても
赤くなります。
ーーーーーーーーーーーーーーーー
 
しかしこのやり方だと、ユーザが誤って上書きしたときに「元に戻すキー」を押しても、セルの内容も赤いセルの色も元に戻らず、ユーザの困惑の元になるため、以下のように表の右外に1列開けての目印(「数値入力」とか「文字入力」とか)をつけることを考えています。
 
ーーーーーーーーーーーーーーーー
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCel As Range
Dim buf as string
 
Set myCel = Application.Intersect(Target, Columns("A:B"))
 
rw = myCel.row
clm = myCel.CurrentRegion.Columns.Count+2
 
buf = TypeName(myCel.Value)
If buf = "Double" Then
    Cells( rw, clm +2 ) = "数値入力"
ElseIf buf ="String" then
    Cells( rw, clm +2 ) = "文字入力"
End If
 
End Sub
ーーーーーーーーーーーーーーーー
 
質問1
 
上のコードだと、表の右外に正しく「数値入力」とか「文字入力」と書き込まれたあとに、
rw = myCel.row
のところでエラーになって止まってしまいます。
 
エラーメッセージは、「オブジェクト変数またはwithブロック変数が設定されていません。」です。
オブジェクトが存在しないのはmyCelで、エラーが出た時点のTargetの値は、「数値入力」や「文字入力」となっていることはわかったのですが、回避の方法がわかりません。
どなたかご教示いただけると大変ありがたいです。
 
質問2
上のエラーは一旦最後まで処理が進んだ後にまた上に戻ってきてエラーが発生しているように見えます。これは一体何が起きているのでしょうか。
 
質問3
本当は、表の中には時刻が表示されているため、文字列か数値かの判別だけではなく、時刻かどうかの判別も加えたいのですが、TypeNameでは判別できないように思えます。
もし良い方法ご存知の方がいたらお教え下さい。
 
 
質問3については、可能であればやりたい、という程度なので解決しなくても大丈夫です。
 

回答
投稿日時: 21/03/18 16:47:59
投稿者: WinArrow
投稿者のウェブサイトに移動

内容をよく読んでいませんが、
 
数式を保護するために、(壊されないために)
通常は、
当該セルにロックを掛けて、シートを保護する
というような機能を使います。
 
上書き保存した後は「元に戻す」は無効です。
また、コードで設定したこと(色設定など)も「元に戻す」は効きません。
 
このセルには、数値だけ、など特定することができる倍は
入力規則で
IME設定したり、入力メッセージを表示させたりもできます。
一旦、覚えてしまうと、入力メッセージは煩わしく感じられます。
 
もう一つは、条件付き書式をを使う方法があります。
例外的な値が入力されたら、色を表示する というようなことも可能です。
 
質問1
A:B列以外のセルを変更した時のエラーです。
 
 
質問3
時刻は、小数点以下の数値です。
1日は、1.0ですから、それを利用することもできます。
 

回答
投稿日時: 21/03/18 16:52:01
投稿者: WinArrow
投稿者のウェブサイトに移動

質問2
は説明がよくわありませんが、
↓のようにすれば回避できます。
 

   If myCel Is Nothing Then Exit Sub
    rw = myCel.Row
    

回答
投稿日時: 21/03/18 17:00:47
投稿者: sk

引用:
上のコードだと、表の右外に正しく「数値入力」とか
「文字入力」と書き込まれたあとに
rw = myCel.row
のところでエラーになって止まってしまいます。

引用:
上のエラーは一旦最後まで処理が進んだ後にまた上に戻ってきて
エラーが発生しているように見えます。これは一体何が起きているのでしょうか。

引用:
If buf = "Double" Then
    Cells( rw, clm +2 ) = "数値入力"
ElseIf buf ="String" then
    Cells( rw, clm +2 ) = "文字入力"
End If

そのワークシートのセルの値をコードによって書き換えることで
新たな Change イベントが発生するため(イベントの連鎖)。
 
この場合、2 回目の Change イベントにおいて Target が返すのは
Cells( rw, clm +2 ) と同じセルへの参照です。
  
引用:
エラーメッセージは、
オブジェクト変数またはwithブロック変数が設定されていません。
です。

引用:
Set myCel = Application.Intersect(Target, Columns("A:B"))
  
rw = myCel.row

Target が示すセル範囲が A:B に含まれていなければ
Intersect 関数は Nothing を返すため、myCel には
どのセル範囲への参照も渡されません。
 
その状態で Row などのプロパティを参照しようとすれば
当然実行時エラーが発生します。
 
いずれにしても、イベントの連鎖は最悪の場合
無限ループを招く要因となりますので、その都度
Application.EnableEvents プロパティの設定を
適切に行なうようにしてください。
 
引用:
あらかじめ関数式が入っているExcelの表において、関数式を上書きして、
時刻、数字、文字列を入力した場合、あとからどのセルを上書きしたのか
わかるようにしたい

引用:
本当は、表の中には時刻が表示されているため、文字列か数値かの判別だけではなく、
時刻かどうかの判別も加えたいのですが、TypeNameでは判別できないように思えます。

VBA において、日時データに当たるデータ型は Date です。

回答
投稿日時: 21/03/18 17:12:20
投稿者: WinArrow
投稿者のウェブサイトに移動

セルの表示形式が時刻であることを、確認するには、
シート関数の「Cell関数」が使えます。

=CELL("format",A5)
時刻は、D6,D7,D8,D9のいづれかで判定します。
 
ところがこの関数はVBAでは使用できません。
 
代案のコード
If Application.Evaluate("CELL(""format"",A5)") = "D9" Then
 

投稿日時: 21/03/18 18:47:23
投稿者: sundayyonday

WinArrow様
  
以下のやり方で質問1のエラーは発生しなくなりました。
ありがとうございます。
  
> If myCel Is Nothing Then Exit Sub
> rw = myCel.Row

投稿日時: 21/03/18 21:21:23
投稿者: sundayyonday

WinArrow様
 
代案のコードで悪戦苦闘中です。
 
> If Application.Evaluate("CELL(""format"",A5)") = "D9" Then
 
これを利用させていただこうと思います。
たしかに、この通りに書くと動くのですが、以下のように書き直すと、「型が一致しません」エラーになります。
If Application.Evaluate("CELL(""format"", Target.address )") = "D9" Then
 
この一行でかれこれ、2、3時間格闘しましたが解決に至らず、再びアドバイスを頂けないかと再訪しました。
 
いかがでしょうか?

投稿日時: 21/03/18 21:48:07
投稿者: sundayyonday

sk様
 
早速のご回答をいただき、ありがとうございます。
 
 
> そのワークシートのセルの値をコードによって書き換えることで
> 新たな Change イベントが発生するため(イベントの連鎖)。
 
> この場合、2 回目の Change イベントにおいて Target が返すのは
> Cells( rw, clm +2 ) と同じセルへの参照です。
 
 
なるほど。
イベントの連鎖については、十分な知識がありませんでした。
改めて自分なりに調べてみましたが、確かに
・intersectを使う際には、セルの値が変わるとWorksheet_Changeが再度呼ばれてしまう
・新たなイベントが発生しないように、
  Application.EnableEvents = False
でイベントの発生を抑止する必要があること
が書かれていました。
 
大変勉強になりました。
ありがとうございます。
 
 
> VBA において、日時データに当たるデータ型は Date です。
 
 これを見てヒントになりました。
TypeNameの場合は時刻も数値もDoubleになってしまいますが、VarTypeを使えば、 Date、Integer, Doubleの区別が付きますね。
 
以下のように書き直しました。
 
Buf = VarType(myCel.Value)
if Buf = 5 then

回答
投稿日時: 21/03/18 23:54:39
投稿者: WinArrow
投稿者のウェブサイトに移動

「Target.Address」という文字列になっています。
解決策
Application.Evaluate("CELL(""format""," & Target.Address & ")")

投稿日時: 21/03/19 00:13:33
投稿者: sundayyonday

WinArrow さんの引用:
「Target.Address」という文字列になっています。
解決策
Application.Evaluate("CELL(""format""," & Target.Address & ")")

 
なるほど。
よく考えてみればそのとおりですね。
ありがとうございます。
明日やってみます。
 

回答
投稿日時: 21/03/19 16:02:11
投稿者: WinArrow
投稿者のウェブサイトに移動

回答ではありません。
掲示板にコードを手入力していますよね?
 
コードペインをコピペするようお勧めします。
手入力で誤入力したコード(rw clmのような入力忘れも)を見せられると
余計な突っ込みを入れたくなる。

回答
投稿日時: 21/03/20 18:26:25
投稿者: WinArrow
投稿者のウェブサイトに移動

数式が壊されたことを検知して、
別セルに色を表示する
目的は何でしょうか?
 
「数式が壊された」
といくらアナウンスしても
元の数式に戻すことは無理だと思います。
再度になりますが、
セルのロック&シート保護を検討してみませんか?
 

トピックに返信