[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『For〜Eachなどで任意のブックをアクティブにしたい』（ろでます）
度々度々お世話になっております、ろでますです。
誠に恐縮ではございますが、またご質問させてください。
For〜each文などで任意のブックをアクティブにしたいのですが、やり方がわかりません。
具体例を申し上げますと、現在ブックとして3つのブックを開いているとします。
ブック名は「解析.xlsm」「データ.xlsm」「結果.xlsm」とします。
マクロ本体は「解析.xlsm」にあります。
このマクロでFor〜each分を動かして「データ.xlsm」をアクティブにしたいと考えています。ただし、この「データ.xlsm」は使用するユーザーが任意でファイル名をしたいようにしたいと考えています。
なので以下の様な構文は取りたくないと考えています。
Dim wb as Workbook
For Each wb In Workbooks
if wb.name = "データ.xlsm" then
Workbooks("データ.xlsm").Activate
End if
Next wb
この方法だと、ファイル名が固定されてしまいます。
なので以下のようなことはできないでしょうか。
Dim wb as Workbook
For Each wb In Workbooks
if wb.name <> "解析.xlsm" And wb.name <> "結果.xlsm" then
「いまfor〜eachが参照しているブックをアクティブにする構文」
End if
Next wb
のようなやり方はないでしょうか？
ご存知の方いらっしゃいましたらご教授のほどよろしくお願い申し上げます。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
>「いまfor〜eachが参照しているブックをアクティブにする構文
開いているBookが 解析 と 結果 以外に一つしかないのであれば
wb.Activate でいいと思います。
（tora） 2014/08/19(火) 14:47
例として、ユーザーが「会計.xlsm」というファイルを開いているとして、その状況で「解析.xlsm」「データ.xlsm」「結果.xlsm」の3つのファイルを開き、「解析.xlsm」の中にあるマクロ
Dim wb as Workbook
For Each wb In Workbooks
if wb.name <> "解析.xlsm" And wb.name <> "結果.xlsm" then
「いまfor〜eachが参照しているブックをアクティブにする構文」
End if
Next wb
を実行すると、
「会計.xlsm」がアクティブになるのか、「データ.xlsm」がアクティブになるのかは分からないと思います。（厳密にいうと、開いた順でアクティブになるのでしょうか、この場合だと、「会計.xlsm」？）
これを回避するには、私的には、マクロの最初で、開いているブックを全て閉じるマクロを走らせた後に本マクロを走らせるしか方法がないのかなと思っているのですが、それだと、ユーザーがが多くのブックを開いていた場合ユーザビリティが低下してしまうなと考えています。
もしこれを回避できる方法がございましたら、再度ご教授をお願いできませんでしょうか。
よろしくお願い申し上げます。
（ろでます） 2014/08/19(火) 16:33
では改めて、アクティブにしたいブックはどれなのかの説明をお願いします。 ファイル名は、データ.xlsmとは限らないわけですよね。 （cai） 2014/08/19(火) 16:50
＞こちらが意図していない任意のエクセルファイル こちらが意図しない任意って面白い表現ですね。 「任意」ってその人の自由意志に任せる、ひいてはその人に選んでもらうことが前提のはずです。 なので「こちらが意図しない」と「任意」はまるっきり逆のことを言っているような？ 私なりの解釈なので、間違っているかもしれませんが。
ともあれ、私がこのような処理を作るときは、 １） 使う人にダイヤログでファイルを選んでもらい、 ２） 選んだファイルが開かれていないか確認し ３−１）開かれていたらそのファイルをそのまま使用 ３−２）開かれていなければ選んだファイルを開く このように組みます。
若しくは、このままコードのコードを生かすなら、使用した人に判断をゆだねるコードを追加します。 If Msgbox(wb.Name & "を処理します。よろしいですか？",vbYesNo) = vbYes Then wb.Activate : Exit For
（稲葉） 2014/08/19(火) 16:55
例えば wb.Sheets("Sheet1").Cells(1, 1).Value のように、ブックとシートを明示するのが良いと思います。
（???） 2014/08/20(水) 08:34
＞cai様
＞稲葉様
＞???様
＞では改めて、アクティブにしたいブックはどれなのかの説明をお願いします。
＞ファイル名は、データ.xlsmとは限らないわけですよね。
すいません、私の書き方が不足しておりました。
ファイル名が動的に変わるとお考えください。「データ.xlsm」のファイル名をユーザが任意で変更可能だということです。私の作ったマクロを自分だけではなく、多くの方に使ってもらう場合、「データ（東京版）.xlsm」等にファイル名を変えられてしまう可能性があります。こうなってしまった場合、マクロ内でブック名固定しまうような構文例えば
Workbooks("データ.xlsm").Activate
という構文は取りたくないということです。
やりたいことは
Workbooks(「変数かつ、変えられてしまったファイル名」).activate
という構文です。
当然、「変えられてしまったファイル名」というのはマクロ側では把握できないので、稲葉様のコメントにあるように私も
＞１） 使う人にダイヤログでファイルを選んでもらい、
＞２） 選んだファイルが開かれていないか確認し
＞３−１）開かれていたらそのファイルをそのまま使用
＞３−２）開かれていなければ選んだファイルを開く
という手法を用いております。
ただ、この方法でも、ユーザーがほかのエクセルファイルを同時に開いていた場合、
先の私のコメント内にもあります通り、For〜Each文内でwb.activateをすると、その「ほかのエクセルファイル」をActiveにしてしまう可能性があります。（と申しますか、時と場合、開いた順等によ入り必ず「ほかのエクセルファイル」がActiveになるはずだと考えております）
＞こちらが意図しない任意
すいません、これは私の語学力不足で申し訳ありません。詳しく申し上げますと、上記に書いているようなことで、私はほかのエクセルファイルを開いていてほしくない（つまり意図しない）のに、ユーザーは開いている（何らかのエクセルファイル、つまりユーザーが任意）という意味で書かせていただきました。
???様のコメントの手法も考えてはいたのですが、マクロ本体のある「解析.xlsm」はファイル名は変えてもいいけれども、中身は変えられないよう（変えるとマクロが動作しなくなるため）にシートの保護をかけようと考えているため、セルに対しユーザーが動的に変わるファイル名「データ.xlsm」や「データ（東京版）.xlsm」等を入れてもらうのもちょっと無理かなと考えています。
大変長文になってしまい誠に申し訳ないのですが、私がやりたいことの概要をもう少し詳しくお伝えさせていただきます。（あくまで例です）
「解析.xlsm」には解析したい物品がなんなのか、そしてそれを月単位なのか、半期単位なのか、年度単位なのか等をドロップダウンリストからユーザに選択させるようにしています。そして、「解析」ボタンを作り、ボタンに本マクロを登録しています。
ボタンを押すと、月単位で詳細を入力しているデータベースのようなエクセルファイル（これが「データ.xlsm」です）をユーザーが選択して開きます。（この時に稲葉様の手法を用いています）
データベース用のファイルは月単位解析ならば1つ選んで開きます。年度単位ならば12個のファイルを選択して開いてもらいます。
その上で、開いたファイルの特定のセルから値を拾い変数に代入したうえで、「結果.xlsm」の特定のセルに代入するという内容です。
それならば、ユーザーが開いたファイル名を変数（配列）に代入してそれを順にActiveにすればいいじゃないかという話になるのですが、実ををいうと、ファイルを開く部分はApplication.runで別に本マクロから呼び出しているので、それを渡すことができないのです。
マクロの構成はこうなっています
[1]マスターの解析マクロ
┃
┃━[2]Application.runでファイルを開くマクロ（ユーザーが選択した複数ファイルをいっぺんに開く）
┃━[3]BApplication.runで開いたファイルからデータを拾い、「結果.xlsm」に値を代入するマクロ
┃
完了
という方式で考えています（と申しますが、作っていて詰まりました）
[2]で返り値として開いたファイル名を[1]に渡し、そこから更に[3]にそれを引数として渡せればそれで解決するのですが、Application.runは返り値を1つしか取れないと私は認識しています。
なのでその手法がとれません。
なので、仕方なく、マクロ[3]でFor〜Eachで開いているマクロををアクティブにして、そこから値を拾おうと考え、そして実際マクロを組んでいるとい先の私のコメントにあるような壁に当たってしまい、自分の頭ではどうしても解決ができなく、こちらでご質問させていただいた次第ございます。
すいません、私の不心得な語学力でご理解いただけましたでしょうか。
本当に、誠に長文になってしまい誠に申し訳ございません。
何か解決策等ご教授いただけます方がいらっしゃいましたら何卒よろしくお願い申し上げます。
（ろでます） 2014/08/20(水) 11:34
データ.xlsm のファイル名を データ（東京版）.xlsm に変更しているとします。 また、同時にデータ（神奈川版）.xlsm、データ（千葉版）.xlsmを開いているとします。 この時アクティブにしたいのがデータ（東京版）.xlsmだというのは、どこで判断しますか。 （cai） 2014/08/20(水) 12:42
＞という方式で考えています（と申しますが、作っていて詰まりました） ＞[2]で返り値として開いたファイル名を[1]に渡し、そこから更に[3]にそれを引数として渡せれば ＞それで 解決するのですが、Application.runは返り値を1つしか取れないと私は認識しています。 ＞なのでその手法がとれません。
そもそも別のブックのマクロ（[2]Application.runでファイルを開くマクロ）でファイルを一気に 開く発想がわけがわかりません。 最初に起動したコードの中に記述すれば済むのでは？
つべこべ言わず、マクロAとマクロBを含むすべてのコードを開示した方が解決が早いような。
（稲葉） 2014/08/20(水) 13:00
>「ほかのエクセルファイル」をActiveにしてしまう可能性があります。 ろでますさんのVBAコード対象ブックとそうでないブックを区別したいという事なら、 VBAコード対象ブックには、どこかに新規作成時に識別コードを埋め込んでおく (例えば、カスタムドキュメントプロパティや隠しシートの適当なセル）その識別子の有無で VBAでの処理対象ブックか否かを判断します。
２ >Application.runは返り値を1つしか取れないと私は認識しています。 Functionプロシジャーにしても 複数のブック情報が返せないという事でしたら、 これは、配列やコレクションを返り値として設定すれば、複数の値を返すことは出来ます。
そうではなく、Functionでは、別の意味のあるデータを返り値として使用しているから・・・、 というなら、パラメータをオブジェクト化（コレクションでもいいです）し、 そのプロパティとして返り値を設定する という方法だってあります。
以上、ちょっと気が付いた点を記述しました。
（ichinose@食事終わり） 2014/08/20(水) 13:12
＞cai様
＞データ.xlsm のファイル名を データ（東京版）.xlsm に変更しているとします。
＞また、同時にデータ（神奈川版）.xlsm、データ（千葉版）.xlsmを開いているとします。
＞この時アクティブにしたいのがデータ（東京版）.xlsmだというのは、どこで判断しますか。
この場合、データベース用のファイルのワークシートの中身まで見ています。。例えば「データ（東京版）.xlsm」の場合、「場所」というセルがあり、その横に「東京版」と書かれています。ここを見て、このファイルは東京版だと判断するようにしています。もちろん、ここを改ざんされるとどうしようもないのですが・・・。
なので、「データ（千葉版）」の中にある場所の横のセルに「東京版」とい書かれているとこれはもう判断することができませんが、そんなことをする人間はいないし、禁止しているという前提で作成をしています。
じゃあ、ファイル名も命名規則を作成しておくとか、禁止事項を作成しておく等で判断するようにすればいいという話にもなりますが、これはこちら側の勝手な事情で申し訳ないのですが、できないようになっています。
＞稲葉様
Application.runでマクロを分けているのは、その他でもマクロ[2]を使いまわしたりするが為です。
Funcionで渡す引数により、何のファイルを開くかを判断し、マクロ[2]が引数に見合ったファイルオープン指示をユーザーに選択させるようにしているためです。
マクロ全文公開は、可能であればやりたいのですが、情報保護をしないといけないところもあり、そこを書き直したり、削除したりするのに時間を相当とられそうなので、誠に勝手ながら差し控えておりました。申し訳ござません。
＞ichinose@食事終わり様
2でいただいた情報が非常に関心を持ちました。実際試してみたいと思います。
つまりは以下のようなことができることでしょうか？
マクロ[1]
sub
dim a(9) as long
i = 0
For i = 0 to 9
a(i)=application.run(マクロ[2]) next i end sub
マクロ[2]
Function マクロ[2]() as long
dim j
for j = 0 to 9 マクロ[2] = j next j end Function
こうすることによって、マクロ[1]の配列変数a(0)には整数「0」、a(1)には整数「1」・・・a(9)には整数「9」が入ると考えてよろしいでしょうか？
これができるようであれば解決できるような気がします。
（ろでます） 2014/08/20(水) 14:24
＞（略）その他でもマクロ[2]を使いまわしたりするが為です。 としているのに、 ＞（大幅省略）こうすることによって のところは、予備先の変更を加えているように思うのですが・・・ 使いまわすのに変更しちゃっていいの？
なら
呼び元（RUN1.xlsm）のコードはこのようにして
'―――――――――――――――――――――――――――
Option Explicit
Dim WBS 'モジュールレベルでVariant型の変数を宣言
Sub run_test1()
Dim WB
Application.Run "RUN2.xlsm!run_test2", "マクロAだよ"
For Each WB In WBS
Debug.Print Workbooks(WB).Name
Next WB
End Sub
Function GetWbs(ByVal WBN As String)
WBS = Split(WBN, ",")
End Function
'―――――――――――――――――――――――――――
呼び先（RUN2.xlsm）のコードをこうすれば
'―――――――――――――――――――――――――――
Option Explicit
Sub run_test2(ByVal マクロ分岐 As String)
Dim WBN As String
Dim OP_WB As String
Do
OP_WB = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OP_WB = "False" Then Exit Do
WBN = WBN & Dir(OP_WB) & ","
Workbooks.Open OP_WB
OP_WB = ""
Loop
If マクロ分岐 = "マクロAだよ" Then
If Len(WBN) > 0 Then Application.Run "RUN1.xlsm!GetWbs", Left(WBN, Len(WBN) - 1)
End If
End Sub
'―――――――――――――――――――――――――――
戻り値ではなく、引数として値渡しができるんじゃないですかね？ （稲葉） 2014/08/20(水) 15:07
これで、壁を越えられそうです。
稲葉様、そしてコメントいただきました皆様、本当にありがとうございました。
私もまだまだ初心者です、また何か伺うことがあることもあると思います。
その際は、またご機会ございましたら、是非ともよろしくお願いいたします。
私も早く教えられる側から、教えられる側になれるように頑張りたいと思います。
本当にありがとうございました。
（ろでます） 2014/08/20(水) 16:22
なんか腑に落ちませんが・・ 私が説明するのもおこがましいichinoseさんの「コレクションにしてもいいし」の部分はこのようにも 書くことが出来ると思います。
呼び元
'―――――――――――――――――――――――――――
Sub FUNC_TEST_CALL()
Dim WB As Collection
Dim B
Set WB = Application.Run("RUN2.xlsm!FUNC_TEST")
For Each B In WB
Debug.Print B(0).Name
Debug.Print B(1)
Next B
End Sub
'―――――――――――――――――――――――――――
呼び先
'―――――――――――――――――――――――――――
Function FUNC_TEST() As Collection
Set FUNC_TEST = New Collection
Dim OP_WB As String
Do
OP_WB = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OP_WB = "False" Then Exit Do
Workbooks.Open OP_WB
FUNC_TEST.Add Array(Workbooks(Dir(OP_WB)), OP_WB)
Loop
End Function
'―――――――――――――――――――――――――――
17:14いらないところ削除・・
Collectionにして、Collectionの要素を更に配列にすれば、 Collectionの要素0の配列の要素0に1つ目のWorkbookオブジェクト、配列の要素1に1つ目のパス Collectionの要素1の配列の要素0に2つ目のWorkbookオブジェクト、配列の要素1に2つ目のパス を持たせるなどもできる、ということがおっしゃりたかったことだと思います。 （稲葉） 2014/08/20(水) 17:04
正直なところを申し上げまして、私は今までコレクションを使ったことがありませんでした。（存在とどういうものかは知ってはいましたが）
なので、ichinoseさんの「コレクション」というコメントには正直、「自分にできるだろうか」と頭をもたげて先ほどまでコレクションに関してネットでいろいろと調べておりました。
まだ、理解が不足してはいるとは思いますが、稲葉様のコメントを参考にコレクションを使用してのマクロ作成も試みてみようと思います。
また、分からないことがありましたら追加でご質問させていただくことがあるかもしれませんが、その際は差し支えなければご教授いただけましたら幸いです。
（ろでます） 2014/08/20(水) 23:08
>Function FUNC_TEST() As Collection これが大まかなインターフェースとして賛成です。
Function FUNC_TEST() As Collection
dim bk as workbook
Set FUNC_TEST = New Collection
Dim OP_WB As String
Do
OP_WB = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OP_WB = "False" Then Exit Do
set bk=Workbooks.Open(OP_WB)
FUNC_TEST.Add bk,bk.name
Loop
End Function
なんて私は考えていましたが、いずれにせよこういう場合のインターフェースに関しては、 よくよく検討します。 汎用プロシジャーのインターフェースってついつい その時作成しているプログラムの仕様に都合よいように考えてしまいますが、そうではなく色んな場面で使用することを想定しながら 決定することが必要ですよね！！
因みに私は、ほとんど Application.Runメソッドは使いません。他のブック（ほとんどアドインファイルですが）のマクロを呼び出す場合には、オブジェクトを介してメソッドとして、呼び出すような仕様にしています。
例えば、 Thisworkbookのモジュール(a.xlsm)に
function 割り算(byval a as double,byval b as double,答え as double) as boolean
割り算=false
if b<>0 then
答え=a/b
割り算=true
end if
end function
これをb.xlsmから呼び出す場合は、
sub test()
dim ans as double
if workbooks("a.xlsm").割り算(10,2,ans) then
msgbox "10/2=" & ans
else
msgbox "割り算できません"
end if
end sub
なんていう仕様です。
Application.Runメソッドは、引数が値渡しでしか使えないのが不便です。
a.xlsmの標準モジュールに
function 割り算(byval a as double,byval b as double,答え as double) as boolean
割り算=false
if b<>0 then
答え=a/b
割り算=true
end if
end function
b.xlsm側で
Sub test2()
Dim ans As Double
If Application.Run("a.xlsm!割り算", 5, 2, ans) Then
MsgBox "10/2=" & ans
Else
MsgBox "割り算できません"
End If
End Sub
これでは、引数ansに正しい結果が入っていません。
オブジェクトを介して使用すれば、引数でも値を戻すことができますので・・。
Application.runでも引数を工夫すれば、値を返すことも可能ですが、 今回は、既にFunctionを使用した最良な投稿が 稲葉さんから提示されていますので 省略します。
（ichinose) 2014/08/21(木) 01:36
とりあえず、今はこれだけをお伝えしたくてコメントさせていただきました。
マクロの理解は今からさせていただきます。
ですが一言だけ本文にコメントを・・・
＞オブジェクトを介してメソッドとして、呼び出すような仕様にしています。
これが私にとっては未知の世界なので、マクロ自体は作ることができても、理解やデバッグ等におそらく時間がかかるかもしれません。私の頭は構造体プログラミングを少々かじった程度で止まっているので、オブジェクト指向はまさに未知の世界です。
何とか頑張ってみます！。
（ろでます） 2014/08/21(木) 09:40
＞Set WB = Application.Run("RUN2.xlsm!FUNC_TEST")
ここで、WBのオブジェクトの実態を作りつつ、その実態はどこにあるのかというと「Application.Run("RUN2.xlsm!FUNC_TEST")」にありますという形でオブジェクトを作成している。
そして、
＞Function FUNC_TEST() As Collection
で、このFunc_TESTはコレクションですよ（つまり、配列を格納するためのプロシージャーであると宣言
そこから
＞Set FUNC_TEST = New Collection
ここでFUNC_TESTのオブジェクトの実態を作成する。
そして
＞FUNC_TEST.Add Array(Workbooks(Dir(OP_WB)), OP_WB)
で、このオブジェクトに対し、ワークブック名と、フルパス付きのワークブック名をItemとしてコレクション配列に格納する。そして、この時点で、オブジェクトWBにはそのコレクション配列がそのまま渡される。（ここは文字列型ではなく、ワークブックオブジェクト型としている理解でよろしいでしょうか？）
という理解で間違っていないでしょうか？
あと、私の知識では理解できない点が一点ございまして、これもご教授いただけませんでしょうか。
＞Dim B
では配列宣言をしていないのに、なぜ
＞Debug.Print B(0).Name
＞Debug.Print B(1)
という形で配列として使用できているのかがどうしても理解できません。
VBの特性として、変数の自動変換などがありますが（たとえば文字列型で宣言しているのに、整数型として認識したりできる様な事を申し上げています）、これに近いものなのでしょうか？
明示的に
Dim B() as Workbook
のような宣言をして、さらに
Redim
などを使って動的配列宣言をしないといけないと私は理解をしていたのですが、なぜ、これで動くのか。
それが理解できませんでした。
誠に申し訳ありませんが、ご教授願えませんでしょうか。
（ろでます） 2014/08/21(木) 11:46
Bに関しては、For eachで検索してみてください
（稲葉） 2014/08/21(木) 13:00
＞＞FUNC_TEST.Add Array(Workbooks(Dir(OP_WB)), OP_WB)
~~~~~~ ~
＞（中略）文字列型ではなく、ワークブックオブジェクト型としている理解でよろしいでしょうか？）
この部分ですが、下線を引いた部分、CollectionのItemの「中に」更にArrayで配列を作っています。
ですので、For each B in FUNC_TESTの「B」は「Workbookオブジェクトではなく、配列」である
必要があります。
既に調べられたと思いますが、ヘルプの抜粋です。
==ヘルプ引用== element 必ず指定します。 コレクションや配列の各要素を繰り返す変数を指定します。 1)コレクションの場合、引数 element にはバリアント型 (Variant) 変数、総称オブジェクト型変数、 または任意の固有オブジェクト型のオブジェクトの変数を指定できます。 2)また、配列の場合は、引数 element にはバリアント型のみ指定できます。 ==ここまで==
ここで述べられている「コレクション」は単一のコレクションオブジェクトではなく、Workbook「s」や Worksheet「s」などの固有オブジェクト型を指しています。 ですのでろでますさんが最初に提示されたコードでも、wbの変数にWorkbook型が当てられていますが、 wb As Objectでも、wb As Variantでも、ヘルプ 1)の通りコードとして成立します。
一方、私が提示したコードでは、B As ObjectもB As Workbookも当てはまりません。 それは先に述べたとおり、コレクションオブジェクトの中は「配列」だからです。 ヘルプの 2)にはバリアント型のみ指定出来るとありますので、B (As Variant）と宣言しています。
＞FUNC_TEST.Add bk, bk.Name しかし、ichinoseさんの場合は、コレクションオブジェクトの中身（Item）はWorkbookですので、 B As Workbookで成立します。
・・・という説明で納得頂けますでしょうか？ 私もVBAしかやったことなく、歴も2年くらいでしっかり勉強したわけではないので間違ったことを 言っている可能性のほうが高いのですが・・・。
諸先生方、間違ったこと言ってましたら訂正願います・・・。
（稲葉） 2014/08/22(金) 09:21
＞ ・・・という説明で納得頂けますでしょうか？
はい！、大変よく理解できました。私の方も昨日Bの中身がなんなのかをTypeName関数などを使って調べたりして、Variant型なんだなとのところまでは把握できていましたが、稲葉様の説明でほぼ完全に理解できたと思います。
動作仕様を考えて、マクロの作成をしたいと思います。
＞ 私もVBAしかやったことなく、歴も2年くらいでしっかり勉強したわけではないので間違ったことを言っている可能性のほうが高いのですが・・・。
私は大体1年くらいなのですが、本も見たこともなければ研修を受けたこともありません。
やりたいことや関数をネットで調べて、右往左往しながらマクロを作ったりしているので身に付きません。前に作ったマクロのコードでさえ忘れて、同じサイトを何回も見直して調べなおしている始末です。
それを考えると稲葉様のVBAだけで歴2年でこのスキル力は正直すごいと思いますよ。
でわ、早速いまから本マクロの方の作成等に着手したいと考えておりますので、その際また何かありましたら、ご指導ご教授の方何卒よろしくお願い申し上げます。
（ろでます） 2014/08/22(金) 11:08
うまくいかないマクロが以下です。
Option Explicit
Sub 解析()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Dim OpenFileName As Collection
Dim TMP(1) As Variant 'ここでエラーが出ます
Set OpenFileName = Application.Run("ファイルオープン")
TMP(0) = OpenFileName
Debug.Print TMP(0).Name
End Sub
Function ファイルオープン() As Collection
Dim OPN As String
Dim WB As Workbook
Set ファイルオープン = New Collection
OPN = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OPN <> "False" Then
If Dir(OPN) = ThisWorkbook.Name Then
MsgBox "同じファイルを開いています" & vbCr & "処理を中断します", vbCritical
Exit Function
End If
For Each WB In Workbooks
If Dir(WB.Name) = Dir(OPN) Then
MsgBox Dir(OPN) & "は、すでに開いています", vbCritical
Exit Function
End If
Next WB
Workbooks.Open OPN
Else
MsgBox "キャンセルされました"
ThisWorkbook.Worksheets("Sheet1").Activate
Exit Function
End If
ファイルオープン.Add Array(Workbooks(Dir(OPN)))
End Function
うまくいく場合が以下です。
Sub 解析()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Dim OpenFileName As Collection
Dim TMP As Variant
Set OpenFileName = Application.Run("ファイルオープン")
For Each TMP In OpenFileName
Debug.Print TMP(0).Name
Next TMP
End Sub
Function ファイルオープン() As Collection
Dim OPN As String
Dim WB As Workbook
Set ファイルオープン = New Collection
OPN = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OPN <> "False" Then
If Dir(OPN) = ThisWorkbook.Name Then
MsgBox "同じファイルを開いています" & vbCr & "処理を中断します", vbCritical
Exit Function
End If
For Each WB In Workbooks
If Dir(WB.Name) = Dir(OPN) Then
MsgBox Dir(OPN) & "は、すでに開いています", vbCritical
Exit Function
End If
Next WB
Workbooks.Open OPN
Else
MsgBox "キャンセルされました"
ThisWorkbook.Worksheets("Sheet1").Activate
Exit Function
End If
ファイルオープン.Add Array(Workbooks(Dir(OPN)))
End Function
私の理解では、うまくいかない場合も、うまくいく場合も、同じようにVariant型にコレクションを代入して、それをDebug.Printで表示しようとしているのですが。なぜか、うまくいかない方のマクロにコメントとして書いた部分で「引数の型が一致しません」とエラーが出ます。
なぜなのか、理解できません。
すいません、ご教授願えませんでしょうか。
よろしくお願い申し上げます。
（ろでます） 2014/08/22(金) 15:56
＞Variant型にコレクションを代入して コレクション「オブジェクト」とVariant型「配列」は異なります。 またコレクションオブジェクトは、ArrayListやDictionaryと違い、 ItemやKeyを配列として出力できません。
また静的配列の1要素としてコレクションオブジェクトを格納することは出来ますが、そういう事を したいわけではないですよね？
まずローカルウィンドウを開いてＦ８をポチポチしながらOpenFileNameがどのように変化していくか
確認してください。
●パターンＡの場合、
[-]OpenFileName
└[-]Item 1 ←Collection.Addメソッドで追加したアイテム
└[-]Item 1(0) ←Collection.Addで追加した配列（Array）のアイテム
├ほにゃらら ←配列（Array）に追加したWorkbooksオブジェクト
このようになっています。 コレクションオブジェクトがItemを取得するときは、Keyを指定するかIndexを指定する必要があります。 今回、Keyは省略していますので、Indexでのアクセスとなります。 以上のことから、Collectionに追加した配列の中にあるWorkbookオブジェクトにアクセスするには
OpenFileName.Item(1)(0).Name
~~ ~~ ~~~~
(a) (b) (c)
(a)Collectionアイテムの1つ目にある
(b)Array配列の0番目の要素のWorkbookオブジェクトの
(c)名前を教えなさい
という書き方になります。
●パターンＢの場合、
[-]OpenFileName
└[-]Item 1 ←Collection.Addメソッドで追加したアイテム
├ほにゃらら ←Collection.Addで追加したWorkbookオブジェクト
このように1階層短いので、
TMP(0) = OpenFileName.Item(1).Name
このように書くことが出来ます。
またWorkbookが開かれているかどうかは、いちいちWorkbooksを回さなくてもチェック出来ます。 WBにWorkbookをセットしようとして、失敗すれば開かれていない、成功すれば開かれている、という 原理を使って評価することが可能です。
Sub 解析()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Dim OpenFileName As Collection
Dim TMP(1) As Variant 'ここでエラーが出ます
Set OpenFileName = Application.Run("ファイルオープン")
If OpenFileName.Count > 0 Then
'TMP(0) = OpenFileName '★ここでエラーの間違いですよね？
TMP(0) = OpenFileName.Item(1)(0).Name '●パターンA
'TMP(0) = OpenFileName.Item(1).Name '●パターンB
Debug.Print TMP(0)
End If
End Sub
Function ファイルオープン() As Collection
Dim OPN As String
Dim WB As Workbook
Set ファイルオープン = New Collection
OPN = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OPN = "False" Then MsgBox "キャンセルされました": Exit Function
On Error Resume Next
Set WB = Workbooks(Dir(OPN))
If Err > 1 Then
Set WB = Workbooks.Open(OPN)
Else
MsgBox WB.Name & "は既に開かれています。"
End If
On Error GoTo 0
ファイルオープン.Add Array(WB) '●パターンA
'ファイルオープン.Add WB '●パターンB
End Function
配列に関しては私も相当悩みましたので、一緒に頑張りましょう！ （稲葉） 2014/08/22(金) 17:08
頂いたコメントを基にしてのマクロ作成は月曜になってしまうと思います。
親切なコメントを頂いたのに申し訳ありませんが、追加のコメントは月曜日以降にさせていただきます。
よろしくお願い申し上げます。
（ろでます） 2014/08/23(土) 21:21
コレクションのの方は親切なご説明をいただいたので、大体の理解をできましたと思っています。
コレクション内の何かを指定や代入する場合は、引数として配列がない場合は以下の通り
コレクション名(インデックス番号）
で指定する。
配列がある場合は
コレクション名（インデックス番号）（そのコレクション内の配列の番号）
というご理解でよろしいでしょうか？。
次に、追加でご説明いただいた、エラー処理原理を用いての評価ですが、
＞On Error Resume Next
これは、そのプシージャーの中でエラーが発生した場合、これを無視し、次の行から処理をそのまま進めていく。つまり、この構文はこの行になくても、仮にプロシージャーの先頭にあっても動作するというご理解でよろしいでしょうか？
つまり、これがないと必ず、
＞Set WB = Workbooks(Dir(OPN))
でエラーが出てしまう。
しかし。ここが理解しづらい点で、ここは私の理解だと強制的エラーをはかせて（エラーナンバー9、インデックスが有効範囲にありません）、そして、Errオブジェクトに9が代入されます。
そして次と次の行で、
＞If Err > 1 Then
＞Set WB = Workbooks.Open(OPN)
でErrが9なので、ワークブックオブジェクトWBをセットしつつ、ファイルを開くという処理を行っている。
ここでかなり悩んだのですが、下記のようでもいいのかなと思い、マクロを作ってみました。
Function ファイルオープン() As Collection
Dim OPN As String
Dim WB As Workbook
Set ファイルオープン = New Collection
OPN = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OPN = "False" Then MsgBox "キャンセルされました": Exit Function
'On Error Resume Next
'Set WB = Workbooks(Dir(OPN))
If Err = 0 Then
Set WB = Workbooks.Open(OPN)
Else
MsgBox WB.Name & "は既に開かれています。"
End If
'On Error GoTo 0
ファイルオープン.Add Array(WB) '●パターンA
'ファイルオープン.Add WB '●パターンB
End Function
こうすると、既に開いているファイルを開いても、
＞Else
＞MsgBox WB.Name & "は既に開かれています。"
に飛んでくれませんでした。
つまりは、既に開いているファイルをファイルを開こうとすると、
＞Set WB = Workbooks(Dir(OPN))
でWBがセットされてしまい、Errが0になってしまう。
これを回避するために、
＞On Error Resume Next
＞Set WB = Workbooks(Dir(OPN))
を記述している。
という理解でよろしいでしょうか？
あと、これは大変初心者的な質問で誠に恐縮なのですが、
＞If OPN = "False" Then MsgBox "キャンセルされました": Exit Function
これがなぜ「End if」なしで動いているのかがわかりません。逆に、End ifを入れるとコンパイルエラーになってしまいました。
また、大変長文になって申し訳ありませんが、ご教授のほどをよろしくお願い申し上げます。
（ろでます） 2014/08/25(月) 11:59
IFについてはこちらを。 http://msdn.microsoft.com/ja-jp/library/752y8abs.aspx
==引用== 単一行の構文 ステートメントが単一行の If かどうかを判断するために、Then キーワードの後ろに何が続くかが 調べられます。 同じ行内の Then キーワードの後ろにコメント以外の記述があると、単一行の If ステートメントと 判断されます。 ==ここまで==
ErrについてはErrオブジェクトを調べてください。
テスト用サンプル
Sub test()
Dim aa
Dim i As Long
For i = 1 To 2
Debug.Print "ON Error Resume Next 前 :" & Err
On Error Resume Next
Debug.Print "ON Error Resume Next 後 Set前:" & Err
Set aa = Workbooks("aaaa")
Debug.Print "ON Error Resume Next 後 Set後:" & Err
On Error GoTo 0
Debug.Print "ON Error Goto 0 後 Set後:" & Err
Next i
End Sub
順番に 0 0 9 0の値が返ってくると思います。 Errオブジェクトは「エラー処理ルーチン内」でしかプロパティを保持しません。 「エラー処理ルーチン内」とは、On Error ステートメントからOn Error Goto 0で終わらせるか、 ヘルプの中にある通り、各Exit ○○で処理が終了した場合のみです。
どういう考えで
'On Error Resume Next
'Set WB = Workbooks(Dir(OPN))
If Err = 0 Then
このような解釈になったのか逆に知りたいのですが・・・
気になったことを聞くのは結構なのですが、もう少しヘルプを参照してみるとか、Google先生に尋ねて みるとかしてみた方が自分のためにもなりますよ。 調べたけどこの解釈がどうしても分からない、という事があれば説明致しますが・・・
（稲葉） 2014/08/25(月) 13:09
すいません、返す言葉もありません・・・。
自分でも結構Google先生に聞いたりしたうえで、解釈があっているかどうかをお伺いした次第でございます。
頂いたテスト用サンプルはエラー処理などを調べた上で、十分理解できます。
本来ならば
＞Set aa = Workbooks("aaaa")
で、ありもしない「aaaa」というワークブックを「aa」にオブジェクトとして格納としているので、Errが9になるが、手前で「On Error Resume Next」を書いているため、エラーにならず次の行へ進む。そして、
＞On Error GoTo 0
でエラー処理ルーチンが無効になるためErrが0になると理解しています。
＞どういう考えで（以下略
すいません、これは、稲葉様から頂いた以下のマクロの逆の発想で考えてしまったためです。
つまり、ちゃんとファイルが選択されていれば、Err=0のはずなので、ファイルを開いて問題ないという発想から来たものです。それが、浅はかな考えだったことは、先のコメントにも書いた通り、既に開かれているファイルや、自分と同じファイルを選択した場合
＞Else
＞MsgBox WB.Name & "は既に開かれています。"
に飛ばないことが分かりました。
理解不足でした、申し訳ありません。
if文の件に関してももう少し調べてから聞くべきでした。
ご気分を害されたのであれば、誠に申し訳ございません、謝罪申し上げます。
今日は昼から出張で出ていたため、マクロを組むのも、コメントをするのも遅くなってしまいました。
しかし、もう一歩のところまで来ているとは自分では感じています。
呼び出し元に対して、呼び出しされる側から、コレクション渡しで値を渡して、配列変数に代入するところまでは出来ました！
あとは、呼び出しされる側のコレクション「ファイルオープン」にWBオブジェクトではなく「何も入らない」場合はいったい呼び出す側に対して何が返り値として返ってくるのかを調べているところです。
（すいません、なぜ「何も入ってこない場合」があるのかは、すいません先に書いた私のマクロの後に、何も入ってない場合(要はファイルオープンに失敗した場合）の処理が入っているためです。
これは、今一生懸命調べたり、サンプルでテストマクロを組んだりして自分で解決しようと頑張っているところです。
何とか調べきれて、自分でできるよう頑張ってみます！。
（ろでます） 2014/08/25(月) 17:07
こんにちは。横から失礼します。
Set WB = Workbooks(Dir(OPN))
If Err = 0 Then
Set WB = Workbooks.Open(OPN)
ここですが、なんとなく、ろでますさんは Dir(OPN)とただのOPNの違いを理解しておられないように思えます。 Dirについて調べてみてはどうでしょう。
あと、Set WB = Workbooks(Dir(OPN))がエラーになるのはどんなときなのか 環境を整えて確実にエラーを起こすことができますか？
（ 佳 ） 2014/08/25(月) 18:18
このスレッドに多少なりとも関わったことで、発見（大げさ）したことを記述しておきます。
Application.Runメソッドについて・・。
仮に a.xlsmの標準モジュールに
'===========================================================
Function get_fpath() As Collection
Const cancel As Variant = False
Dim flpth As Variant
Dim bk As Workbook
flpth = ""
Set get_fpath = New Collection
Do Until flpth = cancel
flpth = Application.GetOpenFilename("Microsoft Excelブック,*.xlsx,Microsoft Excelブック,*.xlsm,Microsoft Excelブック,*.xls", 1)
If flpth <> cancel Then
Set bk = Workbooks.Open(flpth)
get_fpath.Add bk, bk.Name
End If
Loop
End Function
呼び出し側のプロシジャーとして同じ標準モジュールに
'===================================================================
Sub test1()
Dim col As Collection
Dim bk As Workbook
Set col = Application.Run("a.xlsm!get_fpath")
For Each bk In col
MsgBox bk.Name
Next
Set col = Nothing
Set bk = Nothing
End Sub
test1を実行すると、ファイル選択ダイアログが表示され、 選択したExcelファイル名が表示されます。これは、既にこのスレッドで投稿があるとおりです。
このプロシジャー「get_fpath」がThisworkbookのモジュールにある場合は、
先の投稿でも記述しましたが、Workbookオブジェクトに新たに付け足したメソッドと 位置づけられるので,
>Set col = Application.Run("a.xlsm!get_fpath")
の箇所を
set col=workbooks("a.xlsm").get_fpath
とすれば作動すると申し上げました。
ここまでは、そのとおりなのですが・・・・。
では、Thisworkbookのモジュールに「get_fpath」がある場合は、 Application.Runを使うことは出来ないのでしょうか？
確認前は、そりゃあ、出来るだろうけど、workbooks("a.xlsm").get_fpath でいいじゃん、
と思っていましたが・・・。
ThisworkBookのモジュールにget_fpathを配置し、
標準モジュールから、
Sub test2()
Dim col As Collection
Dim bk As Workbook
Set col = Application.Run("a.xlsm!thisworkbook.get_fpath")
For Each bk In col
MsgBox bk.Name
Next
Set col = Nothing
Set bk = Nothing
End Sub
test2を実行すると、ファイルを選択が終了した時点、
>Set col = Application.Run("a.xlsm!thisworkbook.get_fpath")
↑ここで「424 オブジェクトが必要です」というエラーが発生し、プログラムが止まります。
上記では、そもそもオブジェクトや値を返してくれません。 つまり、Functionが機能していない結果になっています。
これがこのスレッドで気が付いたことです。
今まで気が付かなかったので 記述してみました。
、
（ichinose） 2014/08/26(火) 09:03
試してみました。 ファイル選択まで出来ましたが、同じく424エラーでした。 ということは、マクロブックとしてThisworkbookにプロシジャーを集めた場合はメソッドとして 他の標準モジュールなどを使いまわす時はRunでコードを組む？
素直にThisworkbookに集めた方が分かりやすそうですね！
滅多に使わないので、自分ひとりで組んでたら気付けず迷宮入りになっていたかもしれません。 ありがとうございます。 （稲葉） 2014/08/26(火) 09:37
私のVBAスキルでは桂様のおっしゃっていることやichinose様から頂いたマクロを理解するのに時間がかかりそうなので、とりあえず、お礼だけを先に述べておきます。
今からGoogle先生Officeヘルプさんと格闘しながら、理解に努めさせていただきます。
あと、コメントあってから30分でichinoseさんのコメントを理解して、コメントを返される稲葉様はやっぱり本当に正直すごいスキル把握者だと思います。
このエクセルの学校の場で巡り合わせていただいたことをありがたいと思います。（もちろんほかの皆様方もですよ！）
でわ、少し月末近いということもございまして、他業務が逼迫していることもあり、少々追加コメント等時間がかかるかもしれませんが、その際はよろしくお願いいたします。
（ろでます） 2014/08/26(火) 09:53
皆様から頂いたコメントをもとに、自分のやりたいことを考えて、とりあえずうまくいったことをご報告申し上げます。
やりたいことは、大分前のコメントで申し上げた通りファイルを開くことですが、これにプラスして
条件1：複数ファイルを開く
条件2：同じファイルを開いてしまったらエラーメッセージを出し、本体マクロが入ったファイル以外のすべてのエクセルファイルを閉じる。
条件3：ファイルを開く選択画面で「キャンセル」が押された場合は、条件2同様エラーメッセージを出し、本体マクロが入ったファイル以外のすべてのエクセルファイルを閉じる。
です。
そして、作ったマクロが以下の通りです。
Sub 解析()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Dim i, YN As Integer
Dim OpenFileName As Collection
Dim TMP() As Variant
Dim FLcolse As Boolean
i = 0
Do
If i <> 0 Then
YN = MsgBox("まだファイルを開きますか", vbYesNo + vbQuestion, "確認")
If YN = 7 Then Exit Do
End If
Set OpenFileName = Application.Run("ファイルオープン")
ReDim Preserve TMP(i)
If OpenFileName.Count > 0 Then
TMP(i) = OpenFileName.Item(1).Name
End If
i = i + 1
Loop While TMP(i - 1) <> ThisWorkbook.Name
If TMP(i - 1) = ThisWorkbook.Name Then
FLclose = Application.Run("ファイルを閉じる")
If FLclose = False Then MsgBox "ファイルを閉じることができません" & vbCr & "プログラムを終了します": Exit Sub
End If
End Sub
Function ファイルオープン() As Collection
Dim OPN As String
Dim wb As Workbook
Set ファイルオープン = New Collection
OPN = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OPN = "False" Then
MsgBox "キャンセルされました"
Set wb = ThisWorkbook
ファイルオープン.Add wb
Exit Function
End If
On Error Resume Next
Set wb = Workbooks(Dir(OPN))
If Err > 1 Then
Set wb = Workbooks.Open(OPN)
Else
MsgBox wb.Name & "は既に開かれています。"
Set wb = ThisWorkbook
ファイルオープン.Add wb
Exit Function
End If
On Error GoTo 0
ファイルオープン.Add wb
End Function
Function ファイルを閉じる() As Boolean
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then
wb.Close SaveChanges:=False
End If
Next wb
ファイルを閉じる = True
End Function
稚拙なマクロだと思いますが、自分のスキルではこれが今できる精一杯でした。
一番頭を悩ませたのが、やはりコレクション渡しのところでした。
普通にファイルが開かれた場合は、プロシージャー「ファイルを開く」でコレクション「ファイルを開く」にWB（開かれたファイル）のワークブックオブジェクトが格納されますが、キャンセルを押された場合や、同じファイルを開かれた場合、WBには何も入りません。
そうなった場合、次に「ファイルを閉じる」プロシージャーを呼び出すという作業が入るのですが、この条件式として、
if TMP(i)="" then ⇒「ファイルを閉じる」プロシージャへ移行する
や
if TMP(i)="Enpty" then
if TMP(i)="False" then
なども当然駄目でした。
コレクションに何も入っていない場合、どうしたら判定できるのかがどうしてもわかりませんでした。
そこで、もし、キャンセルや同じファイルを開いた場合はオブジェクトWBにThisWorkBookを入れることにしました。
こうすることにより、きちんとファイルが開かれた場合は、さらにファイルを開くかどうかをvbYesNo + vbQuestionで問合わせ、Noなら更にここから続く予定の、開いたファイルから数値を拾っての解析処理へ移行、開かれていない場合（同一ファイルの指定や、キャンセルボタンクリックの場合）は、自分（ThisWorkBook）以外のすべてのエクセルファイルを閉じるという処理を走らせることにしました。
こうすることにより、とりあえず、自分のやりたかったことができるようになりました。
本当に稚拙なマクロですが、自分のやりたいことができたということは、コメントをいただいた皆様方のコメントがあってこそです。
心からの御礼を申し上げます。
また、このマクロ、「ここをこうすればもっと効率がいいよ」などのアドバイス等あれば、是非ともお願い申し上げます。
これから解析処理の方のマクロに着手していくのですが、そこでもまた壁に阻まれることがあろうかとも思います。
その際は、再度のアドバイス、ご教授等、何卒よろしく申し上げます。
とりあえずはですが、本当に皆様ありがとうございました！
（ろでます） 2014/08/29(金) 13:20
効率というか、たぶんまだ一つのブックでしか試してないですよね？ 複数ブックで試したとき、Thisworkbookの挙動確かめましたか？
それから、条件２は、3つブックを開くとき、2つ目まで開いて、3つ目に誤って開こうとした場合も 閉じられてしまうのですか？
閉じる挙動も強制的に上書き保存なしで閉じたら、作業中のブックが開かれていた場合も閉じられて しまいますので、間違った人に非難浴びますよ。
Functionの戻り値もせっかくCollectionにしているのに、1つしか戻さないのならWorkbookで十分です し、次のような考え方の方がよいのではないでしょうか？
Sub 解析()
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Dim i, YN As Integer
Dim OpenFileName As Collection
Dim TMP() As Variant
Dim FLcolse As Boolean
i = 0
Set OpenFileName = Application.Run("ファイルオープン") '●使いまわす前提なら、繰り返し処理も呼び側に記載すべきかと。
'Do
' If i <> 0 Then
' YN = MsgBox("まだファイルを開きますか", vbYesNo + vbQuestion, "確認")
' If YN = 7 Then Exit Do
' End If
' Set OpenFileName = Application.Run("ファイルオープン")
' ReDim Preserve TMP(i)
' If OpenFileName.Count > 0 Then
' TMP(i) = OpenFileName.Item(1).Name
' End If
' i = i + 1
'Loop While TMP(i - 1) <> ThisWorkbook.Name
'If TMP(i - 1) = ThisWorkbook.Name Then
If OpenFileName.Count = 0 Then '●Collectionにはcountプロパティがあるので、0ならという表現が出来ます。
FLclose = Application.Run("ファイルを閉じる") '●ここも使いまわすの？ だったら、引数に残したいファイル名を配列で渡すなど工夫必要では？
If FLclose = False Then MsgBox "ファイルを閉じることができません" & vbCr & "プログラムを終了します": Exit Sub
End If
End Sub
Function ファイルオープン() As Collection
Dim OPN As String
Dim wb As Workbook
Set ファイルオープン = New Collection
Do
OPN = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OPN = "False" Then
MsgBox "キャンセルされました"
Else
On Error Resume Next
Set wb = Workbooks(Dir(OPN))
If Err > 1 Then
ファイルオープン.Add wb = Workbooks.Open(OPN)
Else
MsgBox wb.Name & "は既に開かれています。"
End If
On Error GoTo 0
End If
Loop
End Function
Function ファイルを閉じる() As Boolean
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then '●この表現だと呼出側のブックは閉じられてしまいませんか？
wb.Close SaveChanges:=False
End If
Next wb
ファイルを閉じる = True
End Function
（稲葉@リハビリ） 2014/08/29(金) 13:42
なるほど、確かに、ふむふむと納得します。
特に
＞FLclose = Application.Run("ファイルを閉じる") '●ここも使いまわすの？ だったら、引数に残したいファイル名を配列で渡すなど工夫必要では？
＞If wb.Name <> ThisWorkbook.Name Then '●この表現だと呼出側のブックは閉じられてしまいませんか？
の部分はおっしゃる通りですね。
「ファイルを閉じる」プロシージャに対して、せっかく開いたファイル名をコレクションで渡せたのですから、そこから今度は閉じる側に対してそれを渡せば今まで開いたファイルのみを閉じることが可能ですね。
やり方や実際のマクロはこれから考えますが（ちょと今から出張に出てしまうので、実際のマクロの作成は来週になってしまいますが）、稲葉様にアドバイスいただいた点は確かにユーザビリティの面から是非とも実現したいと思いますし、頑張ります！。
（ろでます） 2014/08/29(金) 15:07
とりあえずはですが、稲葉様から頂いたコメントを参考にして、ファイルを閉じる部分を開いたファイルのみを作成してみました。
あと、「ファイルを開く」Functionはこちらの事情から、どうしてもメインの「解析」サブマクロ側に入れ込まないといけない理由（というか私のスキル不足が8割を占めると思うのですが）から、Do〜Loop分は「解析」で入れ込んで、「ファイルを開く」マクロでは、純粋に
Option Explicit
Function ファイルオープン() As Collection
Dim OPN As String
Dim WB As Workbook
Set ファイルオープン = New Collection
OPN = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OPN = "False" Then
MsgBox "キャンセルされました"
Set WB = ThisWorkbook
Exit Function
End If
On Error Resume Next
Set WB = Workbooks(Dir(OPN))
If Err > 1 Then
Set WB = Workbooks.Open(OPN)
Else
MsgBox WB.Name & "は既に開かれています。"
Set WB = ThisWorkbook
End If
On Error GoTo 0
With ファイルオープン
.Add Item:=WB
End With
End Function
だけになっています。
申し訳ありませんが、大分マクロを端折らせて書かせていただきます。
端折りすぎてご理解できませんでしたら申し訳ございません。
「解析」マクロではファイルを開くのに
Set OpenFileName = Application.Run("ファイルオープン")
ReDim Preserve OPN(i)
OPN(i) = OpenFileName.Item(1).Name
というマクロを使って、Variant型の配列OPN(i)に開いたファイル名を格納します。
そして、ファイルを開くのに失敗した場合や、同じファイルを開いた場合は、「ファイルを閉じる」Functionで閉じますが、以下のように書いて、うまく動作しました！
「解析」マクロ内
FLclose = Application.Run("ファイルを閉じる", OPN)
「ファイルを閉じる」マクロ内
Function ファイルを閉じる(ByVal OPN As Variant) As Boolean
Dim i, j As Integer
j = UBound(OPN)
Debug.Print OPN(1)
For i = 1 To j
If Workbooks(OPN(i)).Name <> ThisWorkbook.Name Then
Workbooks(OPN(i)).Close SaveChanges:=False
End If
Next i
ファイルを閉じる = True
End Function
ちなみに、OPN(i)の配列は、0からではなく、1から使用しております。
これで、試に別のエクセルブックを開いた状態でも、そのブックを除き、誤って開いてしまった（その前にマクロで開いていたブックも含め）ファイルのみを閉じることができました。
これはご報告となりますが、アドバイスいただいた皆様のために、ご報告をさせていただきました。
ありがとうございます！
（ろでます） 2014/09/02(火) 10:09
すいません！（改めて泣）
（ろでます） 2014/09/02(火) 10:41
Dim i, j As Integer
j = UBound(OPN)
Debug.Print j
For i = 1 To j
If Workbooks(OPN(i)).Name <> ThisWorkbook.Name Then
Workbooks(OPN(i)).Close SaveChanges:=False
End If
Next i
ファイルを閉じる = True
End Function
として、仮にファイルを2つ開いた状態で、3つ目のファイルを誤って開かせたとき、私の予想では
Debug.Print j
で要素数として
3が返ってこないといけないのに、どうしても1が返ってきてしまうのが原因のようです。
なぜそうなるのかは今から調べます。
以上ご報告まで！(またまた泣）
（ろでます） 2014/09/02(火) 10:49
OPN(i)の中に入れる変数値(i)が間違っていました。（本来ならば別の変数(j)を入れるべきでした。
何の事だかわからないかとは思いますが、すいません、自己解決したことをご報告申し上げます。
（ろでます） 2014/09/02(火) 12:13
＞あと、「ファイルを開く」Functionはこちらの事情から、どうしてもメインの「解析」 ＞サブマクロ側に入れ込まないといけない理由（略） ＞Function ファイルオープン() As Collection でしたら、このFunctionはWorkBook型にしてはいかがでしょう？ 解析側でCollectionを持てばよいだけの話で、無駄な変数を持たなくてすみます。
＞Function ファイルを閉じる(ByVal OPN As Variant) As Boolean ＞If Workbooks(OPN(i)).Name <> ThisWorkbook.Name Then こちらは前回も指摘しましたが、他のブックにおいて共有して使うんですよね？ もう一度言いますが ThisWorkbook.Name が希望通りの動きになるか再度ご確認ください。 またブックが閉じられないときがどういうときを想定しているのか分かりません。 Functionではなく、Subで宣言されてもよろしいのではないですか？
完全に別ものですが、ひとつの案です。
Sub 解析()
'Application.ScreenUpdating = True
'Application.DisplayAlerts = True
Dim tmpWB As Workbook
Dim OpenFileName As Collection
Dim msg As String
Set OpenFileName = New Collection
Do
'//OpenFileNameにItemがあれば、まだ開くか確認する。
If OpenFileName.Count > 0 Then
If MsgBox("まだファイルを開きますか", vbYesNo + vbQuestion, "確認") = vbNo Then Exit Do
End If
'//「ファイルオープン」の戻り値をWorkbookにして、変数に入れる
Set tmpWB = Application.Run("ファイルオープン")
'//Nothingでなければ、OpenFileName「コレクション」に追加する
If Not tmpWB Is Nothing Then
OpenFileName.Add tmpWB
Else
'//Nothingならば、ファイルを閉じるを実行
If Application.Run("ファイルを閉じる", OpenFileName, ThisWorkbook) = False Then
msg = "ファイルを閉じることができません。" & vbCrLf
Else
msg = "ファイルを閉じました。" & vbCrLf
End If
MsgBox msg & "解析するファイルが既に開かれている場合、閉じてから再度やり直してください。" & vbCrLf & _
"プログラムを終了します"
Exit Sub
End If
Loop
MsgBox "処理開始"
End Sub
Function ファイルオープン() As Workbook
Dim OPN As String
OPN = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OPN = "False" Then
MsgBox "キャンセルされました"
Set ファイルオープン = Nothing
Exit Function
End If
On Error Resume Next
Set ファイルオープン = Workbooks(Dir(OPN))
If Err > 1 Then
Set ファイルオープン = Workbooks.Open(OPN)
Else
MsgBox ファイルオープン.Name & "は既に開かれています。"
Set ファイルオープン = Nothing
End If
On Error GoTo 0
End Function
Function ファイルを閉じる(OPN As Collection, NoClose As Workbook) As Boolean
Dim WB As Workbook
ファイルを閉じる = True
On Error Resume Next
For Each WB In OPN
If WB.Name <> NoClose.Name Then WB.Close savechanges:=False
Next WB
If Err > 0 Then ファイルを閉じる = False
On Error GoTo 0
End Function
（稲葉） 2014/09/02(火) 14:46
確かにおっしゃる通り、「ファイルを閉じる」で、「ファイルを閉じる」が別ブックにあった場合、自分が意図しないファイル（というか呼び出す側のマクロが入ったファイル）まで閉じてしまいました。
これは確かに困ります。
ここは、稲葉様から頂いたマクロをほぼそのまま使わせていただくと、自分の意図する形でのファイルの開閉ができるようになりました。
確かに、単純に考えて、使いまわすのにThisWorkBook指定だと、「ファイルを閉じる」マクロがあるファイル以外がばっと閉じてしまうことを、なぜ今まで気づかなかったのかと、自分でも恥ずかしいところです。
「ファイルを閉じる」マクロは正直そのまま、稲葉様からいただいたマクロをほぼそのまま使用させていただこうかと思います。
＞またブックが閉じられないときがどういうときを想定しているのか分かりません
＞Functionではなく、Subで宣言されてもよろしいのではないですか？
確かに、「ファイルを閉じられない場合」などという動作はないとは思っています。
ここで、エラーが出る（つまりFunctionにして引数でTrue・Falseをとらなくても）事はないとは思っています。
が、まだ自分で自分のマクロ自身が信用できない自分がいます。
ですので、Functionで引数を Boolean型にしておけば、デフォルトがFalseなので、もし「ファイルを閉じる」マクロ内で私の意図しない動作があった場合、Falseが返ってくるため、マクロを強制的に終了さえることができると考えていたためです。
ですので、ここは初心者のデバッグ作業に対する苦肉の策とでも思ってください。
あと、「ファイルを開く」の新しいアプローチの仕方ですが、せっかくマクロコードをいただいたのですが、ファイルを開く側はやはりコレクション渡しのまま残しておこうと思います。
マクロの修正が面倒くさいとかいう理由ではなく、ただ、今後自分がコレクションを使用する場合、自分が作ったマクロをよく参考に見直したりすることがあるため、そのために残しておこうかと思っているためです。
このスレッドで、多くのことを教わったコレクション渡しでの手法を忘れて無駄にしたくありません。
そのためと思ってください。
「ファイルを閉じる」部分を少々手直しするので、本マクロの方にも修正が加わるので、いろいろ考慮する時間等が必要ですが、何とか自分でできるよう頑張ってみます。
（ろでます） 2014/09/03(水) 11:14
＞ですので、Functionで引数を Boolean型にしておけば、（中略） ＞Falseが返ってくるため、マクロを強制的に終了さえることができると考えていたためです。 いやいや、最初の記述を見る限り、On Errorルーチン記述してないんですから、 デバッグエラーで止まりますよ？ 例えOn Error Resume Nextを記述していたとしても、分岐していないところでTrueにしていれば Trueしか戻りませんよ？
＞ファイルを開く側はやはりコレクション渡しのまま残しておこうと どちらにしろ、TMPとかYNとか変数必要ないですよ。 前述したコードのtmpWBをコレクションにして、それ以降の分岐でtmpWB(1)とすれば同じことですから。 というか、最初は「複数の値を戻せない」から始まってCollectionの話をしたのに、 いつの間にか戻り値は1つだし・・・
一番最初のときは上手にFor Each使っていますが、途中からなぜiとかjに・・・？ 今回のコレクションは1次配列なんですから、順番さえ気にしなければFor Eachで回せますよ。
それから、ファイルオープンは手直しするそうですが、こちらも別ブックですよね？ ThisWorkbookをコレクションに入れても期待通りの結果にならないので、Nothingを活用された ほうがいいですよ。 参考までに
解析の変更点
＞ Dim tmpWB As Workbook
Dim tmpWB As Collection
＞ '//「ファイルオープン」の戻り値をWorkbookにして、変数に入れる
'//「ファイルオープン」の戻り値をCollectionのまま、変数に一度渡す
Set tmpWB = Application.Run("ファイルオープン")
'//コレクションの中身は１つしかないので、要素1でWBオブジェクト取得
'//Nothingでなければ、OpenFileName「コレクション」に追加する。
If Not tmpWB(1) Is Nothing Then
OpenFileName.Add tmpWB(1)
ファイルオープンは全掲載
Function ファイルオープン() As Collection
Dim OPN As String
Dim WB As Workbook
Set ファイルオープン = New Collection
OPN = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OPN = "False" Then
MsgBox "キャンセルされました"
ファイルオープン.Add Nothing
Exit Function
End If
On Error Resume Next
Set WB = Workbooks(Dir(OPN))
If Err > 1 Then
ファイルオープン.Add Workbooks.Open(OPN)
Else
MsgBox ファイルオープン.Name & "は既に開かれています。"
ファイルオープン.Add Nothing
End If
On Error GoTo 0
End Function
（稲葉） 2014/09/03(水) 11:58
＞いやいや、最初の記述を見る限り、On Errorルーチン記述してないんですから、
＞デバッグエラーで止まりますよ？
＞例えOn Error Resume Nextを記述していたとしても、分岐していないところでTrueにしていれば
＞Trueしか戻りませんよ？
今、スレッドにある自分のマクロを読み返してみました。
・・・あ、確かにその通りです。
これだとどうやっても確かにTrueしか返らないですね。
フローチャートすら作らずにマクロを組んでいる状態なので（いい加減だと怒らないでください）、とりあえず、Application.Runで別プロシージャーを呼び出す場合は、そのプロシージャーが成功しているかどうかを、Boolean型にしてしまいTrueかFalseで判定をするという、私のマクロを組む際にしみついてしまった「癖」になっており、それが今回ご指摘を受けた点になってしまった・・・。
この癖はご指摘いただいた通りケースバイケースで無駄なマクロを組まない・効率よくするという点で治さないといけませんね（こういうことをしているから、たぶん私はプログラミングの技術が上達しないのだとも思います）
＞どちらにしろ、TMPとかYNとか変数必要ないですよ。
ここは、おっしゃる通り、既に消去をしています。
無駄な変数は極力なくすようには心がけております。
＞ 一番最初のときは上手にFor Each使っていますが、途中からなぜiとかjに・・・？
ここは、正直言って、最初から私がやりたいことを全部包み隠さずに申し上げていれば、ここまでにはならなかったかもしれないですね。
次から次へ追加注文してしまったので・・・この点は反省しております、申し訳ないと思っています。
正直、これ以上は稲葉様にも悪いと思いますし、マクロは不細工ですがやりたいことは、これまでいただいたアドバイスでなんとかやりたいことの「第一歩」は超えたと思っています。（正直これ以外にいろいろ引数などを渡したりしているのですが、それらのパターンを全て網羅した力任せのデバッグもうまくいきました）
とりあえずは、今までいただいたアドバイスは、きちんとテキストファイルに残しています。
結構量が膨大になってしまったので読解力不足の私がすべてを理解するのが大変・・・というのが本音です。親身にアドバイスいただいているのにアドバイスして頂いた方がちんぷんかんぷんでは意味がありません。
ですので、ここまでのアドバイスで、後はマクロをスリムかつ効率の良い作り方を自分なりに考えて、今後のVBA作成に活かしていきたいと思います。
ですので、まずは、理解やわからないことを調べるために少し時間をいただきたいと存じ上げます。
ファイルを開いたり閉じたりするのは、あくまで「解析」マクロを組むための前段で、実際の「解析」をする方にもそろそろ取り掛からないと・・・上からの圧力が・・・(汗
このスレッドは、ここで一度閉じて、また改めて、わからないところがあれば、別スレッドを上げてお伺いしたいと思います。
一方的で失礼かとは思いますが、ご容赦いただけませんでしょうか。
わたしは、この「ろでます」というハンドルネームしか使わないの、また見かけたら是非ともよろしくお願いします。
（ろでます） 2014/09/03(水) 14:41
久しぶりにやる気のありそうな方だなーと思い、こちらも強引過ぎました。 申し訳ありません。 何かあればまた回答致します。 （稲葉） 2014/09/03(水) 15:40
これまで、これほどまでに親身に相談に乗っていただけた方は、他にいらっしゃいませんし、おそらくこのアドバイスいただいた内容などを、研修会社の研修などで受けようとするといくらかかるか分からないほどの金額がかかるでしょう。（どの会社の何の研修とは、その会社を非難することになるので公のネット上では申し上げられませんが、陳腐な内容でも10万単位の金額だったことを覚えています）
申し訳ないという言葉はこちらがしないといけない言葉です。
本当にありがとうございました、またこの掲示板での再会を楽しみにしております！
（ろでます） 2014/09/03(水) 15:56
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.