Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
開いた他ブックへのバラメータ引き渡し
投稿日時: 19/09/30 21:05:13
投稿者: S.Kos

こんにちは、みなさま。VBAて他ブックを開くことの、続編です。
 
MainBook.xlsmとSubBook.xlsmは、共にparaShを持ち、そのparaClRngに、様々なパラメータを格納します。
MainBook.xlsm起動時にユーザー認証等を済ませ、パラメータを確定します。
SubBook.xlsm起動時には、MainBook.xlsmのパラメータをそのまま渡し、以後のアレコレに用います。
(.exeで言う「引数」の感覚です)
 
いま MainBook.xlsm の中で次を発行します。それぞれについてご教示いただければ幸いです
   
Private sub SubbookOpen
  'SubBook.xlsmを開く
  Workbooks.Open ThisWorkbook.Path & "\SubBook.xlsm"
   Q1 → SubBook.xlsm側のイベント ThisWorkbook.Workbook_Open() は、この時点で発生していますか?
   
  'MainBook.xlsmのパラメータをSubBook.xlsmに渡す
  ActiveWorkbook.Worksheets(paraSh).Range(paraClRng).Value = ThisWorkbook.Worksheets(paraSh).Range(paraClRng).Value
   
end sub
このプロシージャを抜けた時点で、SubBook.xlsm側に制御(フォーカス)が移っています。
 
そのとき、SubBook.xlsmで、起動時に渡されたパラメータを元に、いくつかのサブパラメータを構成したい・・・
その処理を SubBook.xlsm側のイベント ThisWorkbook.Workbook_Open() に書いても、巧くできません。
つまり、上記Q1の回答は「ここで発生している」ように思えます。
 
であるなら、
 Q2 → SubBook.xlsmで、起動時に渡されたパラメータを元に、いくつかのサブパラメータを構成するには、どんな手順が考えられるでしょうか?
  

回答
投稿日時: 19/09/30 21:39:58
投稿者: WinArrow
投稿者のウェブサイトに移動

MainBookでSubBookを開くと
開いた時点で、ActiveworkbookはSubBookになります。
どちらがActiveWorkBookなんかは常に意識して、コードを記述する必要があります。
その都度、Activateでブックを切り替えていると、
コード自体がわかりにくくなり、効率も下がります。
 
Dim SubBook As Workbook
 
Set SubBook = Workbooks.OPen(Filename:=パス)
のような開き方をします。
 
SubBook.Sheets("○").Range("■").Value = Thisworkbook.Sheets("△").Range("◇").Value
のような記述で、ブック切替せずに、セルの操作ができます。

回答
投稿日時: 19/09/30 22:10:51
投稿者: WinArrow
投稿者のウェブサイトに移動

> ActiveWorkbook.Worksheets(paraSh).Range(paraClRng).Value = ThisWorkbook.Worksheets(paraSh).Range(paraClRng).Value
 
このコードをSubBook側に記述しているとしたら、
そのままMainBook側に記述すればよいです。
 
Thisworkbookは、コードが記述されているブックの事です。
従って、SubBook側に記述していると
ActiveWorkbookもThisworkBookもSubBookとなります。

回答
投稿日時: 19/10/01 00:45:03
投稿者: simple

Q1から答えると、それは実行されています。
 
Q2について。
Main側に集約するのが手っ取早いが、
どうしてもということなら
以下のような手があります。
 

Application.EnableEvents = False
Set wb = Workbooks.Open ThisWorkbook.Path & "\SubBook.xlsm"
Application.EnableEvents = True

' ここにパラメータ設定処理?を書く

'Sub側の処理を実行
Application.Run "SubBook!ThisWorkbook.Workbook_Open" 

-----------------------------
ちなみに、別件、旧聞に属して恐縮ですが、
「シート上に配置したActiveXコントロール」
https://www.moug.net/faq/viewtopic.php?t=78623
についてお尋ねします。
 
質問の趣旨は、
シートモジュールと標準モジュールに書いた場合の差異ということでした。
 
その点についての、貴兄の整理を改めて教えて下さい。
LinkedCellの話は、質問とは別のことであって、
そもそもの質問がどこかにブっ飛んでしまっています。
きちんとコメントしてもらえますか?
 
(1)標準モジュールで動作したんでしょうか? 
   少なくとも5回中3回程度は、エラーにはならなかったんですか?
(2)もしエラーになったとすると、エラーの原因は何だったという整理なんですか?
   LinkedCellの話は別。シートモジュールでは全回成功していたんでしょ?
 

投稿日時: 19/10/01 09:06:45
投稿者: S.Kos

simpleさん、ご教示感謝です
 
>「シート上に配置したActiveXコントロール」
>シートモジュールと標準モジュールに書いた場合の差異ということでした。
>
全てがここから始まってます。
 
標準モジュールに書いたら、5回中3回程度はOKでした。
LinkedCellに手を入れて、シートモジュールに書き直したら、巧くデキた・・・ように思いました。
 
ところが、別PCだとこれでもアウト、PCごとに、5回中3回だったり1回だったり、散々でした。
 
そこでファイルサイズが問題かな、と思い立ちました。
そのファイルは、過去の様々を一本に纏め、新規追加もあり、その時点で、
 シート数112、フォーム数82、標準モジュール数24、サイズは4メガを超えてました。
 
試しに「シート上に配置したActiveXコントロール」で引っ掛かった箇所を別ファイルに切り出すと・・・
不具合を生じていた全てのPCで、少なくともこれまでは、巧く動いています。
切り出したファイルは、
 シート数2、フォーム数2、標準モジュール数6、サイズは600Kほどです。
 
末端ユーザーの混乱回避のため、複数のファイルを一本に纏めたのに、やっぱり分割・・、ってのも如何なものか?
そこで、MainBookからSubBookを起動しては、と思い至ったしだいです。

回答
投稿日時: 19/10/01 09:30:17
投稿者: WinArrow
投稿者のウェブサイトに移動

>ランチャーのようなもの
 
この機能だけで言いますと
MainBooKをマクロブックにしなくても
SubBookを開くことは可能です。
但し、SubBookはマクロブックなので、「マクロ有効にする確認ダイアログ」が表示されます。
これは、今までSubBookを開くときとおなじかもしれませんが、
「マクロ有効にする確認ダイアログ」を表示させない対応ができていれば、表示されません。
 
 
例えば
MainBook(xlsxでも可)のSubBookの名前等をリスト化した目次シートを作成し
ハイパーリンクを設定すれば、SubBookを開くことができます。
SunBook側の
Workbook_OpenイベントにMainブックからのセル複写を組み込めばよいです。
 

Private Sub Workbook_Open()
    Me.Sheets(1).Range("A1").Value = Workbooks("Main.xlsx").Sheets(1).Range("A1").Value
End Sub

投稿日時: 19/10/01 10:06:49
投稿者: S.Kos

WinArrowさん、ご教示感謝です
 
>SunBook側のWorkbook_OpenイベントにMainブックからのセル複写を組み込めばよいです。
>
そうか、そうか!
開いた時点で制御はSubBookにあるので、MainBookを読めば良い・・
 
MinからSubへ「引き渡す」のではなく、SubからMainを「読む」のですね!
やってみましょう。
 

回答
投稿日時: 19/10/01 11:18:15
投稿者: WinArrow
投稿者のウェブサイトに移動

>開いた時点で制御はSubBookにあるので、MainBookを読めば良い・・
 
勘違いしているようなので
 
SubBook側でMainBookからデータを取得する
は、ハイパーリンクでSubBookを開いた場合の対処です。
 
MainBook.xlsm
で、Workbooks.Open(Filename:=Thisworkbook.Path & "\SubBook.xlsm")
を使ってSubBookを開いた場合は、MainBook側に組んだ方が簡便です。
 
どのSubBookをどのような方法で選択するかという
オペレーションも一緒に考える方がよいと思いますが、
SubBookは複数あるんですよね?
 
Workbooks.Open(Filename:=Thisworkbook.Path & "\SubBook.xlsm")
のところは、サブブックの名前リテラルで指定すると
複数分のコードを記述することになります。
 
 
 
 

投稿日時: 19/10/01 12:52:44
投稿者: S.Kos

WinArrowさん、重ねてのご教示感謝です。
 
>勘違いしているようなので
>
>SubBook側でMainBookからデータを取得する
>は、ハイパーリンクでSubBookを開いた場合の対処です。
>
勘違い、でしょうか・・・?
 
ハイパーリンクであろうと、Workbooks.Openであろうと、SubBookを開いた時点でSunBook側のWorkbook_Openイベントが生じているのではありませんか?
 
実際に試してみました。
SunBookのWorkbook_Openに、
  ThisWorkbook.Worksheets(paraSh).Range(paraClRng).Value = Workbooks("MainBook.xlsm").Worksheets(paraSh).Range(paraClRng).Value
とすることで、MainBookで構成したパラメータを、SubBook側で取得できているように思います。

回答
投稿日時: 19/10/01 13:21:40
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
ハイパーリンクであろうと、Workbooks.Openであろうと、SubBookを開いた時点でSunBook側のWorkbook_Openイベントが生じているのではありませんか?

 
理論的にできる/できないの話をしているわけではありません。
サブブック側にその機能を持たせるということは、
全てのサブブックを変更するということになります。
 
MainBook側に組み込めば、1ヶ所で済みます。
 
 

投稿日時: 19/10/01 13:53:23
投稿者: S.Kos

WinArrowさん、重ねてのご教示感謝です。
 
>サブブック側にその機能を持たせるということは、
>全てのサブブックを変更するということになります。
>
この点は、全く問題になりません。
複数あるSubBookはそれぞれに異なるサブ・パラメータを構成するため、どのみち様々に手を入れますので。
 
試行を繰り返す中、SubからMainを「読む」、そのこと自体は巧くできてますが、問題がひとつ・・・
 
SubからMainを「読む」とき、Mainのファイル名を指定せねばなりません。
開発途上では、Main_1.xlsm とか Main_2.xlsm などと、Mainのファイル名が定まらないことも良くあります。
 
Subの側で、自分を開いたMainのファイル名を取得するには、どんな手法があるでしょうか?
 
現状では、SubBookの直接起動への対応も含めて、以下を試しているところです。
 
Private Sub Workbook_Open()
  Dim wb As Workbook, sh As Worksheet, MainBoolk As String
   
  MainBoolk = ""
  For Each wb In Workbooks
    For Each sh In wb.Worksheets
      If sh.Name = "特定シート" Then MainBoolk = wb.Name: Exit For
    Next
    If MainBoolk <> "" Then Exit For
  Next
  If MainBoolk = "" Then
    MsgBox " 不正起動・・システム閉鎖 "
    Set wb = ThisWorkbook
    Call wb.Close(SaveChanges:=False)
  End If
 
  '起動パラメータ取得
  With ThisWorkbook.Worksheets(paraSh)
    .Range(paraClRng).Value = Workbooks(MainBoolk).Worksheets(paraSh).Range(paraClRng).Value
    .Visible = xlVeryHidden
  End With
   
End Sub
 
ここで "特定シート" は、MainBookにのみ含まれるシートの名前です。

回答
投稿日時: 19/10/01 15:45:02
投稿者: WinArrow
投稿者のウェブサイトに移動

SUB側を今迄のシステムと大幅に変更しない方が安全と思っただけです。

引用:

開発途上では、Main_1.xlsm とか Main_2.xlsm などと、Mainのファイル名が定まらないことも良くあります。

 
それだったっら、MinBook側でSBookにセットしたほうがよいのではないでしょうか?
制御の流れを「上から下へ」という方向性を持たせた方が思想統一できると思います。
 
 
 
引用:
Subの側で、自分を開いたMainのファイル名を取得するには、どんな手法があるでしょうか?
 

 
方法論としては、設計者のシステムデザイン次第と思います。
貴方が試している通りでよいではないでしょうか?

回答
投稿日時: 19/10/01 19:10:08
投稿者: simple

Sub test()
    Dim wb As WorkBook
    Application.EnableEvents = False
    Set wb = Workbooks.Open ThisWorkbook.Path & "\SubBook.xlsm"
    Application.EnableEvents = True
    ' ここにパラメータ設定処理?を書く
    'Sub側の処理を実行
    Application.Run "SubBook!ThisWorkbook.Workbook_Open" 
End SUb

私があえて、上記を提示したのは次の理由です。
・SubBookの初期処理としては、あくまで主たるパラメータ設定までとしたい。
・サブパラメータの設定処理自体は、SubBook自体にやらせたい、
という意図かと思ったからでした。
そういう意図でも無かったんでしょうか。
 
----------------------
くどいですが、前回スレッドの件。
私は、前回の件と、今回の質問の関係やシートの数だとか、
そういうことを尋ねたわけではありません。
 
以下の肝心な質問に是非ともお答えください。
 
> 標準モジュールに書いたら、5回中3回程度はOKでした。
ほんとうですか?
  For Each obj In OLEObjects
    If TypeOf obj.Object Is MSForms.OptionButton Then obj.Delete
    If TypeOf obj.Object Is MSForms.CheckBox Then obj.Delete
  Next
は、標準モジュールではコンパイルエラーになると思いますが。
OLEObjectsがハイライトして、エラーになるはずです。
 
エラーにならないのなら、あなたが標準モジュールで動かしたというコードと、
提示されたコートは別のもののはずです。
 
こうした会話はキャッチボールなんですから、
きちんとしたものを提示したうえで、質問してもらえませんか、申し上げたが、
それは無視されたうえ、
標準モジュールとシートモジュールの差異に言及されずに、
会話を一方的に打ち切られたのは、とても残念です。
 
それで、標準モジュールに書くこととシートモジュールに書くことの差異に
ついてのあなたの見解をお尋ねしています。お答えをお待ちしています。

投稿日時: 19/10/01 20:52:09
投稿者: S.Kos

simpleさん,重ねてのご教示ありがとうございます。
 
6個上で、またひとつ上で再度、記していただいたコードは、WinArrowさんのご教示に沿った「試み」がひと段落した後、改めて取り組むつもりでした。
それはそれとして・・・
 
>それで、標準モジュールに書くこととシートモジュールに書くことの差異に
>ついてのあなたの見解をお尋ねしています。お答えをお待ちしています。
>
明確な見解があれば、ここで訊ねてはいませんよ! 判らないから、問うています。
 
>OLEObjectsがハイライトして、エラーになるはずです。
>
古い(と言っても9/11ですが)ファイルを開いて試しました。
 
'Clear AnserSh
Public Sub ClearDisp_QusAnsSH()
  Dim obj As OLEObject
   
  With Worksheets(AnswerSh)
    .Unprotect ""
       
    For Each obj In .OLEObjects
      If TypeOf obj.Object Is MSForms.OptionButton Then obj.Delete
      If TypeOf obj.Object Is MSForms.CheckBox Then obj.Delete
    Next
       
    .Protect Password:=""
  End With
End Sub
と、標準モジュールに書いていますが、コンパイルエラーにはならず、動きます。
(前の問では With Worksheets(AnswerSh) を書いてないじゃないか! と怒らないでください。
 標準モジュールから特定のシートを扱うにはコレが必須であることなど、暗黙の了解事項だと思います。)
 
改めて連続起動してみると、三回目でEXCELが固まりました。
 
前の問を投げたとき、そしてその後、次のように考えました。
・そう言えば、どっかのサイトに、ActiveXを含むシートに書け、とあったよなぁ・・
・リンクセルの表示ももたついてる・・
・では、リンクセルに手を入れて、シートに移してみよう
 
その結果、開発環境では巧く動きましたが、他PCではダメなので、
・ファイルサイズかなぁ・・・
 
で、今に至っています。

回答
投稿日時: 19/10/01 21:22:43
投稿者: simple

> 前の問では With Worksheets(AnswerSh) を書いてないじゃないか! と怒らないでください。
> 標準モジュールから特定のシートを扱うにはコレが必須であることなど、暗黙の了解事項だと思います。

それはあなたの勝手な理屈です。
Withがないだけでなく、 ドットも頭についていません。
 
今後のこともあるので、繰り返し書いておきます。
> Excelのおかしな振る舞いについて議論するのであれば、
> ・変数をきちんと宣言した
>・Sub から End Subまでを書いたもの
> をきちんと提示したほうがよいと思いますね。

投稿日時: 19/10/02 13:05:54
投稿者: S.Kos

simpleさん,重ねてのご教示ありがとうございます。
 
>それはあなたの勝手な理屈です。
>
読み手にも様々なレベルの人がいる、と考えれば、確かにご指摘の通りでしょう。
 
以降、
>>・Sub から End Subまでを書いたもの
>> をきちんと提示したほうがよいと思いますね。
>
を念頭に置いて、投稿します。
 
さて、二度に渡り記していただいたコードについて、以下お訊ねします。
 
Sub test()
    Dim wb As WorkBook
    Application.EnableEvents = False
    Set wb = Workbooks.Open ThisWorkbook.Path & "\SubBook.xlsm"
    問1 → イベントがFalse故この時点では、SubBook.xlsmのWorkbook_Openは起動していない、と考えて良いですか?
     
    Application.EnableEvents = True
    ' ここにパラメータ設定処理?を書く
    問2 → システムのフォーカスは、未だMainBook.xlsmにあるので、
           Workbooks("SubBook.xlsm").Sheet(◆).Range(★).value = Workbooks("MainBook.xlsm").Sheet(◇).Range(☆).value
           と書ける、と考えて良いですか?
     
    'Sub側の処理を実行
    Application.Run "SubBook!ThisWorkbook.Workbook_Open"
    問3 → このタイミングでSubBook.xlsmのWorkbook_Openが起動するが、Sheet(◆).Range(★).valueにはパラメータが書かれているので、サブ・パラメータが算出できる、と考えて良いですか?
     
End SUb
問4 → このプロシージャを抜けた時点で、システムのフォーカスはSubBook.xlsmにありますよね?
 
以上、改めてご教示いただければ幸いです。

回答
投稿日時: 19/10/02 14:09:40
投稿者: simple

きちんとコード(しかもVBEからのコピペ)を
提示いただく意義:=両者の情報連携のミスマッチの回避
・質問者が勝手に省略されても
   そのことがトラブルの原因であることは経験上多数あり。
    そちらのPCが見えているわけでは無い。
・質問者のレベルや環境等不明なことが多い。
・インプットミスか本来のミスか判別不能。
これらの時間的労力的ロスを回避するためには
不可欠と考える。
 
問1 EnableEventsの意味を調べられたい。
問2,4. 現在どのブックがアクティブであるかは
処理とは無関係です。
非アクティブであってもいくらでも操作可能です。
というより、ブックやシートを逐一選択せず
実行することがスキル向上の要諦です。
複数のプロセスが別に動いているわけではなく
一つのアプリケーションの元で実行されるわけで、
処理の同期的実行はExcel側の任務です。原則的には。
問3 実行して確認されたい。

回答
投稿日時: 19/10/02 14:12:19
投稿者: WinArrow
投稿者のウェブサイトに移動

横から失礼
 
> 問1 → イベントがFalse故この時点では、SubBook.xlsmのWorkbook_Openは起動していない、と考えて良いですか?
 
ここでの、「Application.EnableEvents = False 」は、
SubBookの起動を制御するものではなく、
WorkBook_Openイベントプロシジャを実行させないためのものです。
ですからSubBookは起動しています。
 
ブックの"フォーカス"にこだわっていますが、
Activeworkbookを用いなければ、ブックの"フォーカス"を、気にする必要がありません。
 
 
質問者さん側で説明が不足していると思われる部分
○複数のサブブックがあるという前提で、
  各々のサブブックは、起動パラメータは、同一でしょうか?
  同一だったら、起動パラメータをメインブック側でサブブックにセットする
  同一でない場合は、サブブック側で、取得する(メインブック側の特定セルを参照する)
 という論理が成り立ちます。
 ここをきちんと説明したほうがよいのでは?
 
 

回答
投稿日時: 19/10/02 14:19:18
投稿者: WinArrow
投稿者のウェブサイトに移動

もう一つの質問
 
今迄はメインブックがない状態(サブブックだけ)で運用していたんですよね?
 
その時には、サブブック側では、起動パラメータはどのように取得していたんですか?
説明の文章の中では、"さまざま"とか"いくつか"とか、書かれていますが、
コードの中では1つしか出てきていません。

回答
投稿日時: 19/10/02 14:53:54
投稿者: mattuwan44

横入りすみません。
 
>MainBook.xlsmとSubBook.xlsmは、共にparaShを持ち、そのparaClRngに、様々なパラメータを格納します。
paraShとかparaClRngって、なんですか?
 
>暗黙の了解事項だと思います。
それは書き手の勝手な理屈ですね。
読めばわかると思っていると思いますが、
意図してそう書いたのか、間違ってそう書いたのか、
読み手には判断ができませんし、
書き手が作った変数名に何が入るのか(シートオブジェクトなのかシートの名前なのかとか)、
コードを読んでわかってくれというのは、少し無理があります。
 
>共にparaShを持ち
同じシート名のシートが存在し
 
ということですかね?急にわけのわからん名前が出て来ても読み手は困惑します。
 
あと、
>Workbook_Open
あっちのブックを開いたらあとは勝手にあっちのマクロが
動くであろうというスタンスは個人的に嫌いです。
意図したブックの意図したプロシージャを意図して起動したいです。
 
さて本題。
テーマが、
>開いた他ブックへのバラメータ引き渡し
 
なので、こういうことがしたいのではないか?という想像です。
 
<メインのブックのコード(標準モジュール)>

Sub メイン()
    Const cName As String = "テスト_サブ.xlsm"
    Dim Path As String
    Dim wbkSub As Workbook
    Dim sTargetAddress As String
    
    Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & cName
    sTargetAddress = ThisWorkbook.Worksheets("Sheet2").Range("B1:B10").Address(, , , True)
    
    Set wbkSub = Workbooks.Open(Path)
    Application.Run wbkSub.Name & "!Test_Sub", sTargetAddress
End Sub

 
<テスト_サブ.xlsmブックのコード(標準モジュール)>
Sub Test_Sub(ByVal sRangeName As String)
    ThisWorkbook.Worksheets(1).Range("A1:A10").Value = Application.Range(sRangeName).Value
End Sub

 
>'MainBook.xlsmのパラメータをSubBook.xlsmに渡す
> ActiveWorkbook.Worksheets(paraSh).Range(paraClRng).Value = ThisWorkbook.Worksheets(paraSh).Range(paraClRng).Value

難しく考えなくても、受け取る側は、送り手のことなどは考えなくても、よいかと思いますよ。

回答
投稿日時: 19/10/02 18:49:17
投稿者: simple

追記します。
私の憶測だが、質問者さんは、Main.xlsmとSubBook.xlsm を
あたかも exeファイルの実行と同じように考えているのではないか。
 
問1,3などはご自分で確認すれば簡単に分かることですよね。
SubBook.xlsmのWorkbook_Openのなかに
Debug.Print "SubのWorkbook_Openが実行された"
などとしておき、
パラメータ設定のタイミングでも、Debug.Print "なんたら"
とすれば、いつWorkbook_Openが実行されたかはわかりますよ。
 
にもかかわらず重ねて質問しているのは、
Mainの中でEnableEventsを変更したのに、
なぜそれがSubBookのイベントプロシージャの振る舞いに影響するのか。
SubBookは別の実行なんだろう?
という疑いを持っているのではないか。
 
そうではないんです。
全体がひとつのExcelアプリケーションであり、その配下にMainもSubBookもあるんですよ。
別にプロセスが2つ立ち上がるわけでもないんです。

投稿日時: 19/10/02 19:56:17
投稿者: S.Kos

simpleさん、重ね々々のご教示ありがとうございました。
 
>私の憶測だが、質問者さんは、Main.xlsmとSubBook.xlsm を
>あたかも exeファイルの実行と同じように考えているのではないか。
>
ドンピシャ、ですね!
コマンドラインに様々なスイッチとともに引数を記す、その感覚でいました。
 
>全体がひとつのExcelアプリケーションであり、その配下にMainもSubBookもあるんですよ。
>
この大枠が判ってやっと、みなさんのご指摘が腑に落ちます。
 
みなさま、ご教示のほどありがとうございました。
ここで閉じます