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/09(土) 20:26:49.65

セル結合とvlookupをディスってようやくエクセル使いとして一人前

2021/10/09(土) 20:30:33.16

べつにvlookupは悪くないだろ俺は使いたくないけど

2021/10/09(土) 20:58:23.98

>俺は使いたくないけど
さりげないアピール

2021/10/09(土) 20:59:19.46

>俺は使いたくないけど
さりげなさを装ったアピール

2021/10/09(土) 21:05:39.47

VLOOKUPディスってるのは情弱だろ
2016からめっちゃ高速化されてるから数万行ぐらいなら普通に使える

2021/10/09(土) 21:11:18.66

高速化とかどうでも良い
左端しかデータが使えないのが不便
無駄な検索用データが全部左端に固まる

2021/10/10(日) 06:43:03.77

個人的に使いたい使いたくないは仕方がない
適材適所で最適解を出す手段に自ら縛りを掛けているだけだから
それで他の人に迷惑掛けている訳でなければとやかく言う筋合いでも無いしな

2021/10/10(日) 08:15:02.57

>>362
XLOOKUP使え
うちの環境だとまだ使えないけど... orz

2021/10/10(日) 10:34:27.64

index matchでええやん

2021/10/10(日) 15:09:30.75

MATCHはいちいちIFERROR組み込まなきゃいけないから式が冗長になりがちであんまり好きくない

**デフォルトの名無しさん** (ｽﾌｯ Sdea-JSxF) · 2021/10/10(日) 15:39:33.06

そもそもスピードは求めてない
元データを1日に何回もアップデートするわけじゃないし

2021/10/10(日) 15:46:10.06

>>366は何使ってんの？

2021/10/10(日) 16:04:08.10

>>368
あんまり関数でデータベース検索かけたくないけどかけなきゃいけないならXLOOKUP、自分以外が使う可能性があるならVLOOKUPかHLOOKUPかなぁ
状況に合わないならMATCHも使うけど使用優先度は低め

VBAにやらせる時も中に確実に存在する場合でなければRange型にFind使って放り込む事が多いかも
Is Nothingで見つからない場合でも処理が止まらずに弾けるし

2021/10/10(日) 16:23:38.63

あごめん>>364かと思ってた

処理が止まらずにってのすごい大事だよなぁ

2021/10/10(日) 16:30:18.62

>>370
364だけどVLOOKUP使えるならVLOOKUP
そうでなきゃINDEX+MATCH
大抵そうじゃないの？

2021/10/10(日) 16:51:12.87

ある行を内容の変更は自由にできるけど削除できないようにすることはできますか？

2021/10/10(日) 17:26:53.27

それ、ここでする質問？

2021/10/10(日) 17:53:50.05

>>372
削除を感知して、
https://kirinote.com/excelvba-row-event/

削除されていれば
appliction.undo
ってのはどうだろうか

2021/10/10(日) 18:04:32.90

ゴミみたいな回答ばかり

2021/10/10(日) 19:05:42.11

じゃあ、ゴミじゃない回答よろ

2021/10/10(日) 19:35:34.55

「削除できないように」というのが：
・「セルや行の削除をできないようにしたい」→シートの保護で対処
・「値の変更はできるが消去（空欄に）できないようにしたい」→入力規則で対処

2021/10/10(日) 20:12:04.54

対処の方法がわからないのかと思ったら、対処する方法があるのかどうかの質問だったのかｗ

**372** (ﾜｯﾁｮｲ 67da-JSxF) · 2021/10/11(月) 00:26:21.39

>>377
シートの保護で削除不可を選んでも全ての行が削除不可になること，またセルの内容変更もできなくなるので希望に合いません．
374さんのやり方をVBA画面をで書くしか無いですかね

2021/10/11(月) 14:09:59.80

>>379
保護する前に行選択しておけばいいやん
任意の場所だけ編集不可にできるよ

2021/10/11(月) 14:59:32.76

>>380

> 任意の場所だけ編集不可にできるよ
編集不可ではなくって、編集可で削除不可にしたいんです。

2021/10/11(月) 18:57:29.54

空白に編集する場合とかどう取り扱うつもりなんだろう

2021/10/11(月) 20:49:58.25

クビか配置転換したほうがよいかと

2021/10/11(月) 21:18:23.92

馬鹿でもある程度作れてしまうからダメなんだよな

2021/10/12(火) 18:51:44.09

それを求める環境が悪い

2021/10/14(木) 18:44:35.26

空白は削除じゃないんじゃね

2021/10/14(木) 19:05:30.78

メーカー開発設計だけど実験データ処理とかで
なれないながら頑張ってvba使ってるけど
試行錯誤で調べながらだからあんまりシート使ってるときと比べて効率化できてる気がしない…
でもシートでチマチマするよりは知的労働してる気がするし
いつかのペイオフを期待するわ

2021/10/14(木) 20:42:17.77

あるレベルを超えると突然やりたいことはこうやりゃいいじゃんってなってバリバリ書けるようになる

2021/10/14(木) 22:26:04.22

set覚えてオブジェクトの概念覚えてイベントを理解してしばらくするとほぼ全ての場面で対応できるようになる

2021/10/15(金) 06:17:54.01

色々覚えて余計な知識が付いてきて、
これはもっと綺麗に書けるんじゃないか？っていう欲が出て進まなくなる事が増えたんだけどどうすりゃいいのか
今までは明らかに冗長で読みづらいコードが多かったけど、生産性で見れば高かったように思う

特にプログラミング自体に興味が出てきて他の言語の記事も見るようになって、
そこで得た知識をVBAで無理に再現しようとしてドツボに嵌る事態も多いように感じる

2021/10/15(金) 07:30:19.61

>>390
まず単一責任原則に沿って作るといいよ。
つらつらとコードを一直線に書くんじゃなくて、
役割に合わせてメソッドや関数を作って
それを部品として組み立てて行くイメージ。

後、他言語をやっていてクラスや
インターフェースの概念を理解しているなら
行き当たりばったりで最初からコードを
書くんじゃなくてどういう構成で組むかを
一度図に書いてイメージしてから作るといいよ。
そのためにもUMLとかもかじっておくと
いいんじゃないかな。

後は暇なときにデザインパターンの本でも
読んでおいて使えそうなのだけ
使えばいいんじゃないかな。

ただ、VBAでは自分一人で使うんであれば
それでいいけど、後々人が見たり手を入れたり
することを考えると、やり過ぎると
そもそもその辺の概念を理解していない
人が多いからね。自分以外誰も触れない
オナニーコードの完成だよ。
その辺は気を付けないとね。

2021/10/15(金) 07:53:52.36

>>390
字の練習と同じ
納得できるまで、何度も綺麗に書き直せば良い
その内、早く綺麗に掛けるようになる
「もう少し綺麗に書けそうだけど時間かかるから諦めよう」の判断も出来るようになる

>>391とは逆だけど、vbaの場合は関数やモジュールはあまり分けなくても大丈夫だよ
他の言語だと10行超えたら別モジュール、なんて言われる事もあるけど
vbaだと単一モジュール・単一関数で終わる方が見やすい事も

2021/10/15(金) 08:56:32.98

メソッドが3つ以上にできそうなときはクラスを検討する

2021/10/15(金) 09:22:10.96

美しさより速さだよ
最初の頃は両立するが(無駄なSelectなど)
次第によ醜くなっていく

2021/10/15(金) 10:10:42.08

もう3年くらいExcel使ってないけど全然困らないωωω

2021/10/15(金) 11:34:26.87

ふーん

2021/10/15(金) 20:36:17.72

>>395
なんでこのスレに居るの？

2021/10/15(金) 21:42:47.27

>>391,392
ありがとう

分けた方がスッキリするんだけど、
分けすぎると逆に関数があっちこっちに分散してどれが何に使われているのか分かり辛くなる
そういう時に「どこまで分けるか」っていう塩梅はどういう基準で決めてる？

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

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