Excel VBA 質問スレ Part53

**デフォルトの名無しさん** · 2018/04/27(金) 19:02:38.39

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

※前スレ
Excel VBA 質問スレ Part52
http://mevius.5ch.net/test/read.cgi/tech/1517052305/

**デフォルトの名無しさん** · 2018/05/09(水) 20:43:17.83

>>771
Variant配列には何が入ってるかわからんので最適化しにくい

**デフォルトの名無しさん** · 2018/05/09(水) 20:59:13.98

このように答えがついても反応しない質問者がよくいるからQA以外の話題で盛り上がる。
ま、興味深い話題ならいいんだけどね。

**デフォルトの名無しさん** · 2018/05/09(水) 21:26:33.65

>>777
ループの中じゃなくね？

**デフォルトの名無しさん** · 2018/05/09(水) 21:43:05.88

>>776
ActiveWorkbook.ProtectSharing の後に
ActiveWorkbook.ExclusiveAccess を入れてみれば？

知らんけど

**デフォルトの名無しさん** · 2018/05/09(水) 21:52:15.61

>>778
スコープの件、了解です。
いろいろ試してみます。

**デフォルトの名無しさん** · 2018/05/09(水) 21:53:17.93

>>779
メモリに乗せれば単純に早くなるわけではないんですね。

**デフォルトの名無しさん** · 2018/05/09(水) 21:55:25.27

>>786
配列のマッチングはどうやって書いたの？

**デフォルトの名無しさん** · 2018/05/09(水) 21:57:44.24

>>780
Match使うときはたいてい絶対一致でつかってました。
クロスマッチになっちゃうんでしたっけ？
ここのカキコみて、以前、高速Lookupは使ったことがあるのですが、、
半分うろ覚えです。
リストを昇順ソートして、第３引数を 1 にするだけじゃ
だめですよね。変な答えが返ってきます。

**デフォルトの名無しさん** · 2018/05/09(水) 21:58:30.50

>>781
そうですか。型かえてみようかな？

**デフォルトの名無しさん** · 2018/05/09(水) 22:14:27.61

>>787
いろいろ省略してますが、こんな感じです。

Dim rngFdList as Range: Set rngFdList= Range( ** 以下略(調査表) **
Dim arrFdList as Variant: arrFdList = rngFdList

For each r In Range( ** 以下略(駆動表) **
　With Application
　iHit = .IfError( _
　　　　.Match(r.value, arrFdList, 0), _
　　　　0 _
　　　)
　End With

　(処理...)
Next

**デフォルトの名無しさん** · 2018/05/09(水) 22:19:47.47

>>790
なんだ、ワークシート関数を使ってるのか。
それじゃ配列処理にする意味ないな

**デフォルトの名無しさん** · 2018/05/09(水) 22:23:25.94

>>779
ああ、ワークシート上の関数と比較して、じゃなく、VBA上でMatch関数を使う際に
テーブルをRange指定するか、配列指定するかで速度が変わった、という話です。
これもマルチスレッドが関係します？

たぶんMatchの使い方がまずいんだと思いますが、、

**デフォルトの名無しさん** · 2018/05/09(水) 22:25:05.08

>>791
Matchのほうが早いかなと思って。
Forで回したほうが早いですかね？
それとももっといい方法が、、、
教えてください。

**デフォルトの名無しさん** · 2018/05/09(水) 22:28:35.61

>>783
本当だ
セミコロンで複数の命令繋げて
ご丁寧にインデントまで変えてあるから分からなかった

**デフォルトの名無しさん** · 2018/05/09(水) 22:49:17.51

>>793
>>772と>>775は理解できたの？
これに反応しないからこちらからは状況がよくわからん。

**デフォルトの名無しさん** · 2018/05/10(木) 01:15:35.99

エクセル2016や2013等の新しいエクセルで追加された関数を、
2010等の下位バージョンで動作させるような、
ユーザー定義関数が書かれたVBAモジュールを配布しているサイトってありますか？
検索の仕方が悪いのか、自分では見つけられませんでした。

**デフォルトの名無しさん** · 2018/05/10(木) 06:25:28.16

>>793
セルにアクセスしてループしてるじゃん。そりゃ遅いわ。
メモリ上でループすれば速くなるよ。

処理がこれだけならセル上の関数でも工夫すりゃ同じ速度出そう。
完全一致使わない方法とかで。

**デフォルトの名無しさん** · 2018/05/10(木) 06:42:21.45

>>796
ピンポイントで〇〇の関数を2010で使いたいと言うならまだしも2013/2016の関数って一言で言われてもなぁ

**デフォルトの名無しさん** · 2018/05/10(木) 06:45:52.61

>>790
何回も検索するんならDictionary使えばいい
最初にDictionaryに登録するのにある程度時間がかかるだろうが
一回登録してしまえば検索自体は数秒もかからん

**デフォルトの名無しさん** · 2018/05/10(木) 08:15:00.88

>>796
(関数名) 再現 2010
とか検索したら出てくるんじゃないかな。
IFSとか見つけたけど。

**デフォルトの名無しさん** · 2018/05/10(木) 10:09:42.27

答えがついてもろくに反応しない質問者がいるからQA以外の話題で盛り上がる

ってのは本当だな

**デフォルトの名無しさん** · 2018/05/10(木) 10:46:30.23

>>776です。

>>784
ありがとうございます。
試したけどエラーになります、、、。
いろいろ調べた結果、共有設定の条件によって保存できない。
諦めます（泣）

ありがとうございました。

**デフォルトの名無しさん** · 2018/05/10(木) 13:58:29.65

>>795
二分探索は理解不十分。Matchの第３引数を0にするとバイナリサーチにはならない
んですよね。わかってないからこのザマです。ちょっと勉強してみます。

**デフォルトの名無しさん** · 2018/05/10(木) 13:58:57.48

>>797
>>797
駆動表側のこと？
今はテストなのでセル範囲だけど、実際はもっと時間のかかる別からの供給。
そしてその先にSQL処理があるので、余計なクエリを投げなくていいように篩いにかけて
おこうという意図です。
なので調査対象の表（こちらはセル範囲へ記録した履歴情報）だけでも配列に入れて
高速化しようと試みたんだけど、このザマです。

**デフォルトの名無しさん** · 2018/05/10(木) 14:05:11.32

>>797
Dicかぁ、今、ちょっと時間がとれないので、また試してみます。
早くなるかな？普通の入れ子のLoopとか、Dictionaryとか、いろいろ試してみます。
目標10sec。

**デフォルトの名無しさん** · 2018/05/10(木) 14:06:13.75

>>805
あれ、安価ミス。799への回答でした。

**デフォルトの名無しさん** · 2018/05/10(木) 15:30:36.46

SQLiteでいいじゃん

**デフォルトの名無しさん** · 2018/05/10(木) 16:51:36.95

>>807
それ言えば、またこちらの環境ではセキュリティがどうのこうので入れられない～とか言い出す奴が出てくる
んで、もうExcel限定で良いっしょ。パワーシェルキチは論外としてw

**デフォルトの名無しさん** · 2018/05/10(木) 17:47:50.99

>>805
このへん見るに、かなり高速化できそう
まあループよりハッシュのほうがそりゃあ圧倒的に速いに決まってるけど
https://excel-ubara.com/excelvba4/EXCEL268.html

**デフォルトの名無しさん** · 2018/05/10(木) 17:54:52.83

エクセルマクロはやりたい放題なのにセキュリティとか片腹痛いわ

**デフォルトの名無しさん** · 2018/05/10(木) 19:14:27.72

Excel VBAで悪意のあるもの作るには本人の意思が必要。
外部ソフト入れるなら意図しないものが混入する可能性がある。
これぐらい理解できないのか？

**デフォルトの名無しさん** · 2018/05/10(木) 19:26:28.88

オプソならすっきり解決

**デフォルトの名無しさん** · 2018/05/10(木) 20:08:04.83

>>811
内部犯やチョンボを考えられないあたりが事務員さんって感じで微笑ましい

**デフォルトの名無しさん** · 2018/05/10(木) 21:33:49.65

>>811
怪しいサイトからダウンロードするならまだわかるけどメジャーなオプソでそんなコードが見つかったら祭りになるわ w

**デフォルトの名無しさん** · 2018/05/10(木) 21:36:46.67

EXCELはWebシステムみたいに外部と繋がってないから
比較的被害は小さくて済むよね

でも以前仕事場で
内部にファイル勝手に消えたり
外部接続の無い環境でウイルスらしきものが見つかったりしたことがあって
上の人から秘密裏に頼まれてファイル監視システム作ったこともあったな

**デフォルトの名無しさん** · 2018/05/10(木) 21:39:40.05

>>813
チョンボでセキュリティを突破するようなもの作れるの？

>>814
で、どうやって有名無名を判断するの？

会社勤めしたことあるのかって発言してるよ?w

**デフォルトの名無しさん** · 2018/05/10(木) 21:58:33.12

>>816
一斉メールのBCCをCCに入れちゃったみたいなクソみたいなミスでも情報流出は情報流出
内部から持ち出すのは悪意あろうと無かろうとほんと簡単なんだよ
学生さんはのんきでいいよな

**デフォルトの名無しさん** · 2018/05/10(木) 22:00:56.75

>>817
急に持ち出す話？w
ccとかbccを覚えたてで使いたかったのかな？w

**デフォルトの名無しさん** · 2018/05/10(木) 22:39:02.43

>>815
webシステムが外部と繋がってる？
イントラも知らんのか？

>>816
判断してやるから具体的な名前だしてみ
てかまともな情シスならホワイトリストとか持ってるし

**デフォルトの名無しさん** · 2018/05/10(木) 22:49:26.50

>>819
オマエが判断するのかよw

**デフォルトの名無しさん** · 2018/05/11(金) 00:26:28.42

揚げ足の取り合いというか、マウント合戦というか
油断するとすぐ始まるｗ

**デフォルトの名無しさん** · 2018/05/11(金) 00:34:08.57

>>798,800
prototype.jsのようなVBAファイルがあればと思ったので。
個々にであればいくつか見つけているので、その都度対応するようにします。

**デフォルトの名無しさん** · 2018/05/11(金) 01:02:06.52

色んなソフトがAPI公開してexcelと連動して、みたいなこと言ってるけど実際やってる人いる？
わざわざエクセルから操作する必要性が分からない

**デフォルトの名無しさん** · 2018/05/11(金) 04:46:34.78

>>823
昔、カルテサーバから翌日の輸血オーダ情報を取得して、ちょっと複雑にレイアウトしたTEPRAフォーマットに流し込んで、ハーフカットで輸血ラベルをジャンジャン印刷するのを作った記憶が。
あんまりたいした内容じゃなかった気がするけど。
それこそExcel必要？って言われるかもだけど、現場受けはよかったような。

**デフォルトの名無しさん** · 2018/05/11(金) 05:04:34.87

>>819
お前の会社のイントラは世界中の晒しものになっているのか
そりゃ危険だな

**デフォルトの名無しさん** · 2018/05/11(金) 06:51:44.72

>>824
SPC9-APIとか使ってたなら.csvファイル経由でデータを渡すからExcel使うのはある意味自然だと思う

**デフォルトの名無しさん** · 2018/05/11(金) 06:52:25.93

>>825
？？？
ますます意味不明になってるぞ w

**デフォルトの名無しさん** · 2018/05/11(金) 07:49:28.76

>>824
ありがとう
そういうちょっとした手間の省略は大事よね
もう少し探って見るわ

**デフォルトの名無しさん** · 2018/05/11(金) 08:33:12.57

PSキチガイもいなくなってひと段落といったところか
何か前他の板でもPSはキチガイ独占みたいなのを見たことがあった
それは多分PowerShellとは違うPSなんだろうけど
得てしてPSと言うのはそんなんばっかだってことだろうな

**デフォルトの名無しさん** · 2018/05/11(金) 08:45:45.24

何一つ言い返せなかったけどそれじゃ悔しいから敵が去った後に負け惜しみを言う人w

**デフォルトの名無しさん** · 2018/05/11(金) 08:45:45.76

>>829
お前も消えろ

**デフォルトの名無しさん** · 2018/05/11(金) 11:08:18.88

シート1のA44:I44のセルを、マクロでシート2の最後列に貼り付けていきたいです。
下記のマクロだと、シート1のA44のセルをシート2の最後列のAのセルにのみ、コピーするだけになってしまいます。
どう変更すればいいのか、よかったら教えて頂きたいです。
よろしくお願いします。

Sub テスト()
Dim LastRow As Long
With Worksheets("シート２")
LastRow = Worksheets("シート２").Range("A"&Rows.Count).End(xlUp).Row + 1
Range("A"&LastRow).Value=Worksheets("シート１").Range("A44:I44").Value
End With
End Sub

**デフォルトの名無しさん** · 2018/05/11(金) 11:49:30.00

VBA エキスパートスタンダードより難しい資格ってありますか？

**デフォルトの名無しさん** · 2018/05/11(金) 12:16:30.12

>>833
えんべでっどすぺしゃりすと

**デフォルトの名無しさん** · 2018/05/11(金) 12:17:44.23

>>832
なんでも質問スレの方に書き込みました
すいません

**デフォルトの名無しさん** · 2018/05/11(金) 12:41:09.45

>>834
VBAのスレで聞いてるんだからVBAの資格ってことだってわかるだろw

**デフォルトの名無しさん** · 2018/05/11(金) 13:06:15.00

>>836
最近はpowershellの話題も取り扱うようになったからそっちの資格かもしれない

**デフォルトの名無しさん** · 2018/05/11(金) 19:31:54.26

入力ヴァリデーションのやり方を教えてください

表形式のデータで1行1エンティティです
入力長さ、最小最大値、正規表現パターンなど基本的なカラムごとに独立したヴァリデーション
開始日終了日の順序などのエンティティ内の複数カラムにまたがるヴァリデーション
REST APIでサーバーに問い合わせる非同期ヴァリデーション
集計値の最大値やリレーションの存在確認など表全体のヴァリデーション
を行いたいです

入力が止まるとUXが悪化するのでエラーメッセージはメッセージボックスで表示したくありません
一定時間で消えるかフォーカスすると消滅する吹き出しのようなコントロールが理想です

これらを可能な限り宣言的にかつGUIからのチマチマとした面倒な設定は無しで実装したいです
REST APIや表全体のヴァリデーションの実体はUIと独立したCOMコンポーネントが行うので後はUIと紐付けるだけです

**デフォルトの名無しさん** · 2018/05/11(金) 20:13:41.96

>>838
文章だけで聞くレベルじゃないと思う

**デフォルトの名無しさん** · 2018/05/11(金) 20:33:45.50

>>839
C#erか、Rubierか、PSerからの宿題じゃないの？

**デフォルトの名無しさん** · 2018/05/11(金) 21:41:23.33

ガチガチに入力規制するなら素直にAccess使おうよ…

**デフォルトの名無しさん** · 2018/05/11(金) 21:43:15.64

>>838
もう自分でやり方書いちゃってるじゃん
その通りに実装すれば良いだけじゃないの？

**デフォルトの名無しさん** · 2018/05/12(土) 00:56:13.10

駆け出しの自分には質問内容すらさっぱりなんだが
このスレの住人はこの話の内容が理解できるくらいのレベルなの？
久しぶりに日本語が分からないって思ったわ

**デフォルトの名無しさん** · 2018/05/12(土) 01:10:53.14

>>838
入力したそばからバリデーションを走らせたいの？

**デフォルトの名無しさん** · 2018/05/12(土) 04:58:53.18

>>843
データベースではよくあるけど間違ってもExcelでやる事じゃないから気にしなくていいぞ

**デフォルトの名無しさん** · 2018/05/12(土) 07:55:43.25

無料の「Rails チュートリアル」で勉強すれば？
例えば、Rails で一般的な入力バリデ(validation)は、

空ではない
presence: true

1～50文字
length: { in: 1..50 }

整数のみ
:only_integer

**デフォルトの名無しさん** · 2018/05/12(土) 08:27:40.09

>>843
Webアプリケーションなら典型的と言っていい

**デフォルトの名無しさん** · 2018/05/12(土) 09:48:01.99

>>838
こういうのはポリモーフィズムを利用したくなる典型例の気がする。
型ごとに checkMe メソッドを定義するとか。
ちょっと古くさいかもな。

**デフォルトの名無しさん** · 2018/05/12(土) 10:58:14.77

凄いよ
MVVMやってる横で
MVCで頑張りましょうって言われた感じ
流石PSはキチガイ独占だNE

**デフォルトの名無しさん** · 2018/05/12(土) 11:27:49.88

>>849
どこにレスしてんの？

**デフォルトの名無しさん** · 2018/05/12(土) 11:31:03.75

>>849
MVVMってなに？（wikipediaのページを読みながら）

**デフォルトの名無しさん** · 2018/05/12(土) 11:43:33.47

>>851
京急で採用してるインバーターの事やで(大雑把なボケ)

**デフォルトの名無しさん** · 2018/05/12(土) 19:33:17.11

VBAにはMVVMもMVCもない
でも事務員さんのツールだからそれでいい

**デフォルトの名無しさん** · 2018/05/12(土) 19:47:10.61

事務員て何？
システム開発の専門家以外のホワイトカラーだったら全部事務員でOK？

**デフォルトの名無しさん** · 2018/05/12(土) 19:50:29.63

IT、システムが専門じゃない人でいいんじゃないの？

**デフォルトの名無しさん** · 2018/05/12(土) 21:19:03.46

Excelとパワポしか触ってないIT元請け企業の社員とかね

**デフォルトの名無しさん** · 2018/05/13(日) 00:43:57.24

過疎ってんなぁ

**デフォルトの名無しさん** · 2018/05/13(日) 09:38:43.13

クソな話題文句の言い合いでただ伸びているよりはマシ

**デフォルトの名無しさん** · 2018/05/13(日) 09:42:02.16

クソ or 過疎ならもうスレ要らなくねえか？

**デフォルトの名無しさん** · 2018/05/13(日) 10:08:12.60

土日くらい休めよ

**デフォルトの名無しさん** · 2018/05/13(日) 10:55:04.93

今までC++とかの酷い奴も来たけど
今回のPSキチガイは群を抜いて酷かったな
もう二度と現れないで欲しいよね

**デフォルトの名無しさん** · 2018/05/13(日) 10:55:35.20

二分探索、そしてMatchでこれを活用する方法、理解できました。
・要：調査表の検索キーソート。
・検索キーが数値を含む文字列なので桁揃えする必要あり。
・Matchでは完全一致での二分探索はできないので、戻値と元値の比較が必要。
この編がポイントでしょうか。
目標10秒以内を目指し、皆さんのアドバイスを元に順に試しましたが、結局この二分探索
のすさまじい結果に驚き、辞書までは試しませんでした。多分これも早いんだろうけど、、
（つづく）

**デフォルトの名無しさん** · 2018/05/13(日) 10:55:56.66

>>862
（つづき）
少し条件を変えて、調査表(1.4万) x 駆動表(1万)で試しました。

[線形探索]
　・Match線形探索／調査票(Range) ... 22.9 Sec
　・Match線形探索／調査票(Array) ... 79.12 Sec

[For入れ子]
・調査票、駆動表ともにArrayなげこみ ... 53.10 Sec
　 For2重Loop。発見時、Exit Forで次へ。

[二分探索]（調査票はSort済)
　・Match二分探索／調査票(Range) ... 0.42 Sec
　・Match二分探索／調査票(Array) ... 117.24 Sec
（つづく）

**デフォルトの名無しさん** · 2018/05/13(日) 10:56:21.29

>>862
（つづき）
結果、RangeObjectへのMatch二分探索が最強でした。
Arrayに乗せると、どんどん結果が悪くなるのが意味不明ですが、、
Match部分に時間がかかっているのは明らかでしたが、これを二分探索にすることで
１万回のMatchが 0.4 Secで終わるというのはすさまじいですね。

最終的に、検索キーの供給とマッチング、そのあとのSQL処理まで含めて
約100 Secかかっていたのが、0.8 Secで差分処理出来るようになりました。
みなさん、アドバイスいただきありがとうございました。

**デフォルトの名無しさん** · 2018/05/13(日) 11:03:24.93

>>861
なにも言い返せなかったからなぁ
悔しいねぇ

**デフォルトの名無しさん** · 2018/05/13(日) 13:26:19.68

>>864
二分探索なのにArrayでもMatch使ってるのがよくわからない。
文字列としての単純比較じゃダメなのか？

**デフォルトの名無しさん** · 2018/05/13(日) 15:10:20.32

>>864
たった一万件だろ
それなら誤差
8080の時代じゃねーよ

**デフォルトの名無しさん** · 2018/05/13(日) 15:44:53.98

>>867
22 Sec と 0.4 Secを誤差と？　すごい感覚の持ち主ですね。

**デフォルトの名無しさん** · 2018/05/13(日) 15:48:29.20

>>866
両テーブルをArrayに乗せてForで回して単純比較したのですが、50 Secほどかかってしまったのです。
Matchを使わない場合、どう記述すればよいかわからず。
ヒントをいただけますか？　もっと効率がよい方法があれば試してみたいです。

**デフォルトの名無しさん** · 2018/05/13(日) 16:14:11.00

こんな程度のどうでもよい事の効率を上げるより仕事全体の効率を上げる努力をしなさいって社長が言ってたよ

**デフォルトの名無しさん** · 2018/05/13(日) 16:20:34.22

>>868
お前のプログラムがおかしいだけだろ

**デフォルトの名無しさん** · 2018/05/13(日) 17:40:36.23

50secとか
どれだけ大規模システムを作られてるの？
もしかしてヤフーとかアマゾンとかのSE担当者さんですか？

**デフォルトの名無しさん** · 2018/05/13(日) 17:45:12.77

高速化の問題は細かい情報がないと話にならんよ
テスト用データと自分が書いたコードぐらいはGithubにあげてから質問してくれ
もちろんエクセルファイルの直接交換はセキュリティなど面倒事が多いからテキストファイルでな

**デフォルトの名無しさん** · 2018/05/13(日) 18:11:36.74

>>873
元質問は>>771,>774,
条件は >>862, >>863, に書いたとおりで
80 Sec かかったのは >>790 のコードです。

二分検索で 0.4 Secに短縮できたのは以下のコードです。関数にしました。
Function isReg(STR As String, RNG As Range) As Boolean
　Dim IDX As Long
　isReg = False
　With Application
　　IDX = .IfError( .Match(STR, RNG, 1), 0 )
　　If IDX = 0 Then
　　　' do nothing
　　ElseIf STR = .Index(RNG, IDX, 1) Then
　　　isReg = True
　　Else
　　　' do nothing
　　End If
　End With
End Function

50 Sec かかった単純比較はこんな感じのForの入れ子です。
For i = LBound(arrDrvList) To UBound(arrDrvList)
For k = LBound(arrFdList) To UBound(arrFdList)
If arrDrvList(i, 1) = arrFdList(k, 1) Then
(処理)
Exit For
End If
Next
Next
どんな手法がよいか教えてください。Matchにこだわるつもりはありません。

**デフォルトの名無しさん** · 2018/05/13(日) 18:16:05.72

>>873
あ、テストデータですね。
=TEXT(RANDBETWEEN(1,1000000),REPT("0",7))&"-"&TEXT(RANDBETWEEN(1,2),"000")
で生成できます。
駆動側が1万行、調査側が1.4万行で生成できます。

**デフォルトの名無しさん** · 2018/05/13(日) 18:18:17.66

>>874
それのどこが二分探索なのか俺にはよくわからない。
とりあえず自分のプログラムでArray上のデータをバイナリサーチすると、
調査表、駆動表ともに1.4万件でやって 1.9Sec だった。(Core i5)

**デフォルトの名無しさん** · 2018/05/13(日) 18:19:47.21

>>875
日本語おかしいな。
駆動側が1万行、調査側が1.4万行。駆動側の表から1万回Matchをかけました。
調査側の表はSort済みですが、これをArrayに投げ込んで行うとなぜが遅くなります。

**デフォルトの名無しさん** · 2018/05/13(日) 18:22:03.91

>>876
Matchの第3引数を0以外にするとバイナリサーチになるんだそうです。

その1.9Secの結果をだせたコードの書き方が知りたいです。

**デフォルトの名無しさん** · 2018/05/13(日) 18:24:18.63

>>874
動作する完全なコードを書いて
測定処理も含めたすべて

**デフォルトの名無しさん** · 2018/05/13(日) 18:40:41.94

>>874
それ線形探索