Excel (VBA)

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

 
(Windows 7 Professional : Excel 2013)
他ブックの「Workbook_BeforeSave」イベントを掴む方法について
投稿日時: 18/04/26 18:45:21
投稿者: もこな2

いつもお世話になっています。
表題のことについてなのですが、うっかり編集してはいけないところまで編集して上書保存してしまった〜〜という事故がおこったので、せめて前保存時の状態まで戻せるように、保存時にバックアップファイルを生成するよう、以下のコードをブックモジュールに記述しました。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    With ThisWorkbook
        SaveCopyAs (.Path & "\" & _
            Left(.Name, InStrRev(.Name, ".") - 1)) & "_" & _
            Format(Date, "yyyy_mmdd") & "_" & _
            Format(Time, "hhmm") & _
            Mid(.Name, InStrRev(.Name, "."), 5)
    End With
End Sub

とりあえず、思った動作にはなったのですが、このままだとマクロを記述したブックに対してしか有効ではないと思いますので、アドイン化したいと思っています。
そこでの疑問なのですが、このマクロは「Workbook_BeforeSave」を使ってますので、アドイン化した場合、アドインブック?以外のブックのイベントを掴む必要があるようにおもうのですが、それはどのようにしたら良いのでしょうか。
軽く調べてみると、クラスモジュールを使うというような記事が見つかったのですが、クラスモジュールは未知の領域でよくわかりませんでした。
 
そもそも本当にクラスモジュールを使う必要があるのかも解っていないのですが、その点も含めてご教授いただければ幸いです。

回答
投稿日時: 18/04/26 20:34:35
投稿者: WinArrow
投稿者のウェブサイトに移動

クラスモジュールを使う方法を紹介します。
 
かなり手抜きのサンプルです。
 
 
【ThisworkBookモジュール】
Option Explicit
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set myApp.App = Nothing
End Sub
 
【クラスモジュール】
モジュール名:AppClass
 
Option Explicit
 
Public WithEvents App As Application
 
Private Sub App_WorkbookBeforeSave(ByVal myBK As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
Const SVFILE As String = "Book2.xlsx"
 
    If myBK.Name <> SVFILE Then Exit Sub
     
    Debug.Print myBK.Name
    Cancel = True
End Sub
 
 
【標準モジュール】
Option Explicit
 
Public myApp As New AppClass
 
Sub test()
    Set myApp.App = Application
 
End Sub
 
本ブックと別ブック(ブック名:Book2.xlsx)を開いておきます。
 
TESTを実行します。
 
BOOK2.xlsxを「上書き保存」実行
 
クラスモジュールの中で定義している「Book2.xlsx」と一致すると
保存せずに終了します。
 
 

回答
投稿日時: 18/04/27 09:17:36
投稿者: よろずや

もっと手抜きを紹介します。

【ThisworkBookモジュール】
Option Explicit

Private WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    If Wb.Name = ThisWorkbook.Name Then Exit Sub
    If Wb.Path = "" Then Exit Sub
    Wb.SaveCopyAs Wb.Path & "\" _
        & Left(Wb.Name, InStrRev(Wb.Name, ".") - 1) _
        & Format(Now, "_yyyy_mmdd_hhmm") _
        & Mid(Wb.Name, InStrRev(Wb.Name, "."))
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set App = Nothing
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

回答
投稿日時: 18/04/27 12:31:03
投稿者: よろずや

前掲だと初回保存時のバックアップが取れないので再掲。
【ThisworkBookモジュール】

Option Explicit

Private WithEvents App As Application

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim NewName As String
    If Wb.Name = ThisWorkbook.Name Then Exit Sub
    If Wb.Path = "" Then Exit Sub
    NewName = Left(Wb.Name, InStrRev(Wb.Name, ".") - 1) _
        & Format(Wb.BuiltinDocumentProperties(12).Value, "_yyyy_mmdd_hhmm") _
        & Mid(Wb.Name, InStrRev(Wb.Name, "."))
    If Dir(Wb.Path & "\" & NewName) = NewName Then Exit Sub
    Shell "CMD.EXE /C COPY " & Wb.FullName & " " & Wb.Path & "\" & NewName, vbHide
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set App = Nothing
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

投稿日時: 18/04/27 14:17:31
投稿者: もこな2

回答ありがとうございます。
 
とりあえず、お二人の回答を拝見して、やっぱりクラスモジュール使う必要があるんだろうな〜というところまではわかったんですが、そこから先がいまひとつ理解が追い付いてないです。
 
とりあえず、標準モジュールのモジュールレベル変数で

Public myApp As New AppClass
Private WithEvents App As Application

という宣言をしているので、ここら辺が肝なのかなとおもうのですが、そこから先がどうにも・・
 
今の理解だと
「あああ.xls」というブックを保存しようとするというイベントが発生

「あああ.xls」の【ThisworkBookモジュール】の「WorkbookBeforeSave」が呼び出される
 
っていう理解なんですが、ここがクラスモジュールを使うとどのように変わるのでしょうか
 
App_WorkbookBeforeSaveの引数でWorkBookオブジェクトを渡しているのがポイントなのかなともおもったですが、そもそもこのオブジェトってどこで取得してるんだろうというところがわかってないです

回答
投稿日時: 18/04/27 15:21:28
投稿者: よろずや

マクロブックをダブルクリック。
Windowsがファイルの拡張子を判断してエクセルを起動する。
(ファイル名をエクセルに渡す)
エクセルがファイル(マクロブック)を開く。
そのファイルに Sub Workbook_Open() の記述があるのでそれを呼び出す。
  Set App = Excel.Application
App という変数にエクセル自身を登録する。
この App には WithEvents の記述があるので、エクセル自身が受け取ったメッセージをプログラムで扱えるようになる。
データブックを開く。
開いた時の記述は特に書かれていないのでエクセルはプログラムには何も通知しない。
データブックを保存するボタンをクリックする。
エクセルが保存する前に Sub App_WorkbookBeforeSave を呼び出す。
(ブック名等をVBAプログラムに渡す)
VBAプログラムが実行される。
Cancel を True にしてないのでエクセルは保存を実行する。
ってな感じです。

回答
投稿日時: 18/04/27 22:22:38
投稿者: WinArrow
投稿者のウェブサイトに移動

「モジュール」とは・・・から
 
モジュールを大別すると
「標準モジュール」と」「クラスモジュール」になります。
 
後者を説明上。広義のクラスモジュールと呼びます。
 
「標準モジュール」は、たぶんお分かりだと思いますので割愛します。
 
「広義のクラスモジュール」には、
「シートモジュール」、「Thisworkbookモジュール」、「ユーザーフォームモジュール」と
「クラスモジュール」があります。
 
イベントは、「広義のクラスモジュール」で扱うことができます。
 
なお、
>Private WithEvents App As Application
↑このコードは、標準モジュールには記述できません。
 
 
「クラスモジュール」(広義ではない方)は、対象のオブジェクト毎に、1つの空間が作成されます。
たとえば、ユーザーフォームのコントロールの操作をクラスモジュールで共通化することができます。
コンボボックスが複数あった場合、各々のついて「Change」イベント」プロシジャを記述しなければいけませんが、クラスモジュールを使えば、記述は1つで済みます。
 
今回,
Applicationのクラスを記述するわけですから、自分がExcelになったつもり・・と考えればよいのです。
 
 
概念だけですが、一助になればと思い・・・参考にしてください。

回答
投稿日時: 18/04/27 23:53:37
投稿者: MMYS

Excelを新規起動。
VBEエディッタを開く
ThisWorkbookを右クリック。コードウインドウを表示します
 
左上のコンボボックス (General) をクリック。
すると
(General)
Workbook

とリストか出るので Workbook をクリック
下記の雛形コードが自動挿入されます。
 

Private Sub Workbook_Open()
End Sub

続いて
下記の1行を追加して下さい
 
Private WithEvents App As Application

Private Sub Workbook_Open()
End Sub

同様にコンボボックス (General) をクリック。
すると
(General)
App
Workbook

と見慣れないリストが出現します。
で App をクリック
雛形コードが自動挿入されます。
 
Private WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
End Sub

Private Sub Workbook_Open()
End Sub

下記のように書き換えて下さい。
 
Private WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    MsgBox "新しいブックが追加されました。"
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

このブックを「Excekアドイン形式(*.xlam)」で保存して下さい。
ファイル名はとりあえず "アドインのテスト" にしてください
Excelを終了して下さい。
 
再度Excelを開いて、アドイン管理を開きます。
すると
"アドインのテスト"
があるはずです。組み込んで下さい。
 
ブックを新規作成して下さい。すると
App_NewWorkbookイベントが実行され
  新しいブックが追加されました。
というメッセージが出るはずです。
 
再びVBE画面を開いて下さい。
VBAProject(アドインのテスト.xlam)
とあるのでこれの ThisWorkbook を開きます。
コード次のように書き換えて
そしてVBA画面の保存ボタンで"アドインのテスト.xlam"を上書き保存
 
Private WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    MsgBox "新しいブックが追加されました。"
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim u As VBA.VbMsgBoxResult
    u = MsgBox("本当に保存しますか?", vbOKCancel)
    If u = vbCancel Then
        Cancel = True
    End If
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

コードを修正すると 変数は初期化されますよね。
なのでコードを修正後、Workbook_Openを実行して
変数App に Application を代入してから動作確認テストして下さい。
(またはExcelを再起動)
 
 
さて適当なブックを保存して下さい。
  本当に保存しますか?
と確認メッセージが出ます。このサンプルコードでは
Cancelボタンで保存をキャンセルです。
 
 

投稿日時: 18/05/08 00:42:17
投稿者: もこな2

お返事が遅くなってしまいすみません。
連休の谷間はちょっと忙しくて、実装が連休明けになってしまいました。
 
おかげさまで、やりたいことができました。
 
とりあえず、STOPステートメントなんかを使いながら研究してみて、いまだにクラスって何ぞやって状態を脱することはできてないとおもいますが、クラスモジュールを使うなりWithEventsキーワードをつかうなりして、Application自体を定義しておいて、アドインブックを開くときにインスタンス化するように設計すればいいんだな〜というところまでは何とか理解できました。
 
ずっとアドインブックからほかのブックのイベントを制御するって思っていたんですが、そうじゃなくてExcelというアプリケーション(に属するブック)のイベントだったんですね。
どうも着眼点が違っていたようで…ご教授ありがとうございました。

投稿日時: 18/05/13 11:42:58
投稿者: もこな2

閉じ忘れてました。
とじます。