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/05(火) 15:45:39.37

補足ながら>>311はMPもマルチページとして宣言するか該当箇所をMP.Pages("D3").Name = "X3"に書き換えれば期待通り動くんですよね
どういう仕組みなのか

2021/10/05(火) 15:56:25.07

worksheetfunctionの引数に配列入れるときって数に上限があるな
警告も出さないしほんまクソ

2021/10/05(火) 15:56:31.46

worksheetfunctionの引数に配列入れるときって数に上限があるな
警告も出さないしほんまクソ

2021/10/05(火) 16:15:51.73

>>312
いや
普通に通るけど。ちな2016。

2021/10/05(火) 16:19:44.73

あ、でもこっちはOption Explicit 付けてたから
MPはキチンと型宣言してたな。
でもそこはあまり関係ないよね？

2021/10/05(火) 16:21:29.24

>>316
や、たぶん関係するっぽいですかね？
こちらオプション無し、宣言は無しまたはas objectだとダメでas multipageだと通った

2021/10/05(火) 16:29:34.84

>>317
ふーん
不思議だねぇ
ちなみに宣言しないで走らせたときの
MPの内部型ってどうなってる？
ごめん、もう走ったからこっちは
EXCELのブック消しちゃったんだ。

2021/10/05(火) 16:32:02.05

variant/object/multipageでした

2021/10/05(火) 16:36:45.31

>>319
やっぱり不思議だねぇ
その辺の内情は分からないな
一応片付いたことになるのかも知れないけど
誰かその辺知ってたらレスが付くんじゃないかな

2021/10/05(火) 16:39:04.47

そうですね、とりあえず急ぎとか困りではないので一旦これで…
ご回答ありがとうございました

2021/10/05(火) 18:26:27.16

型推論するのに失敗してるんじゃね

2021/10/05(火) 21:37:47.06

Pagesの引数に変数渡すと数字として解釈しようとしてるっぽいな
buf = "2" とかにするとIndex=2のページを参照してるようだ
文字列リテラルならちゃんと名称として解釈してるっぽい
ちな365の64ビット

これ以上はサポートにでも問い合わせるしかねぇんじゃね

2021/10/07(木) 18:28:58.89

マクロの記録機能使ってマクロの作成したいんだがピボットテーブルのプルダウンから任意の値だけを抜き出すのってどうやってやればいいの？

毎回モジュール見直すとvisible farceになってて要するに抜き出したいもの以外を表示しないようにしてる構文て事だよね？
こうなると仮に次回このマクロを使う時にvisible farceになってない値が新しく出てくるとそれも表示されちゃうって事だよね？
じゃなくていくら値が増えても決められた値だけを抜き出すように作りたいんだ
どなたか教えて下さい

2021/10/07(木) 20:11:24.24

>>324
記録してみてどうなってるか見てみれば分かるじゃん

2021/10/07(木) 21:05:39.19

>>324
Falseね
ピボット周りのフィルタはFor EachでTrueFalseを一つ一つやっていかないと無理な仕様
遅いしわかりにくいけど割り切って

2021/10/07(木) 21:29:48.70

ActiveSheet.PivotTables("ぴぼっとてーぶる").PivotFields("ふぃーるど").ClearAllFilters
ActiveSheet.PivotTables("ぴぼっとてーぶる").PivotFields("ふぃーるど").PivotFilters.Add Type:=xlCaptionEquals, Value1:="表示したいもの"

2021/10/07(木) 23:55:42.12

VBAで最適化したら仕事増やされた話ってよく聞くけど
そいつが辞めたらそいつに回されてた膨大な仕事はどうなるん

2021/10/08(金) 00:17:45.54

うちの職場は適当にvbaでつくったやつをちゃんとしたアプリに置き換えてるよ

2021/10/08(金) 00:31:33.80

アプリだろうがvbaだろうが作った人間がやめたらメンテできなくて終わるんじゃないの
開発者が大勢いるようなレアな職場ならともかく
vba使ってるところなんてたいてい事務で
エクセル職人が少数いるだけだろ

2021/10/08(金) 00:37:30.31

実際にはそういうときには業務そのものが消滅するケースも多いよ
そもそもVBAで簡単に自動化できるような業務はもともと無意味であるものが多いからな

2021/10/08(金) 02:32:08.41

人がやめた後に職場がどうなるかはその職場の責任者が決めるんじゃね
下っ端が気にすることじゃない

**デフォルトの名無しさん** (ｽﾌｯ Sdea-JSxF) · 2021/10/08(金) 04:11:36.40

市販アプリでも、天才的な開発者が辞めて、会社もろともアプリもダメになって終わったパターンをいくつも見てきた

2021/10/08(金) 07:11:46.26

>>331
>>333
なるほどねありがとう

2021/10/08(金) 08:09:49.53

>>326
>>327
丁寧にありがとう
色々試してみます

2021/10/08(金) 09:40:27.58

CPU使用率が大きかったのでダメもとで開いて修復を実行したら
かなり軽くなるのな、知らんかった
これ定期的にやったほうがいいのか？
VBAのコードを一旦退避させるのが面倒だが．．．

2021/10/08(金) 17:00:42.44

>>336
詳しく

2021/10/08(金) 22:11:41.59

属人的にならない仕組みで効率化できればいいんだが、頭の中が古き良きな方々が幅を利かせる日本ではなかなか難しい

2021/10/09(土) 01:49:50.17

自分が初めてVBAを使って解決した時点で、他にVBAを使う人がいないんだから属人化しないわけがないと思うけどな
しかもVBAってプログラミング言語の中では非常に初心者向けなわけで、
誰もVBAで組めない＝プログラミングに全員疎いのはほぼ決定なんだから尚更さ

VBAを共通知識にしたいっていうのは、
最初にプログラミングっていう一般的には取っ付き難い物に興味を持たせるっていう途轍もなく大きなハードルを超える必要があって、
それは「興味がある生徒に教える」プログラミング講師以上に難しいと思う

相当な根気が必要だと思うよ

2021/10/09(土) 02:16:01.12

そもそも人に仕事が割り振られた段階で属人化されてるわけで
たんに次の人が困って終わりだよ

2021/10/09(土) 02:30:43.66

確かに。
レス漁ってみたが、
ここでいう「属人化で困る」は「他の人にはできないVBAというテクニックを持つオレスゲー」を単にオブラートに包んだだけっぽい感じがするな

2021/10/09(土) 02:49:32.47

マクロの効率化ガン無視で社員の手が開いた分だけ仕事を割り振る上司がいるらしいけど
その時点である意味属人化してるよね
その仕事引き継ぐやつ
まずさばき切れないだろ

2021/10/09(土) 04:49:21.33

うちの職場ではほとんどの人がマクロできるけど、新人に自信持たせるためにわざわざ気を使って
「君マクロできるなんて凄いね～」っておだててるのに同調するのが正直めんどくさい

2021/10/09(土) 04:50:24.64

そういうとこは給料高そうで羨ましい

2021/10/09(土) 05:01:41.26

>>344
給料は個人の能力に比例します

2021/10/09(土) 08:10:48.77

新人の育成は面倒だけど将来的に必要なことだからなあ
褒めた方が上達が早いってのも科学的に証明されつつあるし、そういうもんだからあきらめろ

2021/10/09(土) 08:19:40.75

全員VBAできるとか絶対給料高いとこだろ
うちのところなんて一人しかいないよ
しかも残りは関数すらまともにあつない

2021/10/09(土) 08:21:07.29

おかげで効率がすげー悪い
基本は専用ソフト扱うからエクセルは必須ではないとはいえ
それでもエクセルやVBAやれるのとやれないのとでは効率が雲泥の差

2021/10/09(土) 08:31:06.39

選民思想ここに極まれり

2021/10/09(土) 08:33:14.89

研修とか外部の自宅学習プログラムでもやったら？
費用は会社持ち、報奨金も少しは付けて

2021/10/09(土) 08:37:41.66

そんな金あったら給料上げてレベルの高い人材雇うか
もうちょっとマシな専用ソフト使ってる
金をケチったせいでオンボロ専用ソフト使わせれて
専用ソフトじゃフォローしきれないのでエクセル活用しないと行けない羽目になってる

**デフォルトの名無しさん** (ｽﾌｯ Sdea-JSxF) · 2021/10/09(土) 08:57:09.18

会社は社員に楽させたいと思ってるわけじゃない、という大前提
人材なんて時間いっぱい、めいっぱい働かせてナンボ
安いアプリを買って、あとは社員にやらせて、それで回ってるなら何も問題ないんだよ

2021/10/09(土) 09:27:27.16

高い金出して高価な専用ソフト使うくらいなら
多少残業代だしてもいいからボロソフト使ったほうが安上がりだからな

2021/10/09(土) 09:42:15.79

エクセルはマス目状に文章を入力できるソフトぐらいにしか思ってない人もいるしなあ

2021/10/09(土) 15:35:55.10

>>354
ウチの会社の事務方ほとんどそれだわ
セルの結合しまくって数値手入力とか当たり前

2021/10/09(土) 20:10:49.92

セル結合せずにせめて広げろや、とは思う

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でよかったんですね。
参考にしながらいろいろ試したらできました。
すごく助かりました。