Excel (VBA)

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

 
(指定なし : 指定なし)
別シートの最終行を取得 の vaioyukiさんへ
投稿日時: 19/06/25 11:17:45
投稿者: Suzu

別シートの最終行を取得
https://www.moug.net/faq/viewtopic.php?t=78368
 
回答書いているうちに解決なされたみたいなので、こちらに。。
 
vaioyukiさんが、困っている点としては2つ
 
1.VBA実行時 エラー となっている。
    → skさんからの回答で解決済み
 
2.ファイル容量を小さくしたい。
    →ファイル読み込み速度を上げたい
 
      なのかな と考えました。
 
 Excelは表計算ですから、数式が入っていて当然でしょう。
 ファイル容量として考えた時、
   「値」としての【1】
   「数式」の計算結果としての【1】
    両方、見えている分としては、【1】ですが、
    数式が入っているなら、その計算式をデータとして持っている訳ですからその分
    ファイル容量としては増えます。
 
    あくまで結果としての値を求めるなら
    「計算式が無いファイル」
    「計算式が含められたファイル」
     どっちが容量小さいか
     と考えれば、前者の方がファイル容量は小さくなります。
 
    ここまでが、前回の回答に至る考えです。
 
 

引用:
DWHシートともうひとつパターンシートがあり、この2つのシートから読み取った計算式が入力されています。
これを最終的に値コピーで新ブックに貼り付けているようです。

は、私の考えと一緒なのですが
 
提示頂いた
引用:
With ActiveSheet.Cells(1, 1).Resize(ENM_ROW.goukei, ENM_COL.saigo)
  .Calculate '再計算
【中略】
  .Columns(ENM_COL.sakujo).Delete '削除列を削除
End With

のコードを拝見すると
 
既存ブックの あるシート から、アクティブシートへの値の貼り付けであり
・あるシート は 数式の含まれたシート
・アクティブシートは 値のみ のシート
 
 なので、「新ブック」では無い ので ちょっと ご説明と違う。
 
 シート二つありますから、その分容量としては大きくなる。
 
 ファイル容量を気にするのであれば 保存形式は、xlsx でしょうか?
 xls?、xlsx?、csv? 後者に行くほど 容量は小さくなりますね。
 
 
エラーが出なくなった事で解決なのであれば こちらとしては問題ありません。
何もなさそうであれば閉じます。

回答
投稿日時: 19/06/25 13:20:41
投稿者: vaioyuki

わざわざありがとうございます。
私の説明不足で申し訳ありません。
 
現状ではマクロを扱うブックと、
そのブックから新たにブックが排出されるという仕組みになっています。
マクロを扱うブックにはボタンだけがあるシート、データを貼り付けるシート、そのデータを読み取って計算式を扱うシートがあります。
計算式を扱うシートには予測してデータ分の式が入力されていて、最終行にはSUM計算で合計が表示されています。
マクロでこのシートを別で排出するときに空白行を除いて。。。という作業をしているようです。
前任者が作成したものなので確かではないですが、もしかしたら値貼り付けをそのままこのシートにしてしまっているのかもしれません。
 
今相談しているブックは500件ほどなのですが、
別ブックには2500件を超えるものもあり、なんとかならないかなと思って試行錯誤しています。
 
マクロの保存形式はxismで、
新しいブックはxlsxです。

投稿日時: 19/06/25 14:25:35
投稿者: Suzu

引用:
他ブックは2500件を超えるものあり、容量も9MBを超えて開くのも処理をするにも非常に重くなっていて

引用:
なんとかならないかな

 
なんとかしたいのは、
・xlsx
・xlsm
どちらでしょう。
 
 
新しく作成される xlsx ファイル についてなのであれば、
作成されたxlsx は、計算式は無く、値があるだけのファイルなのですよね。
 
であれば、xlsxではなく、csvで十分ですよね。
作成されたxlsx を csv として保存し、ファイル容量を確認してみてください。
 
 
xlsm なのであれば
引用:
マクロを扱うブックにはボタンだけがあるシート、データを貼り付けるシート、そのデータを読み取って計算式を扱うシートがあります。

との事ですから、
 
xlsm に、csvから読み込んだデータを保存しているのですよね。
結果は、xlsxに吐き出すのですから、xlsmに保存しておく必要はない。
 
(途中計算等、バッファーとして保存しておくなら判るけど。。
 そのファイルを再度開くときには、バッファー確認時。
 毎回処理用の xlsmファイル に データが保存されていなきゃならない必要性が無い)
 
 
流れ。
0. xlsm の コピーを取りバックアップとします。
1. xlsm の csv読み込みデータを消す
2. 計算式を扱うシートの 項目名と、データ先頭行を除き削除
3. ここでxlsm を 保存
      この段階で、データ件数は 1件のみですからファイル容量としても少なくなっている
 
4. マクロの流れを CSVファイルからデータ読み込み
5. 計算式を扱うシート側で、オートフィルにて CSVデータの読み込みの最終行までの計算を行わせるようにする
   必要なら、レコード削除も行う。
6. 計算結果 を CSV (xlsx) に吐き出す (当該 xlsmファイルは 必要なら別名で保存)
 
要は、xlsm ファイルはテンプレートとして使えば良い。
こうすれば、処理前のxlsm はファイル容量が小さくて済みますよね。

回答
投稿日時: 19/06/25 15:31:19
投稿者: vaioyuki

ありがとうございます。
 
xlsmの方です。
現在こちらには[パターンC][DWH][マクロ][集計](先ほどの質問ではBシートとなっていたものです)があります。
[パターンC][DWH]はCSVを読み込んできて貼り付けていて、[集計]シートに計算式があります。
毎回作成終わると保存せずに終了するので集計シートには式だけが残る状態です。
前回教えてもらったVBAで計算する方法にすると、539KB⇒180KBになりました。
9MB以上あるのは別ブックで、こちらがうまくいったら9MBあるブックもVBAで計算したいなと思っています。
 
説明が下手で申し訳ありませんがSuzuさんがおっしゃるようにテンプレートとしてこのマクロブックは使いたいと思っています。
ただ、
また別スレにあげさせていただいてますが前任者の流れの続きとして作成したとこでコケています。。。(^^;)

投稿日時: 19/06/25 17:08:23
投稿者: Suzu

別シートの最終行を取得からの続き
https://www.moug.net/faq/viewtopic.php?t=78374
の分。。
 
 
1.新規にワークブックを作成
 
2.ワークシートが3つであることの確認。
   なければ追加し、3つにしてください。
   VBE画面ではなく、
   ふつうのExcel画面で「ワークシート名」
   Sheet1、Sheet2、Sheet3 となっているのを確認。
 
2. 「ワークシート名」 を
   sh1、sh2、sh3 に変える。
 
3. ワークシートを 左から順に
   sh2、sh1、sh3 に変える。
 
4. VBEを開く
 
5. ワークシートの「オブジェクト名」が
   Sheet1、Sheet2、Sheet3 となっているのを確認。
 
6. ワークシートの「オブジェクト名」を
   wst1、wst2、wst3 に変える。
 
 

コード
Sub sumple()
OnError Resume Next
Dim wst As Worksheet
Dim i As Long

Debug.Print "Index" & vbTab; "ObjName" & vbTab & "WorkSheetName"
For i = 1 To ActiveWorkbook.Worksheets.Count
  Debug.Print i & vbTab & Worksheets(i).CodeName & vbTab & Worksheets(i).Name
Next

WorkSheets(1).Cells(1,1).Value = 1
WorkSheets("sh1").Cells(2, 1).Value = 2
WorkSheets("wst1").Cells(3, 1).Value = 3

'sh2.Cells(1,1).Value = 1   'コメントアウト外すとコンパイルエラー
wst2.Cells(2,1).Value = 2 
End Sub

 
結果
Index ObjName WorkSheetName
1 wst2 sh2
2 wst1 sh1
3 wst3 sh3
 
 
と、
ワークシート名
sh2 A1:1、A2:2
Sh1 A2:2
 
Indexは、Excel画面で左から順番の番号。
 
ObjectName
wst2.Cells(2,1).Value = 2 で ワークシート「sh2」 のA2 に 2が代入されます。
 
AccessのVBAを使ったことがあるなら、コード名 の方がしっくりくると思います。
 
Accessでも フォーム名 frm1 のコントロール txtBox を
frm1.Controls("〜〜") や、 frm.txtBox で 参照します。 この時の frm と同じですね。
 
WorkSheetName は
Excel画面上の ワークシート名
 
 です。
 
WorkSheets を使うのは、VBE画面ではなく、通常のExcel画面で確認しましょう。

回答
投稿日時: 19/06/26 14:09:23
投稿者: vaioyuki

ありがとうございます。
物分かりの悪い私ですがなんとなくわかりました。
丁寧に詳しくわかりやすく教えていただきありがとうございます。
 
 
 
Worksheets ⇒ エクセル画面上に記載しているシート名(パターンC等)
Sheets ⇒ VBA画面のシート名(Sheet1等)
 
使い分けとしてはどちらを使っても問題がないということでしょうか?
 

引用:
WorkSheets(1).Cells(1,1).Value = 1
WorkSheets("sh1").Cells(2, 1).Value = 2
WorkSheets("wst1").Cells(3, 1).Value = 3

 
これは、
Worksheetsと書かれているのにエクセル画面上に記載しているもの以外は無効
ということですか?
 
引用:
'sh2.Cells(1,1).Value = 1 'コメントアウト外すとコンパイルエラー
wst2.Cells(2,1).Value = 2

 
こちらも同様に、
コメントアウトを外すとsh2は正式にはWorksheets("sh2")とかくべきということでしょうか?
 

回答
投稿日時: 19/06/26 16:08:09
投稿者: vaioyuki

むむむ。
またちょっと頭がごっちゃになっています。(^^;)
 
https://www.moug.net/faq/viewtopic.php?t=78368
 
今もまだこちらと格闘中なのですが、
前任者が作ったボタンには保存先などを最初に指定してCSVファイルとExcelファイルを読み込む作業が必要だったので計算式だけ出来るようにモジュールで作成しました。
 
モジュールで作成するのと、
フォーム画面のマクロで作成するときにWorksheetsとSheetsの違いはありますか?
モジュールで作成したときはうまくいったのに、
フォーム画面のマクロと組合すと出来なくなります。
 

With ThisWorkbook.Worksheets("集計") 'パターンCシートの最終行まで集計シートに計算式を自動入力

    .Range("A" & lngFirstFormuraRow & ":A" & lngLastFormuraRow).Formula = "=Match(Substitute($g" & lngFirstFormuraRow & ",""-"",""""),DWH!$E:$E,0)"
    .Range("B" & lngFirstFormuraRow & ":b" & lngLastFormuraRow).Formula = "=TRIM(パターンC!$A2)"
  
   〜〜〜〜〜〜〜〜〜〜 省 略 〜〜〜〜〜〜〜〜〜〜

  .Range("AD" & lngFirstFormuraRow & ":AD" & lngLastFormuraRow).Formula = "=IFERROR(OFFSET(DWH!$T$1,集計!$A3-1,0),"""")"    
    
End With

[color=red]Sheets("Sheet1").Select[/color]

Goukei = Range("J1").End(xlDown).Row + 1


With Range("J" & Goukei) '合計を最終行に追加

    .Formula = "=SUM(J3:J" & (Goukei - 1) & ")"
    .AutoFill Destination:=.Resize(1, 20)

End With

With Range("B3:AD" & Goukei) '全てに格子線を引く

    .Borders.LineStyle = xlContinuous
    
End With

Sheet1.copy 'シートを新しいブックにコピーする

    Range("A:AD").copy
    Range("A1").PasteSpecial Paste:=xlPasteValues '値の貼り付け
    Application.CutCopyMode = False 'コピーモード解除

With ActiveSheet
    .Columns(1).Delete '1列目を削除
    Range("A1").Select
End With

 
 
この赤線部分、
モジュールの時はなくても動きましたが、
フォームマクロの方に記述しなおすと、最初に読み込んで開いてきたExcelファイルの方に合計が入ってしまいました。
なのでSelectで集計シートを指定しようと思いましたが、
SheetsともWorksheetsでもエラーになります。

投稿日時: 19/06/26 17:27:50
投稿者: Suzu

引用:
Worksheets ⇒ エクセル画面上に記載しているシート名(パターンC等)
Sheets ⇒ VBA画面のシート名(Sheet1等)

 
 
WorkSheets と Sheets 使い分け を しましょう。
 
Sheetsコレクション
  Excelのシート上で右クリック「挿入」で
    ワークシート、グラフ、Excel 4.0 マクロ、MS Excel 5.0 ダイアログ があると思います。
    これら全てのオブジェクトを含むのが Sheetsコレクション
 
WorkSheetsコレクション
  Excelのシート上で右クリック「挿入」 のうちの、
  ワークシート のみが属するのが WorkSheetsコレクションです。
 
つまり
 ワークシート 「Sheet1」は、Sheetsコレクション、WorkSheetsコレクション両方に属します。
 WorkSheets コレクションは、Sheets コレクション の一部です。
 
Excel画面上の「Sheet1」は、Sheetsコレクションにも、WorkSheetsコレクションにも属します
 

投稿日時: 19/06/26 17:29:03
投稿者: Suzu

引用:
Worksheets ⇒ エクセル画面上に記載しているシート名(パターンC等)
Sheets ⇒ VBA画面のシート名(Sheet1等)
 
使い分けとしてはどちらを使っても問題がないということでしょうか?

引用:
シート名もどちらを使うのが正しいのかもわかってません。
画像が貼り付けられないのでまた伝わらないかもしれませんが、
VBAの画面、プロジェクトエクスプローラーに表記されているシート名?が、
 
Sheet1(集計)
Sht_csv(パターンC)
Sht_dwh(DWH)
Sht_macro(マクロ)

の 解消をしてほしかったのです。
 
 
[quote]WorkSheets(1).Cells(1,1).Value = 1
WorkSheets("sh1").Cells(2, 1).Value = 2
WorkSheets("wst1").Cells(3, 1).Value = 3
 
結果
sh2 A1:1、A2:2[quote]
 
ワークシートsh1 の A1に 1 、 A2 に 2 が 代入されている
                   A3 に3が代入されていない つまり、3の代入に失敗している
 
WorkSheets(1).Cells(1,1).Value = 1 ←有効
WorkSheets("sh1").Cells(2, 1).Value = 2 ←有効
WorkSheets("wst1").Cells(3, 1).Value = 3 ←無効 オブジェクト名を指定してはダメ
 
   ※(オブジェクト名) は、オブジェクト の CodeNameプロパティでもあります
 
 
引用:
Worksheetsと書かれているのにエクセル画面上に記載しているもの以外は無効
ということですか?

 
以外では無いです。
インデックス数値 が有効ですから。
 
・エクセル画面上の 「シート名」
・エクセル画面上の 「左から数えたシート数」
が有効です。
 
 
WorkSheets コレクション
の メンバーのプロパティー
---------------------
CodeName    Name
---------------------
Sheet1    (集計)
Sht_csv    (パターンC)
Sht_dwh    (DWH)
Sht_macro    (マクロ)
 
なので、
 
WorkSheets("パターンC").Range("A1")     ○
WorkSheets("Sht_csv").Range("A1")     ×
 
 
引用:
引用:
sh2.Cells(1,1).Value = 1 'コメントアウト外すとコンパイルエラー
wst2.Cells(2,1).Value = 2

 
  
こちらも同様に、
コメントアウトを外すとsh2は正式にはWorksheets("sh2")とかくべきということでしょうか?

 
sh2.Range("A1")    ×
wst2.Range("A1")    ○
 
パターンC.Range("A1")    ×
Sht_csv.Range("A1")    ○
 
 
ワークシートオブジェクト オブジェクト名.Range("A1") は ○
ワークシートオブジェクト Name.Range("A1") は ×
 
オブジェクトの『Nameプロパティ』に、Range が含まれる訳がありませんからね。
 

投稿日時: 19/06/26 18:04:39
投稿者: Suzu

引用:
モジュールの時はなくても動きましたが、
フォームマクロの方に記述しなおすと、最初に読み込んで開いてきたExcelファイルの方に合計が入ってしまいました。

 
例えば、
引用:
Goukei = Range("J1").End(xlDown).Row + 1

これはどの シート の J1 に対して 操作しようとしているの?
 
上位シートを指定せずに、単に Range〜 を指定したら
  シートモジュールでは、そのシートの セル
  標準モジュール では、アクティブシートのセル
が操作の 対象となります。
その事を踏まえ、見直してみましょう。
 
 
 
引用:
Worksheetsと書かれているのにエクセル画面上に記載しているもの以外は無効

 
との発言に対し
 
WorkSheets("Sheet1")
 
エクセル画面に Sheet1 があるの?
 
 
当方の説明では 質問者さんの 理解の助けにはならかった様なので。。
質問があるのでしたら、ご自分でスレッドをもう一度立ててください。
(スレッドを閉じた人しか閉じれないのでこのスレッドは閉じます。)