エクセルのVBAの関数について

マクロ初心者です。

２つ質問です。
①標準モジュールや各シート、thisworkbookにプログラムできますが、利用可能なプログラムの範囲？はどうなっているのでしょうか？また、どうやって使い分けしているのでしょうか？
説明を受けた際に各シートのプログラムはそのシートでしか利用しないもの、標準モジュールはいろんなシートで利用するものとききました。thisworkbookは？と思いました。また、記載場所を分けるだけで、sheet1のプログラムはsheet2で呼び出すことができるのでしょうか？



②コンボボックスの値を設定できずに困っています。
以下のプログラムです。
=================
private sub comb_change()
call change()
end sub
=================
sub change()
処理
thisworkbook.sheets(1).OLEObject("COMB").object.value=○○
end sub
=================

change()に引数としてbyvalでCOMBオブジェクトを渡しても、値を設定できませんでした。
ただ、private sub comb_change()内では、値を設定できるようです。

設定できるできないの違いはどこから来るのでしょうか？

No.2 ベストアンサー 回答者： WindFaller 回答日時： 2016/06/02 12:36

#1の回答の続きですが、ご質問の内容からこんな感じでしょうか。

添付の画像を御覧ください。

この場合は、直接、ComboBox で処理しないで、一旦、サブプロシージャを用意して、その時に排出するListIndex を利用します。特に、あまり13という数が必要ではなく、「東京都」を選んだ時点で、13という数字は確保しています。(ただし、今回は、あえて13を出すようにしています。）

それを、今度は、本来は、47個分の地域名のリストが入っているのですが、今は、北海道と神奈川だけですが、こんなリストを作ります。カンマの間にセルの範囲のアドレスを入れます。

Const AREALIST = ",c2:c189,,,,,,,,,,,e2:e62,g2:g59"
それを配列にしてあげて、ListIndex の数字で呼び出して、
e2:e62　が出てきたら、ComboBox2 のListFillRangeに入れます。
この配列にきちんと合わせなくてはなりませんが、面倒なら、VLookUp関数などを使うと良いです。

'//
Private Sub ComboBox1_Change()
　　Dim L_indx As Long
　　L_indx = ComboBox1.ListIndex + 1
　　If ComboBox1.ListCount >= L_indx And L_indx > -1 Then
　　　　Call AreaChange(L_indx)
　　End If
End Sub
Sub AreaChange(indx As Long)
Dim myLists
Const AREALIST = ",c2:c189,,,,,,,,,,,,e2:e62,g2:g59"
myLists = Split(AREALIST, ",")
ComboBox2.ListFillRange = "Master!" & myLists(indx)
ComboBox2.ListIndex = 1　'変更になったことを示すため
End Sub

'//

この場合は、#1と話が違うと思われるかもしれませんが、ComboBox1ひとつだけのためなら、そのComboBox1 の直下に置くことも多いですが、これが、他のシートにも関係してくるとなると、標準モジュールに置かざるを得ません。

サブプロシージャは、他のシートからも呼び出せないわけではありませんが、面倒な上に煩雑になります。煩雑ということは、同じものを数多く作ってしまうことです。後になって、異様にシートが重くなってから気がついた時には、手がつけられなくなっています。

ということで、私の方では、最近は、フォーム・コントロールを多く用いるようになりました。シートをコピーしても問題ないということもあります。その上、マクロは一つでたります。

この回答へのお礼

回答ありがとうございます。

お礼日時：2016/06/08 21:37

No.1 回答者： WindFaller 回答日時： 2016/06/01 00:03

こんばんは。

>change()に引数としてbyvalでCOMBオブジェクトを渡しても、値を設定できませんでした。

マクロの初心者とおっしゃっているわりには、ずいぶん難しいことをされておられるようです。

私は、あまり特殊なことは考えたことがありません。だから、あまり難しいことをお聞きになっても、分からないことだらけになるような気がしますが、分かる範囲を説明させていただきます。

シートやThisWorkbook モジュールのことをオブジェクト・モジュールと言いますが、ThisWorkbookモジュールは、家でいうと玄関口のようなものだという解釈をしています。

だから、開いた時に、真っ先にThisWorkbookモジュールが反応し、次に、標準モジュールが感知します。方法によっては、標準モジュールが感知しないというか、ないと同じことになってしまう現象さえあります。

他の特にWordやOutlookなどは、標準モジュールというのは、それほど多く使いません。

ThisWorkbook の使い方は、そのブック全体のイベントに関わるものや、IEオブジェクトイベントなどが組み込まれます。ThisWorkbookモジュールで、Sheet1などシート・イベントに関しては、共通の設定をさせることが可能です。

例えば以下のような場合、Sh は、それぞれのシートを意味しています。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Su

確か、シートの上に載っているActiveX は、シートと共に移動したりするので、孤立していなければならないわけで、つなげてしまうと、エラーの発生の恐れが出たような気がします。

>sheet1のプログラムはsheet2で呼び出すことができるのでしょうか？
できますが、そのようなイレギュラーなふつうはしないと思います。

なぜ、標準モジュールを使わないのか、よくわからないです。

また、ComboBoxの内容を、Changeイベントで書き換えるということも考えたことがありません。

例：
'Sheet1
Private Sub ComboBox1_Change()
　Call Sheet2.myChange
End Sub
'シートがアクティベートされると、コンボボックスに値が入る
Private Sub Worksheet_Activate()
Dim ar
　ar = Split("赤,白,抹茶,あずき,コーヒ,ゆず,桜", ",")
　ComboBox1.List = ar
End Sub

'Sheet2
Sub myChange()
'処理
Sheet1.ComboBox1.List(3) = "あがり"
End Sub

>thisworkbook.sheets(1).OLEObject("COMB").object.value=○○

綴りが間違っていますね。OLEObjects と複数型になります。
オブジェクトモジュールには、親(Parent)がついていますから、一般的には、ThisWorkbook は入りません。

Sheet1.OLEObjects("COMB").Object.Value=○○
これに何の意味があるのかは分かりませんが、ComboBox に現れているValue値が変わるだけだと思います。

この回答へのお礼

回答ありがとうございます。

＞Sheet1.OLEObjects("COMB").Object.Value=○○
＞これに何の意味があるのかは分かりませんが、ComboBox に現れているValue値が変わるだけだと思います。

マスタデータのシートを持っています。
マスタデータは処理に便利なように、A列に数字、B列に値と別れて保持しています。
A列：都道府県番号とB列：都道府県名です。
コンボボックスの値はファイルを開いたときにコンボボックスのfillrangeにA1:B47と入れるようにしています。
このときの表示内容は１３　東京といった感じです。

コンボボックスの値が変わったときに、
Changeイベントでコンボボックス共通のエラーチェックを行い
（例えば、関東地方を選択しているのに、大阪になっている等）
コンボボックスには　１３　東京と表示させたいです。
ただ、エラーチェックでは１３の方しか利用してないからか、意図的に値を入れないと１３のみしか表示されません。


そのため、Changeイベントで表示内容を変えたいと思っています。
すべて標準モジュールに記載しています。

お礼日時：2016/06/01 23:23