Private _WorkBook As String = Nothing Private _WorkBookIsNew As Boolean = Nothing Public Property WorkBook As String Set(value As String) _WorkBook = value Select Case _WorkBook Case "" WorkBook_Open() _WorkBookIsNew = True Case "Close" _frgClose = True WorkBook_Close() Case "Save" xlsWorkbook.Save() Case "SaveAfterClose", "CloseBeforeSave" xlsWorkbook.Save() _frgClose = True WorkBook_Close() Case Else WorkBook_Open(_WorkBook) _WorkBookIsNew = False End Select End Set Get Return _WorkBook End Get End Property
Private Sub WorkBook_Open(Optional ByVal strFileName As String = Nothing) 'Excel アプリケーション起動 xlsApplication = New Excel.Application 'Excel の Workbooks 取得 xlsWorkbooks = xlsApplication.Workbooks 'Excel非表示 xlsApplication.Visible = False xlsApplication.DisplayAlerts = False If IsNothing(strFileName) Then '新規 Excel ファイルを開く xlsWorkbook = xlsWorkbooks.Add() Else '既存 Excel ファイルを開く xlsWorkbook = xlsWorkbooks.Open(strFileName) End If 'Excel の Worksheets 取得 xlsWorkSheets = xlsWorkbook.Worksheets 'Excel の Worksheet 取得 xlsWorkSheet = xlsWorkSheets.Item(1) xlsWorkSheet.Select() xlsApplication.Visible = ExcelVisible End Sub
Private Sub WorkBook_Close() '終了処理 'xlsRange の解放 MRComObject(xlsRange, True) 'xlsWorkSheet の解放 MRComObject(xlsWorkSheet, True) 'xlsWorkSheets の解放 MRComObject(xlsWorkSheets, True) 'xlsWorkbookを閉じる If Not xlsWorkbook Is Nothing Then xlsWorkbook.Close() 'xlsWorkbook の解放 MRComObject(xlsWorkbook, True) 'xlsWorkbooks の解放 MRComObject(xlsWorkbooks, True) 'Excelを閉じる If Not xlsApplication Is Nothing Then xlsApplication.Quit() 'xlsApplication を解放 MRComObject(xlsApplication, True) End Sub
'COM オブジェクトへの参照を解放 ''' <summary> ''' COMオブジェクトの参照カウントをデクリメントします。 ''' </summary> ''' <typeparam name="T">(省略可能)</typeparam> ''' <param name="objCom"> ''' COM オブジェクト持った変数を指定します。 ''' このメソッドの呼出し後、この引数の内容は Nothing となります。 ''' </param> ''' <param name="force"> ''' すべての参照を強制解放する場合はTrue、現在の参照のみを減ずる場合はFalse。 ''' </param> Friend Sub MRComObject(Of T As Class)(ByRef objCom As T, Optional ByVal force As Boolean = False) If objCom Is Nothing Then Return Try If System.Runtime.InteropServices.Marshal.IsComObject(objCom) Then If force Then System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objCom) Else System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom) End If End If Finally objCom = Nothing End Try End Sub ----------------------------------------------------------------------------------------------------
上記ExcelExクラスがベースクラスで、子クラス、孫クラスがあります。 子クラスで、Data As Microsoft.Office.Interop.Excel.Rangeと言うヶ所があるので、 （必要無いとは思っているものの）MRComObject(Data)と入れてあります。
ちょっと書いている途中で思いついたので、ベースクラスのみ呼び出して閉じる… ---------------------------------------------------------------------------------------------------- Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click ExEx1 = New ExcelEx() ExEx1.WorkBook = path & "Test.xlsx" ExEx1.xlsApplication.WindowState = Excel.XlWindowState.xlMaximized ExEx1.Sheets(1) ExEx1.WorkBook = "Close" End Sub ---------------------------------------------------------------------------------------------------- を実行してもやはりゴミが残ります。
Sheetsの実装はこうなっております。 ---------------------------------------------------------------------------------------------------- Public Sub Sheets(strSheetName As String) xlsWorkSheet = xlsApplication.Sheets(strSheetName) If SheetVisible Then xlsWorkSheet.Select() End Sub ----------------------------------------------------------------------------------------------------
>>Friend xlsApplication As Excel.Application = Nothing > これらの COM オブジェクトを非 Private として公開されていますが、 > Friend で公開するにしても、ReadOnly な Property にしておくべきかと。 > > ただし ReadOnly だとしても、ExcelEx 外から Open や Close 等、あるいは Cells 等への > アクセスは無制限に可能となってしまいますので、COM オブジェクトを直接公開すると、 > ExcelEx だけでは、参照と解放の管理をまかないきれなくなることがあります。
明記はしませんでしたが、IDisposableも実装しています。（実装パターンそのまま） ---------------------------------------------------------------------------------------------------- Private disposedValue As Boolean Protected Overridable Sub Dispose(disposing As Boolean) If Not disposedValue Then If disposing Then ' TODO: マネージド状態を破棄します (マネージド オブジェクト) WorkBook_Close() End If
' TODO: アンマネージド リソース (アンマネージド オブジェクト) を解放し、ファイナライザーをオーバーライドします ' TODO: 大きなフィールドを null に設定します disposedValue = True End If End Sub
（以下、Excel側VBAコード） ---------------------------------------------------------------------------------------------------- Private Sub Workbook_Open() Dim row As Integer Dim work As String Dim No As String Dim FolderName As String Dim i As Integer
On Error Resume Next
If ActiveWorkbook.Name = "御見積書_原紙_1.xlsm" Or ActiveWorkbook.Name = "御見積書_原紙_2.xlsm" Then Check = True If Sheets("【設計用】見積書").Range("H1").Value <> "No." Then Sheets("【原紙】【設計用】材料一覧表").Select Cells.Select Selection.Copy Sheets("【設計用】材料一覧表").Select Range("A1").Select ActiveSheet.Paste Sheets("【原紙】【設計用】見積書").Select Cells.Select Selection.Copy Sheets("【設計用】見積書").Select Range("A1").Select ActiveSheet.Paste End If
NewNo Check = False End If
UserForm1.Show (False)
Range("A5").Select End Sub ----------------------------------------------------------------------------------------------------
■No35245に返信(たこさんの記事) > Public Sub Sheets(strSheetName As String) > xlsWorkSheet = xlsApplication.Sheets(strSheetName) > If SheetVisible Then xlsWorkSheet.Select() > End Sub
https://www.petitmonte.com/bbs/answers?question_id=25306 >> 魔界の仮面弁士 2005-12-22 11:29:19 No: 128686 >> どうやら、レイトバインドの場合、 >> Dim Hs As Object = S.HPageBreaks >> Dim H As Object = Hs.Add(R) >> の処理を行った時点で、変数 R の参照カウントが >> 増加してしまっていたようです。
■No35244に返信(魔界の仮面弁士さんの記事) ① 魔界の仮面弁士様のアドバイスを元に、各オブジェクトをプライベートに設定し、プロパティとメソッドを分けました。 ---------------------------------------------------------------------------------------------------- 'Excel のアプリケーション参照用オブジェクト Private _xlsApplication As Excel.Application = Nothing Friend ReadOnly Property xlsApplication As Excel.Application Get Return _xlsApplication End Get End Property
Friend Sub New() 'Excel アプリケーション起動 _xlsApplication = New Excel.Application 'Excel の WorkbookBeforeClose イベントを取得 'AddHandler xlsApplication.WorkbookBeforeClose, AddressOf xlsApplication_WorkbookBeforeClose 'Excel の Workbooks 取得 _xlsWorkBooks = _xlsApplication.Workbooks 'Excel非表示 _xlsApplication.Visible = False _xlsApplication.DisplayAlerts = False End Sub
Friend Sub WorkBook_Open(strFileName As String) '既存 Excel ファイルを開く _xlsWorkBook = xlsWorkBooks.Open(strFileName) 'Excel の Worksheets 取得 _xlsSheets = xlsWorkBook.Worksheets 'Excel の Worksheet 取得 _xlsWorkSheet = CType(xlsSheets.Item(1), Excel.Worksheet) ' （a) _xlsWorkSheet.Select() _xlsApplication.Visible = ExcelVisible End Sub
Friend Sub Workbook_Save() If Not WorkBookName Is Nothing Then _xlsWorkBook.SaveAs(Filename:=WorkBookName) Else _xlsWorkBook.Save() End If End Sub
Friend Sub WorkBook_Close() '終了処理 'xlsRange の解放 MRComObject(xlsRange) 'xlsWorkSheet の解放 MRComObject(xlsWorkSheet) 'xlsWorkSheets の解放 MRComObject(xlsSheets) 'xlsWorkbookを閉じる xlsWorkBook.Close(SaveChanges:=False) 'xlsWorkbook の解放 MRComObject(xlsWorkBook) 'xlsWorkbooks の解放 MRComObject(xlsWorkBooks) 'Excelを閉じる xlsApplication.Quit() 'xlsApplication を解放 MRComObject(xlsApplication) End Sub
Friend Sub WorkSheet_Select(strSheetName As String) Dim check As Boolean = False For Each sh In xlsSheets If sh.Name = strSheetName Then check = True Exit For End If Next If Not check Then _xlsWorkSheet = xlsApplication.Sheets.Add() xlsWorkSheet.Name = strSheetName Else _xlsWorkSheet = xlsApplication.Sheets(strSheetName) End If If SheetVisible Then xlsWorkSheet.Select() End Sub ----------------------------------------------------------------------------------------------------
■No35247に返信(魔界の仮面弁士さんの記事) ② >>xlsWorkSheet = xlsWorkSheets.Item(1) > .Item の戻り値は Object のはずですので、明示的にキャストしましょう。
Option Strict Onをすると『Option Strict On では、遅延バインディングを使用できません。』といっぱい怒られます(^^ゞ 調べたところ… （https://learn.microsoft.com/ja-jp/dotnet/visual-basic/misc/bc30574 『明示的に書け』だそうで…
例えばシートを選択する処理… ---------------------------------------------------------------------------------------------------- Friend Sub WorkSheet_Select(strSheetName As String) Dim check As Boolean = False For Each sh In xlsSheets If sh.Name = strSheetName Then '←ここで怒られましたが、これは『Ctype(sh,Excel.Worksheet).Name = strSheetName』とする事で解決。 check = True Exit For End If Next If Not check Then _xlsWorkSheet = xlsApplication.Sheets.Add() '←ここでも怒られますが、『CType(xlsApplication.Excel.Sheets, Excel.Worksheet).Add()』としてみましたが、怒られたままです＞＜ ’■No35248に返信(魔界の仮面弁士さんの記事) 参考情報： https://www.petitmonte.com/bbs/answers?question_id=25306 ここの… '>> 基本的に、『.』を連続して使うのは赤信号だと思ってください。ここにヒントがある気はしまして… '『 CType(CType(xlsApplication.Excel, Excel.Application).Sheets, Excel.Worksheet).Add()』とかしてみましたが、駄目でした＞＜ 'いろいろやってみました。『CType(xlsSheets.Add(), Excel.Worksheet)』で良さそうな感じが…（まだエラーだらけで実行出来ていません(^^ゞ xlsWorkSheet.Name = strSheetName Else _xlsWorkSheet = xlsApplication.Sheets(strSheetName) '←怒られた '『 CType(xlsApplication.Sheets(strSheetName), Excel.Worksheet)』で良さそう…
End If If SheetVisible Then xlsWorkSheet.Select() End Sub ----------------------------------------------------------------------------------------------------
例えばシートを選択する処理最終版… ---------------------------------------------------------------------------------------------------- Friend Sub WorkSheet_Select(strSheetName As String) Dim check As Boolean = False For Each sh In xlsSheets If CType(sh, Excel.Worksheet).Name = strSheetName Then check = True Exit For End If Next If Not check Then _xlsWorkSheet = CType(xlsSheets.Add(), Excel.Worksheet) xlsWorkSheet.Name = strSheetName Else _xlsWorkSheet = CType(xlsSheets(strSheetName), Excel.Worksheet) End If If SheetVisible Then xlsWorkSheet.Select() End Sub ----------------------------------------------------------------------------------------------------
Form1クラス ExcelExテスト用起動処理 ---------------------------------------------------------------------------------------------------- Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click ExEx1 = New ExcelEx() ExEx1.WorkBook_Open(見積書 & "【テスト】見積り一覧.xlsx") ExEx1.xlsApplication.WindowState = Excel.XlWindowState.xlMaximized ExEx1.WorkSheet_Select("見積一覧") ExEx1.WorkBook_Close() ExEx1 = Nothing End Sub ----------------------------------------------------------------------------------------------------
ExcelExクラス（参照しているプロパティ、メソッドを全記載） ---------------------------------------------------------------------------------------------------- Option Explicit On Option Strict On
Imports Microsoft.Office.Interop
Public Class ExcelEx
'Excel のアプリケーション参照用オブジェクト Private _xlsApplication As Excel.Application = Nothing Friend ReadOnly Property xlsApplication As Excel.Application Get Return _xlsApplication End Get End Property
'Excel の Workbooks 参照用オブジェクト (Workbook の Collection) Private _xlsWorkBooks As Excel.Workbooks = Nothing Friend ReadOnly Property xlsWorkBooks As Excel.Workbooks Get Return _xlsWorkBooks End Get End Property
'Excel の Workbooks 内の1個の Workbook 参照用オブジェクト Private _xlsWorkBook As Excel.Workbook = Nothing Friend ReadOnly Property xlsWorkBook As Excel.Workbook Get Return _xlsWorkBook End Get End Property
'Excel の Workbook 内の Worksheets 参照用オブジェクト (Worksheet の Collection) Private _xlsSheets As Excel.Sheets = Nothing Friend ReadOnly Property xlsSheets As Excel.Sheets Get Return _xlsSheets End Get End Property
'Excel の Sheets 内の1個の Worksheet 参照用オブジェクト Private _xlsWorkSheet As Excel.Worksheet = Nothing Friend ReadOnly Property xlsWorkSheet As Excel.Worksheet Get Return _xlsWorkSheet End Get End Property
'Excel の Sheet 内の1個の Range 参照用オブジェクト Friend Property xlsRange As Excel.Range
Private _ExcelVisible As Boolean = True Friend Property ExcelVisible As Boolean Set(value As Boolean) _ExcelVisible = value If Not _xlsApplication Is Nothing Then _xlsApplication.Visible = _ExcelVisible End Set Get Return _ExcelVisible End Get End Property
Private _SheetVisible As Boolean = True Friend Property SheetVisible As Boolean Set(value As Boolean) _SheetVisible = value If Not _xlsApplication Is Nothing And _SheetVisible Then _xlsWorkSheet.Select() End Set Get Return _SheetVisible End Get End Property
Friend Property CellsVisible As Boolean = True
Friend Property WorkBookName As String = Nothing
Friend Sub New() 'Excel アプリケーション起動 _xlsApplication = New Excel.Application '←(a 'Excel の Workbooks 取得 _xlsWorkBooks = _xlsApplication.Workbooks 'Excel非表示 _xlsApplication.Visible = False _xlsApplication.DisplayAlerts = False End Sub
Friend Sub WorkBook_Open(strFileName As String) '既存 Excel ファイルを開く _xlsWorkBook = xlsWorkBooks.Open(strFileName) 'Excel の Worksheets 取得 xlsSheets = xlsWorkBook.Worksheets 'Excel の Worksheet 取得 _xlsWorkSheet = CType(xlsSheets.Item(1), Excel.Worksheet) _xlsWorkSheet.Select() _xlsApplication.Visible = ExcelVisible '←(b End Sub
Friend Sub WorkSheet_Select(strSheetName As String) Dim check As Boolean = False For Each sh In xlsSheets If CType(sh, Excel.Worksheet).Name = strSheetName Then check = True Exit For End If Next If Not check Then _xlsWorkSheet = CType(xlsSheets.Add(), Excel.Worksheet) xlsWorkSheet.Name = strSheetName Else _xlsWorkSheet = CType(xlsSheets(strSheetName), Excel.Worksheet) End If If SheetVisible Then xlsWorkSheet.Select() End Sub
'COM オブジェクトへの参照を解放 ''' <summary> ''' COMオブジェクトの参照カウントをデクリメントします。 ''' </summary> ''' <typeparam name="T">(省略可能)</typeparam> ''' <param name="objCom"> ''' COM オブジェクト持った変数を指定します。 ''' このメソッドの呼出し後、この引数の内容は Nothing となります。 ''' </param> ''' <param name="force"> ''' すべての参照を強制解放する場合はTrue、現在の参照のみを減ずる場合はFalse。 ''' </param> Friend Sub MRComObject(Of T As Class)(ByRef objCom As T, Optional ByVal force As Boolean = False) If objCom Is Nothing Then Return Try If System.Runtime.InteropServices.Marshal.IsComObject(objCom) Then If force Then System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objCom) Else System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom) End If End If Finally objCom = Nothing End Try End Sub ----------------------------------------------------------------------------------------------------
■No35250に返信(たこさんの記事) > Private _xlsApplication As Excel.Application = Nothing > Friend ReadOnly Property xlsApplication As Excel.Application > Get > Return _xlsApplication > End Get > End Property これではあまり意味が無いと思いますよ。結局のところ、 Friend ReadOnly xlsApplication As Excel.Application な読み取り専用フィールドと、さほど変わらないように見えます。
COM オブジェクトを直接公開してしまうと、ExcelEx の外部で ExEx1.xlsApplication.Workbooks.Add() などと書かれてしまえば、COM オブジェクトの解放漏れに繋がります。
IDisposable としてカプセル化するのであれば、Excel の COM オブジェクトは 外部からは直接操作できないようにして、すべてクラス内に隠蔽します。 戻り値と返すのも COM オブジェクトではなく、.NET のマネージオブジェクトにします。
> Option Strict Onをすると『Option Strict On では、遅延バインディングを使用できません。』といっぱい怒られます(^^ゞ 書き換えながら、御自身で正解に向かったところもあるようですが、一応ひとつひとつ見ていきましょうか。
> If Not check Then > _xlsWorkSheet = xlsApplication.Sheets.Add() この処理には、問題点が 2 つあります。
1 つは「.」による COM オブジェクトの連続呼び出しであり、 Sheets プロパティから返されるコレクションの解放が漏れています。
COM に対する For Each は、COM の IEnumVARIANT インターフェイスを通じて行われるため、 稀に、これが解放の妨げになることがあるためです。 https://learn.microsoft.com/ja-jp/windows/win32/api/oaidl/nn-oaidl-ienumvariant https://divakk.co.jp/aoyagi/csharp_tips_vssenum.html
なので For Each ループではなく、For ループを Count プロパティと共に使う方が安全です。 https://qiita.com/mima_ita/items/aa811423d8c4410eca71
> If Not check Then > _xlsWorkSheet = xlsApplication.Sheets.Add() '←ここでも怒られますが、『CType(xlsApplication.Excel.Sheets, Excel.Worksheet).Add()』としてみましたが、怒られたままです＞＜ xlsApplication は Excel.Application 型ですよね。
そもそも「xlsApplication.Sheets(strSheetName)」の Sheets メンバーというモノは、 Property Sheets(n As String) As Object なプロパティでもなければ Function Sheets(n As String) As Object なメソッドでもありません。
その実態は ReadOnly Property Sheets() As Excel.Sheets という『引数の無いメンバー』です。
こうした「既定のプロパティ」によって、「『.』の連続」が見えにくくなる事象は 他の COM コレクション型に対しても発生します。たとえば Dim r As Excel.Range = Sheet1.Cells(1, 1) 'Cells プロパティの引数に 1,1 を渡しているように見えるが…? r.Value = 123 なども解放漏れ要因になるので NG ですね。
上記で解放漏れを防ぐには、 Dim r1 As Excel.Range = Sheet1.Cells '実は Cells プロパティは、「引数を持たない」仕様です Dim r2 As Excel.Range = r1(1, 1) 'そしてこれは、Range オブジェクトの .Item(1, 1) に相当します r2.Value = 123 MRComObject(r2) MRComObject(r1) などのような操作が求められます。
> For Each sh In xlsSheets > If CType(sh, Excel.Worksheet).Name = strSheetName Then > check = True > Exit For > End If > Next
For Each を For に置き換えるとこんな感じ。
Dim found As Boolean = False For n As Integer = 1 To xlsSheets.Count Dim ws = DirectCast(xlsSheets(n), Excel.Worksheet) Dim nm = ws.Name MRComObject(ws) If nm = strSheetName Then found = True Exit For End If Next
■No35251に返信(たこさんの記事) > Dim check As Boolean = False > For Each sh In xlsSheets > If CType(sh, Excel.Worksheet).Name = strSheetName Then > check = True > Exit For > End If > Next
ここがマズイですね。 MRComObject(sh) が明らかに漏れています。
また、 No35252 でも紹介したように、COM オブジェクトに対して For Each を使うと、 System.Runtime.InteropServices.ComTypes.IEnumVARIANT インターフェイス (を実装した EnumeratorViewOfEnumVariant クラス) の解放を行うことが難しくなるので For ループ または Do ループ あるいは While ループ に置き換えることをお奨めします。
> Private _ExcelVisible As Boolean = True > Friend Property ExcelVisible As Boolean > Set(value As Boolean) > _ExcelVisible = value > If Not _xlsApplication Is Nothing Then _xlsApplication.Visible = _ExcelVisible > End Set > Get > Return _ExcelVisible > End Get > End Property
フラグ管理したいのであれば _xlsApplication などの COM オブジェクトを公開してはいけません。 ExEx1.xlsApplication.Visible を直接操作されたら、上記の _ExcelVisible フラグが連動しなくなりますよね?
このスレッドをツリーで一括表示