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(水) 12:40:19.25

>>771
ソートしたか？

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

後釣りの意味がわからないならググれ
どのレスのこと言ってるのかわからないならこのスレを「釣」で検索

**デフォルトの名無しさん** · 2018/05/09(水) 12:56:39.40

>>772
検索範囲を昇順ソートしてみました。結果、、
　・Cell範囲の場合：　43sec
　・配列の場合：　127sec
ん？　更に延びた。さっきのはキャッシュでも効いてたのかな？
Stringの中身は "ID-枝番" 書式の文字列です。

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

>>774
検索アルゴリズムをバイナリサーチにしてないんじゃないの？

**デフォルトの名無しさん** · 2018/05/09(水) 15:17:53.88

初めまして。共有保護について教えていただけませんでしょうか。
共有保護解除→マクロ処理終了→共有保護設定をする方法を作ってみましたが問題がありました。
「On Error Goto」を入れるとエラー発生→共有保護設定後、一回閉じてまた開くと「共有」が消えてしまう。
色々試したけどダメでした。（「On Error Goto」を設定しないとうまくいく。）

Sub 共有保護解除設定()
Dim x As String：ｘ＝1
On Error GoTo myError
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveWorkbook.UnprotectSharing Sharingpassword:="aaa"

Do: Sheets( x ).Delete: x = x + 1: Loop

　　myError:

　　ActiveWorkbook.ProtectSharing Sharingpassword:="aaa"

End Sub

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

>>776
よく分からんけど
何故にエラーの飛び先をループの中に突っ込むのか

**デフォルトの名無しさん** · 2018/05/09(水) 18:16:21.44

>>765
実行ポリシーにはスコープという概念がある
スコープはマシン、ユーザー、プロセスがある
ユーザー、プロセスの実行ポリシー変更は管理者権限不要
ユーザースコープの実行ポリシーを変更するのがベターだね

>>763
コマンドプロンプトみたいにVBSファイルを指定して実行できるし
スクリプティング環境をComインスタンスとして生成することもできる
直書きというのは何のこと言ってるのかわからんな

需要がないからやったことないけれどexcelのイベントにCLRのハンドラを登録することは可能だよ
オススメはしない

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

>>771
VBAは基本的にシングルスレッドで動く
今のワークシート関数とかは、条件が整えばマルチスレッドで動く

なので、必ずしもVBAの方が早くなるとは限らんのだよ
Match関数がどうなってるかは知らんがな

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

二分探索使ってないとか初歩的なことじゃなく？

**デフォルトの名無しさん** · 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
お前のプログラムがおかしいだけだろ