【VBA】入力規則（Validation）のリストに数式を設定する方法

お世話になります。

申し訳ございません。
質問内容に不備がありましたので再投稿させていただきます。

入力規則（Validation）で、
変数Listに格納した文字列を表示させようとしています。

現在、私の手元では以下のように記述しています。

…
Dim List As String
List = "A,B,C,=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -3)"

If 条件 Then
Target.Validation.Add Type:=xlValidateList, Formula1:=List
End If
…

リストが適用されたセルを確認すると、
数式部分がカンマで分割されて表示されてしまいます…

なんとか数式を分割させずに適用させる方法はないでしょうか？

No.3 回答者： WindFaller 回答日時： 2018/09/12 01:57

こんばんは。

>=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -3)"
その数式の値が変化するのでしょうか。数式自体を残す意味合いはないように思うのです。入力規則に書かれた内容ですから、それを①のように翻訳してしまえば済むことだと思います。

もちろん、以下の②のように、
'adr = "OFFSET(INDIRECT(ADDRESS(ROW()，COLUMN()))， 0， -3)"
全角カンマは、ワークシートでは、自動的に半角になるのを利用すれば可能ですが……

お好きな方を選んでください。

Sample
'//
Dim myList As String
Dim adr As String
myList = "A,B,C"
'数式の翻訳　①
adr = ActiveCell.Offset(0, -3).Address(0, 0)
'裏技　上と交換する　②
'adr = "OFFSET(INDIRECT(ADDRESS(ROW()，COLUMN()))， 0， -3)"
With ActiveCell.Validation '入力規則
　 .Delete
　 .Add Type:=xlValidateList, Formula1:=myList & ", =" & adr
End With

No.2 回答者： fujillin 回答日時： 2018/09/06 09:47

No1です

＞回答者様の「手動で」という方法が、以下の内容と同様かもしれません。
内容的には異なります。No1で言及したのは、例えばリストで直接"A,B,=A1+B1"のような指定を行うと、「=A1+B1」を選択すると計算した結果が表示されますが、これと同じ状態を関数式中にカンマが入っても実現できるという意味です。（ただし手動のみ）
補足でご提示の内容は、むしろ「次善の策」として申し上げたものに近いかと。


＞リストに表示させる内容を都度変更するためVBAを使用した記述方法を模索していました。
補足のご説明を読むと、リストの項目構成が変わるけれど、関数式の内容が変わるわけではなさそうですが・・・
＞例１．特定のセルが「A」ならばその下のセルはA1~A4をリストに表示
＞例２．特定のセルが「B」ならばその下のセルはA2~A4をリストに表示
計算式は固定で（固定でなくても良いですが）、リストの項目が変化するということでしょうか？
ドロップダウンの項目表示内容さえ気にしなければ、比較的容易に実現できそうな気がします。

そもそも、ドロップダウンの表示に関数式そのものを表示することに疑問を感じています。
式そのものを表示するよりも、式の意味を表示した方が使う人にはわかりやすいと思いますので。
（式は長いものになりがちだし、式を見て意味を即座に理解できる人は少ない。
　それよりも式の意味（例えば：「○〇の集計」とか「◇◇表からの参照」など）を表示する方が選択の目安にしやすいと考えます）

以下、そのような考え方で実現する方法を示してみます。
1）まず、準備として、補足でも述べていらっしゃいますが、使用する関数式（複数あればその分だけ）表示名（計算の内容）を決めて、「名前の定義」で関数式を登録しておきます。
（相対参照の式にしておくと、使用セルの位置関係でエクセルが調整してしまうので、絶対参照の式にしておいた方が安全です）
定義した名前を、仮に、「関数式1」、「関数式2」の2つだとします。

2）VBAの処理内で、条件に応じてリストの文字列を作成する際に、関数式ではなく上記の名前の定義を利用することで「カンマ」を排除することが可能になります。
例えば、list = "A,B,=関数式2" のような感じです。
表示したい項目構成と関数式に応じて、この文字列の内容を調整しさえすれば、条件に応じた内容を自由に表示できるようになるはずです。
（最初のご質問の内容と異なるのは、リストに表示される項目名が、「関数式そのもの」ではなく「式に付けた名前」（－式の意味）になる点だけと考えます）

※ 式そのものの内容も条件によって変わる場合（参照位置など）であっても、名前の定義内容をVBAから変更することで、同様の方法で実現は可能だと思います。

No.1 回答者： fujillin 回答日時： 2018/09/05 16:54

あっちに回答しようとしたら、すでに締め切られていましたので。

「入力規則」でしたかぁ・・・かなり難しそうですね。

「"A,B,C,""D,E,F"""」で定義するとか、配列の名前の定義経由で行うなどを試してみましたが、どうもうまくいかないようです。
私に思いつけるのは以下の代替方法ですが、他の方が良いアイデアをお持ちかも知れません。

関数式の内容が固定であるなら、手動でご提示のような入力規則を設定する方法はあります。
しかしながら、VBAからだと同様に行っても無視されてしまうようです。
（とは言え、VBAでできたとしても相当に面倒な処理になりますが…）
もしも関数式が可変で、VBAで都度式を作成しているようなケースであれば、手動設定という方法は無理ですね。

ただし、直接結果を求める関数式をリストに入れるのではなく、別のセルに計算させておいて、そのセルを参照する式（=カンマを含まない式）をリストに設定するのであれば可能です。
被参照セルの関数式も、通常の式の設定ですみますので、VBAから式の内容を変更することが可能です。

次善の策として、
空いているセル範囲にリストを作成して、それをセル範囲で参照する形式の入力規則リストにしたのではご希望には沿わないのでしょうか？
この場合は、計算式を表示するようにすると、セル選択の表示も計算式が表示されるだけとなります。
元のリストを関数式として設定しておけば良いのですが、その場合はリストで表示される内容が関数式ではなく、式の計算結果となります。

ご質問とは異なる解決方法になってしまいますが、
リストは「A,B,C,計算値」などからの選択としておいて、「計算値」が選択された場合だけ、隣のセルに関数の計算結果が表示されるというような仕様でも良ければ、関数式が可変（VBAで作成する）の場合でも対応可能と思います。
（隣のセルにVBAから関数式を設定するだけですみますので）

この回答へのお礼

質問の乱投により、混乱させてしまい申し訳ありません。

ご回答ありがとうございます。
回答内容を拝見させていただきました。
また、思ったより長文になってしまい遅くなってしまいましたすみません。

（以下また質問内容に記載漏れですが…）
回答者様の「手動で」という方法が、以下の内容と同様かもしれません。

【Excelの「セルの入力規則」を使用した実現方法】

セル A1~A4の範囲に名前付けをし、
それぞれのセルには以下の値を入れます。
A1：A
A2：B
A3：C
A4：'=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -3)
※A4のシングルクォーテーションは計算式を文字列として扱わせるためのものです。
※この方法の場合はリストからA4を選択すると計算式のみが表示されますが、
　一度セル内を選択してエンターすると計算式が実行されますので一応動きます（震え）

「セルの入力規則」の機能より、リストを選択して元の値を「＝範囲の名前」とすると
今回実施しようとした内容を（手動で？）実現させる事は出来ます。

しかし、今回の質問で「VBA」でやろうとしている理由として以下の条件を設定するためです。

【条件】
特定のセルが更新された時に、
その下のセルに対して設定するリストを変更する。

例１．特定のセルが「A」ならばその下のセルはA1~A4をリストに表示
例２．特定のセルが「B」ならばその下のセルはA2~A4をリストに表示

このように値によってリストに表示させる内容を条件分けするため、
回答者様の言うとおり、リストに表示させる内容を都度変更するためVBAを使用した記述方法を模索していました。

しかし、なかなかスマートな解決策はないようですね・・・
C言語のエスケープ文字と同じような効果の機能があればとも思いましたが、
VBAでは存在しないのでしょうか・・・？

ご回答頂いた内容もまだ全て試せていませんが、
明日には進捗を追記しますのでお時間あればご確認頂ければなと思います。

お礼日時：2018/09/05 18:34