Excel VBA 質問スレ Part78

2022/10/30(日) 13:43:16.31

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑同じ内容を3行貼り付けること

ExcelのVBAに関する質問スレ
コード書き込みや作成依頼もOK

次スレは>>980が立てること
無理なら細かく安価指定

※前スレ
Excel VBA 質問スレ Part77
https://mevius.5ch.net/test/read.cgi/tech/1658009255/

-
VIPQ2_EXTDAT: checked:vvvvv:1000:512:: EXT was configured

VIPQ2_EXTDAT: checked:vvvvv:1000:512:: EXT was configured

2022/12/04(日) 20:19:07.12

>>270
そのまま別のデータをコピペすることもあるので
ピボットテーブル直接表示させたらズレませんか？

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/04(日) 20:33:04.86

>>271
どうズレるかはよく分からんけど・・・

ズレるのが怖いなら、
単純に参照すれば良い。黄色の部分は本当にシンプルな式だけで良いと思う
https://i.imgur.com/rwpq1hu.png

2022/12/04(日) 20:47:06.88

>>267
ピボットテーブルのフィルターに「取引先」を設定して
コピペするときにリンク貼り付けすれば
後はフィルターを切り替えるだけじゃないだろうか

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/04(日) 22:29:31.05

>>266
ありがとうございました
これでUboundを業務で使えます
これからもご指導よろしくお願いします

2022/12/05(月) 00:46:19.76

>>272
>>273
いずれもコピーは必要なんですね
ここに貼り付ける、ときたら
勝手に集計したのを貼ってくれといいのですけど

ちなみに零細で月に10枚程度しか請求書ださないとこは
ExcelのなんちゃってDBで十分ですかね？

フリーSQLも含めACCESSやファイルメーカーもたかってみたがデータの柔軟性がなさすぎて困った
これとこれをグループ化とかも簡単にできないし

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/05(月) 01:25:43.19

>>275
＞ちなみに零細で月に10枚程度しか請求書ださないとこは
＞ExcelのなんちゃってDBで十分ですかね？
じゃなくて、成長率
毎年の成長率が高い企業は、本当に気をつけたほうがいい

＞フリーSQLも含めACCESSやファイルメーカーもたかってみたがデータの柔軟性がなさすぎて困った
Excelがおかしな事をしているだけなんだよ
それがたまたま日本の零細～中小に合致しているだけ

2022/12/05(月) 01:33:53.42

>>276
ファイルメーカーつかってたが
レコードを日付でまとめて一覧ししてレポートだす機能もないんだよ…

2022/12/05(月) 01:36:52.68

外部からインポートもコピペではできないし
テーブルを全く計算も不得意ときてる

2022/12/05(月) 03:27:44.54

安い販売管理アプリすら買えない貧乏会社は潰れろ

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/05(月) 06:57:51.15

今どきはフリークラウドの管理ソフト（販売系、経理系、労務系）のサービスが山ほどあるでしょ
提供会社が無償提供やめたり、提供自体やめると面倒だけど
データ移行性を確保できるのならそっち方法がいい

2022/12/05(月) 07:47:07.80

>>279
>>280
それこそつかいにくい

2022/12/05(月) 11:40:12.06

>>246
一例をそのまま貼ると、こんな感じです。

Dim VLOOKUP商品名 As String
VLOOKUP商品名 = "=VLOOKUP(RC" & eCols_明細.得意先コード & "," & WsName得意先テーブル & "!R" & eRows_通常.開始行 & "C" & eCols_得意先テーブル.得意先コード & ":R" & EndRow得意先テーブル & "C" & eCount_得意先テーブル & "," & eCols_得意先テーブル.得意先名 & "," & 完全一致 & ")"
めちゃくちゃ長くて可読性が悪いなと……

2022/12/05(月) 12:03:42.76

変数に代入している際の話なら
VLOOKUP商品名 = ""
VLOOKUP商品名 = VLOOKUP商品名 & "AAA"
VLOOKUP商品名 = VLOOKUP商品名 & "BBB"
VLOOKUP商品名 = VLOOKUP商品名 & "CCC"
とかきりのいいところで分割して代入すればいいんじゃない？

2022/12/05(月) 12:08:08.63

>>282
それ自力で展開できないようじゃやばいよ

2022/12/05(月) 12:14:29.23

>>283
一応テーブルを参照する部分(VLOOKUP関数の参照範囲の箇所)を変数に代入することで少しは可読性があがったのですが、そもそも複数行へのVLOOKUP関数の埋込みをFormulaR1C1プロパティを使わなくても出来ないものなのかなあ、と思いまして
>>284
「展開」とは？

2022/12/05(月) 12:36:26.38

>>267
「GROUP BY 複数条件」じゃないの？

GROUP BY 取引先, 日付

2022/12/05(月) 18:30:47.05

>>285
「そもそも複数行へのVLOOKUP関数の埋込みをFormulaR1C1プロパティを使わなくても出来ないものなのかなあ」ってのがよくわからん
どんな機能を使おうが、セルに代入したいなら必ず一度はコードに記載しなきゃいけないんだから「eCols_明細.得意先コード」とかの変数名を短くするくらいしかないでしょ

2022/12/05(月) 20:42:26.49

>>282
VBAからvlookup関数を埋め込んだことが無いので処理のイメージがつかめないんだけど、
今やろうとしていることはvlookup関数の字面を文字列を繋げて作り上げようとしてる感じなのかな？

そもそもVlookup関数の構文は　VLOOKUP（検索値, 範囲, 列番号, 検索の型）なのだから変数を使うのであれば
検索値や範囲を変数に格納して繋げれば良いのでは？　例えば,,,

Dim val as String: val="値"
Dim rng as Range: set rng ＝ range(”A1”,"B10")
Dim col as long: col = 2
Dim FormulaStr as string

FormulaStr = "=VLOOKUP（" & val & "," & rng.Address & "," & col & ", false）"

みたいな感じ？値や範囲をセルによって可変にしたいなら変数に格納する値を変えればいいわけだし。。。
こういうことじゃないのかな？

2022/12/05(月) 20:47:00.71

そもそも数式をVBAで入力することってあんまりないよな

2022/12/05(月) 21:46:02.27

>>289
そうですねぇ、あんまりないですね。
経験としては、計算式があるのにどうしても直打ちされて消されてしまうシートに
ボタン一発で計算式を復旧させる機能を付けた時くらいですかねｗ

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/05(月) 22:43:24.64

やっぱりVlookやsumiｆみたいな
組み込み関数のほうが、普通にコードするより速く強力なんでしょうか？

2022/12/06(火) 00:26:23.27

コードが短くなるから

2022/12/06(火) 05:54:39.62

>>291
Excelに備わっている関数は強力だと思うし使い勝手が良かったりするものもありますね。
私の場合はcountifやmatch、max、minあたりはVBA上でもWorksheetfunctionからよく使います。

2022/12/06(火) 06:19:16.90

>>291
かなり最適化されてるしマルチスレッドで動作するから同じ処理ならVBAより相当速い

2022/12/06(火) 07:43:11.23

>>275
零細だったらそれこそappsheetでもいいんじゃないですか？

2022/12/06(火) 08:29:25.79

関数を直接入力するとその後にシートに何かしら入力するたびに再計算されるから面倒だわ
その再計算が気にならないってんならvbaで処理しても気にならないレベルでしょ
countifなんか特に再計算に3分とかかかる場合もあるし

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/06(火) 09:00:18.69

>>296
ピボットあたりにしてみては？
coutif遅いよ

2022/12/06(火) 11:03:13.75

>>297
意味が分かりにくくてすまん
だから関数を直接セルに入力するようなことはせずに、vbaでworksheetFunctionとかDictionaryとか使って結果だけを出力するなって意味
vbaで関数を入力するのは引き継ぎとか他人に渡すのを想定したときくらいかな

2022/12/06(火) 11:13:52.38

2行目もよくわからんし
VBAで関数を入力とかイミフなんだが

2022/12/06(火) 11:40:45.39

Application.Calculation = xlCalculationManual はとりあえず書いとくでしょ

2022/12/06(火) 13:28:57.18

つかVBAで関数使えるなんて最近知ったわ

2022/12/06(火) 13:35:48.17

>>301
お馬鹿自慢要らない

2022/12/06(火) 18:35:08.85

どんな機能も馬鹿には使えない

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/06(火) 22:10:52.72

>>300
昔書いてたけど、最近はほとんど書いてないな
PCスペックが大幅に向上し、負荷のないコードが自然に掛けるようになった

2022/12/06(火) 23:34:20.45

2行目がひどすぎる

2022/12/07(水) 01:18:20.40

なにが？

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/07(水) 07:13:11.50

演算処理能力、メモリ容量、基盤の伝送量と速度が大幅に向上し
組み込み関数による処理、VBA関数による処理などの差によるメモリ効率、処理速度を考えず
組み込み関数に依存しないコーディングを書けるようになった

言い換えると3行になるな
メモリ効率を悪化させてしまった

2022/12/07(水) 11:24:59.17

>>304
関数が入っているセルを参照するようなコードの場合
例えばCells(4, 3)にCells(3, 3)の値を10倍するような関数が入っているとして
Cells(3, 3).Value = 40
Debug.Print Cells(4, 3).Value
とやった場合、自動になっていればCells(4, 3)は400になった値が参照できて
手動ならもともと入っている値が参照できるという状態でしょ
スペックが向上、負荷のないコードだから書かないというのは危険な考えでは

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/07(水) 11:32:32.02

>>308
再計算に左右されるコードを書く事がそもそもおかしいんですわ

2022/12/07(水) 12:12:32.61

配列がらみのFunctionを作ったときにメモリを空けようと「一時的な配列」を初期化しているのだけれどもしかしてあんまり意味ない？
たとえばこのように↓

Function 戻り値配列 (引数)As Variant

Dim TempArr As Variant
(……何らかの処理)

戻り値配列 = TempArr
Erase TempArr ← これ

End Function

同じ要領で“TempDic”をRemove allしたら戻り値の連想配列も空になっちゃったけどこれはなんでなんだろう

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/07(水) 12:16:33.70

>>310
function抜けたらメモリ勝手に空くから特に意味はない
それに今は潤沢にメモリあるから、メモリの空き容量なんて気にしなくていいよ

2022/12/07(水) 12:21:53.20

>>309
なるほど
こういう回答するやつとは通じ合えないのがわかったわｗ
しかも>>311の回答も適当だしな

2022/12/07(水) 13:54:56.12

>>312
vbaって配列の要素に変数を入れ込んでから配列を初期化すると、
要素変数への参照は切れるけれどメモリ領域は解放されないのでは？
それともvbaって配列要素への参照切れで割りあてたメモリを開放するの？

2022/12/07(水) 14:42:43.86

調べてから質問しよう

2022/12/07(水) 15:06:50.61

とりあえず「Erase」を書くのはあんまり意味無いっぽいんでやめます

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/07(水) 18:33:11.05

>>312
といいつつなにも訂正できないアホ

2022/12/07(水) 18:52:51.37

>>310
> 同じ要領で“TempDic”をRemove allしたら戻り値の連想配列も空になっちゃったけどこれはなんでなんだろう
そりゃ参照を返してるだけで実体は一個しかないからRemoveAllしたら消えるよ
その後 TempDic を使わない事を明示したいなら
Set TempDic = Nothing
でもしとけばいいけどまあ関数抜けたら TempDic はなくなっちゃうんだから気にしなくていいと思うよ

2022/12/08(木) 00:25:28.15

まぁでも関数やメソッド抜けるまでは保持し続けるから単一責任原則を守ったコードに慣れるまでは
使い終わったらさっさと解放することに越したことはないよ。

最近当たったんだけどボタンを押して呼んだ関数の中で数万のインスタンスを生成する処理があって、
完了メッセージを出した後もその関数抜けるときにメモリを解放しているもんだから暫く待たされるという残念なコードを見てる。

生成した後のごちゃごちゃした処理は別関数でやってたのに実に残念なツールになってたよ。

2022/12/08(木) 01:16:01.92

意識高い系のVBA使いとか生きるの辛そうだな
プログラミング界ではそもそもVBAな時点でみんな半端者扱いなんだから気楽にやろうぜ

2022/12/08(木) 02:22:15.27

vbaを使ってて0.01秒早くしてどうすんだ？って思うときはある

2022/12/08(木) 06:20:02.03

>>318
> まぁでも関数やメソッド抜けるまでは保持し続けるから単一責任原則を守ったコードに慣れるまでは
> 使い終わったらさっさと解放することに越したことはないよ。
呼び出し元に返す話に何を言ってるんだ？

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/08(木) 07:04:26.73

ならやっぱり配列要素がプリミティブでなければ
要素そのものを解放しないと
配列だけ初期化する意味なんてあるの？

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/08(木) 07:26:25.62

というか、vbaには参照切れで自動的にメモリ開放する機能がないってことなのか

2022/12/08(木) 08:07:05.00

>>322
例えば配列にループしてインスタンスを設定するとして、そのループの中でひとつの変数にNewしてインスタンスを作成して、
そのインスタンスを配列に設定した場合、前回のループで設定したインスタンスは配列の内容が削除されれば、他に参照しているところがない限りメモリから解放されるよ。

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/08(木) 08:35:17.09

>>323
ガベージコレクションね
無いよ

逆に参照が合った時に初めてnewされるよ

2022/12/08(木) 10:09:55.21

>>322
配列の話じゃねーから絡んできて頓珍漢なこというのはやめてくれ

> 同じ要領で“TempDic”をRemove allしたら戻り値の連想配列も空になっちゃったけどこれはなんでなんだろう

2022/12/08(木) 10:17:37.27

>>326
仕様ですが何か？

2022/12/08(木) 10:23:17.17

配列の話じゃなかったんですね、すみません

2022/12/08(木) 10:27:25.77

よく見たら >>317 で解決済みじゃんか

2022/12/08(木) 12:32:14.89

>>326
それは済まなかった
でもそんなにイライラするなよ
ハゲが止まらなくばるぞ

**326** (ﾜｯﾁｮｲ 7274-JnHA) · 2022/12/08(木) 14:20:26.49

>>330
いやわたしはハゲちゃおらんよ。
君みたいなハーゲマンと一緒にしないでくれ。

2022/12/08(木) 17:59:48.27

またヘアの話してる。。
ヘア話好きだなぁお前ら

2022/12/09(金) 01:27:21.92

クエリA
UNION ALL
クエリB
→文字化け

クエリB
UNION ALL
クエリA
→正常

なんでやねん!
何で上下入れ替えると直る?
因みに、文字化けする方も文字列型にすると化けないので、
文字コードの問題ではない。

2022/12/09(金) 02:00:05.39

コードだせよ

2022/12/09(金) 02:12:16.40

毛出せよ

2022/12/09(金) 04:09:12.16

>>333
何に大してSQL投げてる？

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/09(金) 07:02:42.07

神様皆様おはようございます
神様たちのお導きで50万行＊80列のデータから
所望のデータを抽出できました
ありがとうございました！

2022/12/09(金) 13:09:40.12

OneDriveで自動保存設定してるブックに手動保存するときだけbeforesaveイベントする方法はある？

2022/12/10(土) 04:11:06.01

>>336
CSV
schema.iniも使ってる。

2022/12/10(土) 07:54:03.42

>>339
SELECT句の中にアスタリスク使ってない？
UNION句で挙動がおかしくなるのはCSVファイルじゃなくてEXCELのシートにSQL投げたときに起こるのも見たことあるけど
まぁそもそもDBに投げてる訳じゃないからある程度は仕方ないってところもあるよ。

CSVファイルはUTF-8かSJISかってのもあるけど
UNION句の上下入れ替えたら直ったというのであれば今回は関係なさそうだしね。

2022/12/11(日) 02:40:17.51

>>340
アスタリスクは使ってるけど、
A.* ってテーブルは指定してる。
入れ替えで直るから、
今困っているわけじゃないけど、
これから先使うの不安だわ。

2022/12/11(日) 02:50:20.80

あすたのリスクに備えよう

2022/12/11(日) 04:50:30.55

いつまで起きている？

2022/12/11(日) 12:55:57.38

>>338
Ruby on Rails では、before_action, after_action, around_action で、コールバックできる

before_save :a, only: [:new :edit]
after_save :b, except: [:new :edit]

onlyで、new, editが呼ばれる前にだけ、aを実行する。
逆にexceptでは、new, editが呼ばれなかった後にだけ、bを実行する

2022/12/11(日) 14:15:24.03

>>344
布教活動大変だね

**デフォルトの名無しさん** (JP 0Hcf-KKgq) · 2022/12/11(日) 16:42:01.94

>>344
もうすぐなくなる言語が何だって？
https://trends.google.co.jp/trends/explore?geo=JP&q=ruby,python,php,vba

2022/12/11(日) 20:21:22.99

Ruby信者ってどこにでも沸くな

2022/12/11(日) 23:36:23.53

サーバーにあるフォルダからPDFを検索したいのに上手くいかない
検索はかけても何もヒットしない…
どこか修正するとこありますか？
テキストボックスに入力して検索
検索結果をリストボックスに表示します

Private Sub CommandButton1_Click()
Const BASE_PATH = "\\L\設計\図面"
Dim myPath As String, myName As String
Dim FSO As Object, oFolder As Object, oSubFolder As Object, oFile As Object
On Error Resume Next
If TextBox1.Value = ""Then Exit Sub
ListBox1.Clear
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(BASE_PATH)
For Each oSubFolder In oFolder.SubFolders
For Each oFile In oSubFolder.Files
If LCase(oFile.Name) Like LCase(TextBox1.Value) &"*.pdf"Then
Debug.Print oFile.Path
myPath = oFile.ParentFolder &"\"myName = oFile.Name
ListBox1.AddItem myName
ListBox1.List(ListBox1.ListCount - 1, 1) = myPath
End If
Next
Next
Set FSO = Nothing
End Sub

2022/12/12(月) 00:49:34.01

if文の文字列比較条件でワイルドカードが使えたらいいよね

2022/12/12(月) 00:53:47.45

あ、vbaか
すまん

2022/12/12(月) 01:42:01.46

読めん
字下げから始めた方がミス探しやすいはず

2022/12/12(月) 02:19:04.59

>>348
とりあえずデバッガーした結果をクレ

2022/12/12(月) 05:04:18.82

>>348
とりあえず
For Each oFile In oSubFolder.Files
Debug.Print "Debug:" "& oFile.Name
If LCase(oFile.Name) Like LCase(TextBox1.Value) &"*.pdf"Then
としてまずファイルがあるのか確認しなよ
あとデバッグ中は
On Error Resume Next
をコメントアウトしといた方がいい
予想外のエラーを握り潰されて（当人にとっては）意味不明な挙動になることあるから

2022/12/12(月) 08:24:42.26

>>349
正規表現あるやろ

2022/12/12(月) 14:02:36.57

請求書の原紙となるsheetをコピーして、そこに配列に格納した請求情報を貼り付けるという作業を約100回繰り返したいのですが、
①先に約100回のsheetのコピーを済ませて、生成されたsheets間をループして請求情報を貼り付ける
②sheetのコピー→請求情報の貼付けを約100回ループする
どちらの処理が早いのでしょうか

2022/12/12(月) 14:07:14.79

100回程度ならどっちでも体感の差はないと思う

2022/12/12(月) 16:22:37.21

シートがクソ重かったりPCがクソ貧弱だったりするなら、
②の方がブックが小さい状態で貼り付けの処理ができるから安定して動くだろうね

2022/12/12(月) 17:07:21.38

>>357
後にまとめて印刷して突き合わせを行うため、請求書のシートの順番を配列の要素の順番と同じようにしなければなりません
よって②の場合は、配列の値を貼り付けた後に(配列の添字が 1 から始まるため) “Copy After := Worksheets( i )” を記述することになります
つまりシートをコピーする度にシート数をループ変数から取得する処理の繰り返しになるのですが(①だと “Copy After := Worksheets( 1 )” でもよい)、それでも②の方が安定しているのでしょうか

2022/12/12(月) 17:09:47.10

>>358
シート数を→シート番号を

2022/12/12(月) 17:26:03.86

請求書ってそんな適当に作ったマクロで請求していいものなのか

2022/12/12(月) 17:30:19.83

>>360
私がマクロを作る前は手書きで請求書を書いてエクセルに抽出した請求データと目視で突合していたんですよ(現在は自作した請求書を自動発行するマクロをスピードアップを目的に改修しているところです)
そういう会社もあるんです

2022/12/12(月) 18:07:39.61

iはWorksheets.Countあたりを使えばいいんじゃないの？
ただ100件程度なんてせいぜい数分でしょ
今までの時間に比べたらどっちでもいいんじゃないの

2022/12/12(月) 18:13:02.49

ただそういうモノづくりをしてる自分からしたら
かりにある得意先の請求書のみ間違えていて修正したい場合①②どちらも最初から作り直すような感じに見えるけど
そうすると他の請求書にも影響あたえるような作りに見えるんだよね
なのでそういうのを作る場合は得意先コード的なものを最初に指定してその範囲の請求書を作る
かりに修正したい得意先があればそれだけを指定すればいいみたいな作りにするけどね

2022/12/12(月) 18:21:55.06

>>362
Worksheets.Count はなおさら遅いのではないでしょうか？　シートのカウントを約100回繰り返すので
些末なこだわりと言われればそのとおりなんですが、どうせ改修するなら細かいところまでこだわった方がスッキリすると思いまして
>>363
指定した得意先の請求書のみを発行するコードも別のモジュールに書いてあります
メインは請求データの照合及び修正を行った後に約100件分をまとめて発行するフローでないと時間がかかってやってられないんです

2022/12/12(月) 18:35:02.38

それならまずシートを100作るだけのコード書いて試せば済むことじゃないの？
よくわからないけど通常と指定可能な請求書作成が別のコードになってるんだとするとメンテのコストが2倍になるのかなと思わなくもないね
まあ他人の作るものだから余計なお世話だけどね

2022/12/12(月) 18:40:55.92

>>365
確かに自分で三通りの方法をテストしてみれば済む話なんですが、もしかして私が挙げた処理はよくあるパターンであって一般解が確立されているのかなと思い、質問させていただきました

2022/12/12(月) 20:57:08.22

たかが100回で差が出るわけ無いじゃんというレス付いてるだろ
それが一般解

2022/12/13(火) 08:18:42.44

>>348
If LCase(oFile.Name) Like LCase(TextBox1.Value)&"*.pdf" Then
この部分、頭にもアスタリスクをつけてみたら？

If LCase(oFile.Name) Like “*”&LCase(TextBox1.Value)&"*.pdf" Then

2022/12/13(火) 09:09:12.15

100回やって差なんか0.1秒もないだろ
その0.1秒を縮めるのに何分無駄にしてんだよ
どっちでもいいような事に無駄に時間を費やすのは無能のやることだぞ

2022/12/13(火) 11:03:24.68

>>369
スレ読んで書き込むのに何分使ったの？