Excel VBA 質問スレ Part73

2021/09/13(月) 07:29:59.47

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑同じ内容を2行貼り付けるナリ

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

※前スレ
Excel VBA 質問スレ Part70
https://mevius.5ch.net/test/read.cgi/tech/1616072923/
Excel VBA 質問スレ Part71
https://mevius.5ch.net/test/read.cgi/tech/1621914481/

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

2021/10/15(金) 22:02:32.86

>>398
役割。
モジュールもメソッドも全てに役割分担を持たせてキチンと仕舞うべき場所に仕舞っておく。
だからその役割を行う為なら100行になろうが1000行になろうがそのメソッドに記載する。

何を持ってしてひとつの役割とするかは作る人のセンスに掛かってくるから場数をこなして慣れるしかないかな。

2021/10/15(金) 22:06:51.27

>>398
変数や関数名や引数である程度分かるようにする
それでも迷うようならコメント付ける

2021/10/16(土) 13:55:14.29

>>398
> 分けすぎると逆に関数があっちこっちに分散してどれが何に使われているのか分かり辛くなる
関数にカーソル合わせて「Shift+F2」で定義箇所に飛べるよ
https://www.wordvbalab.com/code/2925/

まあ言語自体も開発環境も貧弱すぎるからまとめたくなっちゃう感覚はわかるけど

2021/10/16(土) 16:39:05.71

調べてもわからないのでお聞きしたいです

下の処理でtest3まで処理は正常に行われるんですが
mainに戻ったときに型の不一致でエラーが出て止まってしまいます。

Dim WS As Worksheet

Sub main()
Call test3(TEMP_SHEET)
End Sub

Function TEMP_SHEET() As Worksheet
Set TEMP_SHEET = ThisWorkbook.Worksheets("temp")
Set WS = TEMP_SHEET
End Function

Function test3() As Variant
With WS
.Select
.Cells(1, 1) = "test"
End With
End Function

2021/10/16(土) 16:51:56.90

test3()なのにcallで引数渡そうとしてる

2021/10/16(土) 17:03:21.91

VariantにはArrayとかも入るから ()付けても書式エラーにはならないのかな

2021/10/16(土) 17:06:32.68

>>402
test3()は引数がないのに引数をわたそうとしている。

test3()は関数なのにy=test3(x)という構文ではなくてcallでtest3()を呼び出しいる。

2021/10/16(土) 17:09:51.40

test3の実行後にエラーになるのがこの質問のミソだと思うよ

2021/10/16(土) 17:43:21.23

>>402
そもそも「test3までは正常」というのが間違い
これではまったく動かない

とりあえず動くようにするなら、たとえばこう書けばいい
ほかにも色々なパターンが考えられるけど、何がやりたいのかよくわからないので

Dim WS As Worksheet

Sub main()
　　Call TEMP_SHEET
　　Call test3
End Sub

Sub TEMP_SHEET()
　　Set WS = ThisWorkbook.Worksheets("temp")
End Sub

Sub test3()
　　With WS
　　　　.Select
　　　　.Cells(1, 1) = "test"
　　End With
End Sub

2021/10/16(土) 18:35:14.73

確かにこれではtest3は動かない

2021/10/16(土) 18:36:14.79

>>402
test3まで処理が正常に行われたなら気にしなくていいんじゃね

2021/10/16(土) 18:46:34.14

ステップ実行させたら確かにtest3実行するな
こんなのコンパイルエラーで良いと思うんだが、なかなか興味深い

2021/10/16(土) 18:57:45.79

ありがとうございます！
test3はsubでよかったんですね。
参考にしながらいろいろ試したらできました。
すごく助かりました。

2021/10/16(土) 19:30:00.02

いや、問題はSubかFunctionかじゃないんだが
FunctionをCallしても、戻り値が捨てられるだけで別に問題はないぞ
名前の付け方がまあアレだが

Dim WS As Worksheet の行消して

Function TEMP_SHEET() As Worksheet
Set TEMP_SHEET = ThisWorkbook.Worksheets("temp")
End Function

Function test3(WS As Worksheet) As Variant
With WS
.Select
.Cells(1, 1) = "test"
End With
End Function

とかで動くだろ

2021/10/16(土) 20:00:16.92

>>412
ありがとうございます。すごく勉強になりました。

2021/10/16(土) 21:23:47.08

最終の答え得るためにいくつの関数に分けるか、というのはどうやったら身につくんでしょうか

2021/10/16(土) 21:32:26.89

分けたくなったら分ければいい

・朝起きる
・トイレ
・歯磨き
・食事
・デンタルフロス
・着替え
・家出る

寝起きのルーチンとしてトイレ歯磨きをセットとするとか
無い時もある食事デンタルフロスをセットとするとか
歯磨きが先なのは寝起きで飲み食いが嫌で個人的な事だから異論はあると思う

2021/10/16(土) 21:49:34.88

>>414
欲しい答えじゃないかもしれないけど

同じような処理を何度か書くような場面ができたときに、一つの関数にまとめるべきか検討するといいよ

むやみに何でも関数にすればいいというものではない

2021/10/17(日) 09:01:15.98

>>415
>>416
ありがとうございます。
手続き型しか書けなくて、ダラダラ長くなってしまうのが少し気になり始めてしまって、見やすいシンプルなコード見ると、どうしたらこういう考え方できるのかなと。
実験的にもいろいろやってみます。

2021/10/17(日) 11:37:36.37

別スレから来ました

A1 会社名 B1仕事の項目 C1整理番号
B1の項目に従って規定のExcelファイルを開いてA1の会社名とC1の整理番号を合体して名前をつけて保存っていう操作をしたいんだけど、これはVBAで出来ますか？
別件ですがVBAでオススメの書籍があれば教えてください

2021/10/17(日) 11:41:00.73

余裕で可能

2021/10/17(日) 11:47:00.68

>>418
大分質問が上手くなったじゃないか。
ただ規定のファイルにはただ名前つけて保存するだけでいいのか？

2021/10/17(日) 12:11:29.02

>>418
どこのスレから来たの？
Rubyスレかそうでないかで対応変わるんだけど

2021/10/17(日) 12:14:56.83

なんでExcelファイル操作するのにRubyスレから来るんだよ

2021/10/17(日) 12:16:45.71

別スレから来ました

A1 会社名 B1仕事の項目 C1整理番号
B1の項目に従って規定のExcelファイルを開いてA1の会社名とC1の整理番号を合体して名前をつけて保存っていう操作をしたいんだけど、これはVBAで出来ますか？
別件ですがVBAでオススメの書籍があれば教えてください

2021/10/17(日) 12:18:39.23

頭おかしくなったのか？

2021/10/17(日) 12:19:45.69

ブラウザバックしたら投稿した
すまない
Excel質問スレから来た

>>420
名前をつけて保存の前にいろいろやろうとは思ってるけど、VBA素人だからとりあえず名前をつけて保存までにしようと

VBAで出来ることはわかったのでダイマでもステマでもいいのでオススメの書籍を教えてください

2021/10/17(日) 12:22:55.44

だってルビースレから来た時点で質問が目的じゃなくて
最終的にルビーコード貼り付けてどやーするのが目的の人だし

2021/10/17(日) 13:08:15.77

ルビースレってそもそもなんなのかって状態なんだが…
この板はブラウザバックで二重投稿した程度で中傷くらったりするのか？
エクセルのVBAについて質問してるのに全然違う答え返ってくるし

2021/10/17(日) 13:55:19.97

ﾆﾔﾆﾔ

2021/10/17(日) 14:58:05.05

>>425
自分で本屋に行って内容を確認しようとは思わないのか？

2021/10/17(日) 15:24:33.87

>>425
B1の項目に従ってというのが謎なので気を利かせて絶対パスで指定のファイルを開くようにしたよ。
ちなみに5ちゃんに書くとファイルパスの￥マーク書けないから半角に直してね。

思ってた動きと違うと言うなら自分の質問200回音読してから書いてね。
動作が不足してるならちゃんと質問のここが出来てないって書いてね。

使い方
・データを打ち込んでるブックで、処理させたいデータの行のどの列でもいいから選択しておく
・マクロ実行
・指定したファイルに選択した行のA列＋C列のデータの名前で名前をつけて保存される

─────────────────────

Sub test()

Gyo = Selection.Row

Data0 = Cells(Gyo, 1).Value
Data1 = Format(Cells(Gyo, 3), "000")

Namae0 = ThisWorkbook.Path & "￥" & Data0 & Data1 & ".xlsx"
Namae1 = Data0 & Data1 & ".xlsx"

Workbooks.Open "C:\Users\*****.xlsx"

Workbooks("*****.xlsx").SaveAs Filename:=Namae0

Workbooks(Namae1).Close

End Sub

2021/10/17(日) 15:26:59.96

あーあルビーキチガイに無駄なエネルギー使ってる

2021/10/17(日) 15:28:04.20

ツンデレで草

2021/10/17(日) 16:11:59.85

>>430
> Workbooks.Open "C:\Users\*****.xlsx"
> Workbooks("*****.xlsx").SaveAs Filename:=Namae0
> Workbooks(Namae1).Close
こんなふうに書く人いるけどなんで
Dim Book As Workbook
Set Book = Workbooks.Open("C:\Users\*****.xlsx")
Book.SaveAs Filename:=Namae0
Book.Close
って書かないのか不思議だ

2021/10/17(日) 19:08:06.14

>>433
単に返り値がopenしたbookであることを知らないだけだと思う
俺も昔は前者みたいな書き方してたな

2021/10/17(日) 19:23:15.59

なるほど、結構基本的なことだと思ってたけどそうでもないんかな
シートでも Worksheets("sheet1").~ って書いてる人をよく見る

2021/10/17(日) 19:27:41.57

>>433
わたすもまだ今年から始めたド素人なもので…。参考になります。
>434の言うとおり戻り値がそんなだなんて知らなかったんだよう。

2021/10/17(日) 19:29:03.49

後続の処理でworkbookオブジェクトを使わないならWithで纏めちゃうな

With Workbooks.Open("C:\Users\*****.xlsx")
.SaveAs Filename:=Namae0
.Close
End With

こんな感じで

2021/10/17(日) 19:31:49.54

>>437
一行ごとに処理書いてる感なくてスマートでええな。

2021/10/17(日) 21:41:34.16

Withは便利だけど積極的には使わない派
後で見返す時にわけわからなくなるから

2021/10/17(日) 21:43:33.55

ネストしたwithを見たらエディタ閉じるわ

2021/10/17(日) 21:48:12.01

俺は>>437と同じ
オブジェクトを変数に入れずにWithに直接ぶっこむ派
DimとSetの行がなくなるから入力の手間も減るし狭い画面でソースが見やすくなる

2021/10/17(日) 21:51:58.87

扱うオブジェクトが１つならいいけど複数あったりサブルーチンに渡したりする時もあるから

2021/10/17(日) 22:22:04.64

withはネストするとトタンに見にくくなるよね
>>437ぐらいなら見やすいと思うけど、>>439と完全に同意見だわ

2021/10/18(月) 00:37:42.11

以前VB6スレでもWith Newを使ってる人がいて論議になったことがあったね。

俺はこの方法は有りだと思う。
ひとつしか使えないけど関数やメソッドの中で唯一ひと階層下のスコープを設定出来る方法だからそれを意識した使い方なら。

ただこの方法であまり長いロジックを組むのは確かに可読性に宜しくないのも同意。
だから使いどころに気をつけながら使う方法だと思うよ。

2021/10/18(月) 00:52:44.24

オブジェクトを抱えるWithはSubやFunctionとセットで使うんだよ
1つのSubには一つのWith
それ以上になる時は機能を詰め込みすぎだから分割すべき

2021/10/18(月) 01:37:56.48

https://ro69-bucket.s3.amazonaws.com/uploads/artist_image/image/2282/width:600/resize_image.jpg

2021/10/18(月) 07:58:42.77

複数のファイルじゃなくて複数のフォルダを選択する方法はありますか?

2021/10/18(月) 08:17:54.78

>>447
vba 複数フォルダ選択
でググればいくらでも出てくる
https://stabucky.com/wp/archives/1507

2021/10/19(火) 05:36:51.83

クリックする

2021/10/19(火) 13:51:25.65

パワポもvbaで操作している人はいますか？
また手っ取り早く時短できる部分はありますか?

2021/10/19(火) 14:28:41.78

>>450
Excel のデータを50枚ぐらいにスライドに整形して表示するって言うのをVBAでやったよ

2021/10/19(火) 16:06:34.91

>>448
リンクにあるのはフォルダじゃなくてファイルについてですね。。。
どうやらファイルは複数選択できるけどフォルダはできないっぽいですね。。。

2021/10/19(火) 16:21:20.09

>>452
横槍だけどリンク先ちゃんと下まで読んだ？

2021/10/19(火) 16:32:01.94

全くの初心者です

元エクセルファイルを、そのファイル内の特定のシートの特定のセルないの文字(名前)でフォルダを作成し(作成階層は元エクセルファイルがある場所)、そのフォルダ内にフォルダと同じ名前で元エクセルファイルを出力したいです

検索してみてそれっぽいコードを実行してみましたが上手く行かないか1004のエラーが出てしまうので、できれば教えていただきたいです

2021/10/19(火) 17:37:09.15

>>454
まずは自分で作ったプログラムをここに貼って

2021/10/19(火) 17:40:33.12

>>454
流石にエスパーじゃないからそれだけの情報じゃなんとも言えんが、
ファイルの指定が間違ってる可能性がありそう
エラーが出たときにデバッグってボタン押したらどんな命令文の背景が変わってる？
省略しないでその文を一字一句違わず書いてみ？

2021/10/19(火) 18:02:38.90

実行してるファイルと同じ名前はダメだろ

2021/10/19(火) 20:26:31.01

>>457
元のファイルがあるフォルダーのサブフォルダーに格納されるんで同じ名前でも大丈夫だよ。

2021/10/20(水) 00:29:04.94

質問者の勘違いとエスパーしたんじゃね

**デフォルトの名無しさん** (ｽﾌｯ Sdbf-ZO17) · 2021/10/20(水) 00:47:36.84

わたしは、エスパー
今、質問者の脳内に、直接、回答を送っています
わたしは、エスパー

**デフォルトの名無しさん** (ｽﾌｯ Sdbf-ZO17) · 2021/10/20(水) 00:49:43.99

残念です
夜中なので、質問者は、寝てました
残念です

2021/10/20(水) 00:53:24.04

なぜプログラム板でExcelVBAのスレが活発なのよ？
日本終わってんな

2021/10/20(水) 03:43:52.55

労働者の多くがExcel使ってんだから需要からしたら普通だろ

2021/10/20(水) 06:13:11.55

まぁ年収ランキングとしてもそこそこ高い位置にいるし当然と言えば当然だな
https://xtech.nikkei.com/atcl/nxt/column/18/01068/111100004/

2021/10/20(水) 10:35:33.40

>>462
なんでRubyスレにいかないの？

2021/10/20(水) 10:37:34.01

プログラマーじゃない人もたくさん使ってるから
人が増えるのは必然

2021/10/20(水) 17:58:56.42

>>456
https://lilia-study.com/excel/vba-sample/cellvalue-save/

Sub hozon()
Dim wb As Workbook
Dim ws As Worksheet
Dim hozonPath As String
Dim FolName As String
Dim FilName As String

Set wb = ThisWorkbook
Set ws = ActiveSheet

hozonPath = “K:\”
FolName = ws.Range(“A1”).Value
FilName = ws.Range(“A2”).Value

wb.SaveAs Filename:=hozonPath & FolName & “\” & FilName
End Sub

このvbaで
A1=A2にして、hozonPathをThisWorkbook.Pathにしたりすればできるかな?と思ったのですが上手く行かずです…

2021/10/20(水) 18:30:57.52

ThisWorkbook.Pathにはケツの￥が付いてないから
ThisWorkbook.Path & ”¥” & FolName & “\” & FilName
に書き換えてみ

2021/10/20(水) 18:49:10.89

\
"
半角にしてね

2021/10/20(水) 20:45:15.20

>>467
お前のＰＣにK:ドライブは存在するのか？
とりあえず
wb.SaveAs のまえに、
MsgBox hozonPath & FolName & “\” & FilName
いれて、表示された内容をかけ

個人名とか入ってたら適当に隠せよ

2021/10/20(水) 22:22:06.11

超初心者です　

以下の対応をさせたいです
①シートを支店分だけ増やす
②シート名をそれぞれ「支店一覧」A2～A18の支店名にする
③作成したシートのB5に支店名を入れる
④作成したシートの表のA列が"0"になっている行を削除する
⑤作成したシートのオートフィルタを解除する

以下のものではエラーになりますが、どこが悪いのでしょうか

Sub シート作成
Dim 支店名 As Range
For Each 名前 In Worksheets("支店一覧").Range("A2：A18")
Worksheets("原本").Copy After:=Worksheets(Worksheets.Count)
With ActiveSheet
.Name = 支店名.Value
.Range("B5") = 支店名.Value
End With
　 Range("B3").AutoFilter 1, "0"
With Range("B3").CurrentRegion.Offset(1, 0)
.Resize(.Rows.Count - 1).EntireRow.Delete
　　Range("B3").AutoFilter
End With
Next 支店名
End Sub

**471** (ﾜｯﾁｮｲ efda-KUiw) · 2021/10/20(水) 22:24:48.55

④は「B列が"0"になっている行を削除する」の誤りでした

2021/10/20(水) 22:37:03.53

とりあえずここだと思う
下の方はサンプル作ってくれれば試すけど、手元にないからよくわｋらん

Sub シート作成()
For Each 支店名 In Worksheets("支店一覧").Range("A2:A18")

**471** (ﾜｯﾁｮｲ efda-KUiw) · 2021/10/20(水) 22:41:41.27

>>473
大変失礼いたしました
あるページからコードをコピーしてきたときに修正し忘れました
実際は名前→支店名にしていましたが、エラーとなっていました
エラーの内容の詳細が必要でしたら、明日投稿します
（現在手元にＥｘｃｅｌファイルがなく、すみません）

2021/10/20(水) 23:13:32.77

安心して
手元にエクセルファイルが無いのに記憶だけでそこまで書ければ超初心者じゃないよ

2021/10/21(木) 05:25:42.24

>>474
> エラーの内容の詳細が必要でしたら、明日投稿します
なぜ最初から書かないの？

2021/10/21(木) 06:18:01.89

>>471
あと、
・どこで
・どんな
エラーになるのか書いて

2021/10/21(木) 06:54:51.77

Select Caseにbreakがないのは何故なんでしょうか
beakがないから最初に一致した条件以降のCaseも
実行されると勘違いしやすい
仕事で大きなトラブルを起こしてしまった…

2021/10/21(木) 07:06:17.27

Select Caseって上から順に見て真っ先に当てはまったCaseのみ実行じゃなかった？違うんだっけ？

2021/10/21(木) 07:46:09.23

>>478
> Select Caseにbreakがないのは何故なんでしょうか
そういう仕様だから
Pascal とかと同じ

> beakがないから最初に一致した条件以降のCaseも実行されると勘違いしやすい
むしろCとかでbreak忘れてトラブる奴の方が多いだろ
なのでC# はコンパイルエラーにするぐらいだし

> 仕事で大きなトラブルを起こしてしまった…
仕事で使うならちゃんとテストしろよ…

2021/10/21(木) 11:01:11.45

>>479
違うよ
Select Case の後にTrueとか書いて
Case の後にTrueとかFalseとか書くと
簡単に確認出来るよ

2021/10/21(木) 11:14:41.56

>>478
これはVBAとかCase文に限らず今自分がやってる言語→ 他言語に切り替える際には何か違う挙動するなー、て言うのはあるあるなことだからまぁいい勉強になったって思うべきだね。

>>480の人が言う通り仕事で使用するならキチンとテストするべきだったね。
UTとして証跡残すのがセオリーだけど、せめて一度走らせて挙動確認しておけば今回の件は防げたはず。

その言語仕様を嘆くよりそういったこともあるって次回に生かした方が幸せになれると思うよ。

2021/10/21(木) 12:11:34.23

select case 普通に動作するんだが、どうやったらトラブル起こせるんだ？

2021/10/21(木) 17:55:34.88

>>470

https://d.kuku.lu/8dd3704ad

すみませんimgurが使えないのでよくわからんあぷろだですが…

やはりエラーが出ます、

2021/10/21(木) 18:43:47.31

だからhozonPathとFoINameの間に¥を入れろって

2021/10/21(木) 21:55:14.75

>>484
お前みたいな馬鹿にはVBAは無理だと思うよ

2021/10/21(木) 22:14:08.48

>>484
最強料理はケバブ

2021/10/21(木) 22:16:16.48

コピペ間違えた

>>484
wb.SaveAs Filename:=hozonPath & "\" & FolName & “\” & FilName
で

あと
FolderName
を無理に
FolName
と変に略すと、あとで何書いてるかわからなくなるぞ。

2021/10/21(木) 23:15:26.60

パス区切りに、/ は使えないの？

例えば、Ruby なら、

glob_pattern = "C:/Users/Owner/Documents/*.txt"

p Dir.glob( glob_pattern )

出力
[ "C:/Users/Owner/Documents/a.txt",
"C:/Users/Owner/Documents/b.txt" ]

2021/10/22(金) 00:34:42.64

やってみればわかるんじゃないの？

2021/10/22(金) 00:44:00.08

あまりに馬鹿過ぎると思ったらやっぱりruby馬鹿か

2021/10/22(金) 00:46:54.73

>>484
何をやりたいのかよくわからんけど、連続して2回以上実行すると、実行回数分のネストしたフォルダが必要になるのでエラー吐く。
あと、コピー先のフォルダ／ファイル名はActiveSheetを参照しているので、複数ブック開いていたりするとおかしなことになる。

2021/10/22(金) 01:17:15.93

>>484
保存先のフォルダがないとそのエラーになるから、wb.SaveAsの直前にその確認を入れる
↓で動いたの確認した

'※hozonPathはあるのか
Dim ExistHozonPath As Boolean
ExistHozonPath = Len(Dir(hozonPath, vbDirectory))

If Not ExistHozonPath Then MsgBox hozonPath & "ドライブは存在しません。"

'※FolNameはあるのか
Dim FolFullName As String, ExistFol As Boolean
FolFullName = hozonPath & FolName
ExistFol = Len(Dir(FolFullName, vbDirectory))

If Not ExistFol Then
Dim Question As String
Question = "「" & FolFullName & "」フォルダが存在しません。作成しますか？"
Select Case MsgBox(Question, vbYesNo)
Case vbYes 'はい→作る
MkDir FolFullName
Case vbNo 'いいえ→終了
Exit Sub
End Select
End If

wb.SaveAs Filename:=hozonPath & FolName & "\" & FilName

2021/10/22(金) 01:25:21.34

>>493
ドライブない時に終了させるの忘れてた

2021/10/22(金) 07:57:46.72

ユーザーフォーム自体(コードではなくてボタンとかが表示されている方)を印刷する時、
デフォルトで用紙が縦になるんですが、横に出力する方法はないでしょうか？

2021/10/22(金) 09:34:10.25

>>495
Windowsの設定（コンパネ）のプリンタで用紙設定しておく

2021/10/22(金) 12:17:25.64

あー、あの縦か横かをオプションボタンで選ぶ画面ですね。
VBA以前の問題でした。失礼しました。
帰宅したら試します。

2021/10/22(金) 19:20:10.02

横１列に並んだセルを
A B C D E F
1 あああいいいううう阿嗚呼伊井伊雨右鵜
2 かかかきききくくく可化歌機器機久々九
3
↓
A B C
1 あああいいいううう
2 阿嗚呼伊井伊雨右鵜
3 かかかきききくくく
4 可化歌機器機久々九
のように１行飛びに成形するにはどうしたら良いですか？