Excel (VBA)

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

 
(Windows 10 Pro : その他)
開いたExcelの場合セル参照時に重たくなる
投稿日時: 21/06/30 14:26:03
投稿者: n.wakama

他のExcelファイルで作成したデータを読み込む配列を読み込むマクロを組んだのですが
他のExcelファイルが開かれていると値の参照時に異常に処理が重たくなります
何か解決方法はないでしょうか
 
Excelファイルを分けた理由ですが
他に作成したファイルが15個発生するため、
1. 1ファイルにすると処理が重たくなる
2. ファイルサイズが大きくなりすぎる
3. 15個のファイルは時期が過ぎると新しいファイルに1個ずつ切り替わっていく
 
という条件があるためです
原因が特定できないため、ご助力いただければと思います
 
 
Sub ボタン2_Click()
 
 
Dim ex As Excel.Application '// 処理用Excel
    Dim wb As Workbook
    Dim sPath As Variant '// ブックファイルパス
    Dim Filename As Variant '//この工事横断ファイル名前
    Dim r As Range '// 取得対象のセル範囲
    Dim sht As Worksheet '// 参照シート
    Dim bFlg As Boolean
    Dim i As Integer
    Dim j As Integer
    Dim n_array(81, 144) As Variant
    Dim n_range As Range
     
    Dim n As Integer
    Dim kouzi_no As Variant
     
     
    Application.ScreenUpdating = False
    '// 開くブックを指定
    For n = 1 To 15 Step 1
     
    sPath = Worksheets("全工事条件").Cells(n + 3, 15).Value
    Filename = Worksheets("全工事条件").Cells(1, 13).Value
    kouzi = Worksheets("全工事条件").Cells(n + 3, 11).Value
     
    If sPath = "" Then
     
    GoTo label1
    End If
    '// 既に開かれているか確認
    bFlg = IsBookOpened(sPath)
     
    '// 開かれている場合
    If bFlg = True Then
        Set ex = New Excel.Application
        '// 新規Excelで読み取り専用で開く
        Set wb = ex.Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    Else
        '// 現ブックで読み取り専用で開く
        Set wb = Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    End If
     
    Set n_range = wb.Worksheets("工事(表示計算用)").Range("F7").Resize(81, 144)
    For i = 1 To 81 Step 1
    For j = 1 To 144 Step 1
     
     
    n_array(i, j) = n_range.Cells(i, j).Value
 
 
'この上の行で処理が重くなる
     
    Next
    Next
    Workbooks(Filename).Activate
     
    Worksheets(kouzi).Cells(7, 5).Resize(UBound(n_array, 1), UBound(n_array, 2)) = n_array()
     
    If bFlg = False Then
    wb.Close
     
    End If
     
label1:
    Next
 
Application.ScreenUpdating = True
 
 
End Sub
 
 
Function IsBookOpened(a_sFilePath As Variant) As Boolean
    On Error Resume Next
     
    '// 保存済みのブックか判定
    Open a_sFilePath For Append As #1
    Close #1
     
    If Err.Number > 0 Then
        '// 既に開かれている場合
        IsBookOpened = True
    Else
        '// 開かれていない場合
        IsBookOpened = False
    End If
End Function

回答
投稿日時: 21/06/30 16:41:02
投稿者: sk

引用:
他のExcelファイルで作成したデータを読み込む配列を読み込むマクロを組んだのですが
他のExcelファイルが開かれていると値の参照時に異常に処理が重たくなります

引用:
Dim n_array(81, 144) As Variant

'この変数は配列でなくてよい(理由は後述)
Dim n_array As Variant
 
引用:
If bFlg = True Then
    Set ex = New Excel.Application

If bFlg = True Then
    '追加起動させるインスタンスは 1 つだけでよい
    If ex Is Nothing Then
        Set ex = New Excel.Application
    End If
 
引用:

    Set n_range = wb.Worksheets("工事(表示計算用)").Range("F7").Resize(81, 144)
    For i = 1 To 81 Step 1
    For j = 1 To 144 Step 1
      
      
    n_array(i, j) = n_range.Cells(i, j).Value
  
  
'この上の行で処理が重くなる
      
    Next
    Next

'2 つ以上のセルを含むセル領域の Value プロパティは 2 次元配列を返すので、
'それを Variant 型の変数に渡せばよい。ループ不要。
n_array = wb.Worksheets("工事(表示計算用)").Range("F7").Resize(81, 144).Value
 
引用:
Worksheets(kouzi).Cells(7, 5).Resize(UBound(n_array, 1), UBound(n_array, 2)) = n_array()

Worksheets(kouzi).Cells(7, 5).Resize(UBound(n_array, 1), UBound(n_array, 2)) = n_array
 
引用:
label1:
    Next

label1:
    Next
    '2個目のインスタンスが起動していたら終了させる
    If Not ex Is Nothing Then
        ex.Quit
        Set ex = Nothing
    End If

投稿日時: 21/06/30 17:07:08
投稿者: n.wakama

>> sk様
 
ご回答ありがとうございます
ご指摘通りで劇的に改善されました
ちなみにですが、開いているファイルと開いていないファイルとではまだ処理に開きがあるのですが
原因としては何があるのでしょうか?
 
ファイルを見てみないと不明なところもあるかと思いますが、開いているファイルにアクセスしていることに味噌がありそうですが
ご存じでしたらご回答願います
 
 

回答
投稿日時: 21/06/30 17:25:45
投稿者: sk

追記:

引用:

If bFlg = False Then
wb.Close
  
End If

'元のコードの場合、bFlg の値が True だとその時点で wb が参照しているブックが
'ずっと開きっぱなしになる。もう使わないなら普通に閉じればよい。
wb.Close
Set wb = Nothing
 
引用:
ちなみにですが、開いているファイルと開いていないファイルとでは
まだ処理に開きがあるのですが原因としては何があるのでしょうか?

「コードを実行させている Excel のインスタンスとは別に
もう 1 個インスタンスを起動させる」という処理自体が
まず普通に重いんじゃないかと。

投稿日時: 21/07/01 09:38:17
投稿者: n.wakama

>>sk様
 

引用:
「コードを実行させている Excel のインスタンスとは別に
もう 1 個インスタンスを起動させる」という処理自体が
まず普通に重いんじゃないかと。

 
とのことなので
限界に近いとの判断になりました
 
一旦クローズとさせていただきます
 
ありがとうございます[/quote]