Excel VBA 質問スレ Part63

2019/09/16(月) 19:34:59.04

!extend:checked:vvvvv:1000:512

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

※前スレ
Excel VBA 質問スレ Part62
https://mevius.5ch.net/test/read.cgi/tech/1561303297/

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

2019/10/26(土) 21:44:56.09

>>454
ここはExcelスレじゃない

2019/10/27(日) 04:54:35.83

33sands's blog: VBAでpushやpopができる配列クラスを実装しました
http://33sands.blogspot.com/2012/03/vbapushpop.html

このブログで、VBA自作配列クラスが公開されていたようですが、ファイルが消えていました。
ここで公開されていたファイルをお持ちの方はいませんでしょうか？

2019/10/27(日) 07:39:28.03

いないよｗ

2019/10/27(日) 07:48:21.99

WebArchive

2019/10/27(日) 08:55:23.11

>>449
月曜日に試してみたいと思います。
ありがとうございます。

2019/10/27(日) 09:08:45.25

>>456
そこに書いてある Ubound(配列) + 1 ってのは、よく見かける見る例で、
ループ内で使うと思うんだけど、毎回配列の最大数調べてたら遅くならんかなぁ。
俺は a = a + 1 にしちゃうけど、変わらんか?

2019/10/27(日) 10:16:44.42

>>460
VBAでpushを実装しようと思うとこうするしかないぞ

2019/10/27(日) 11:21:18.40

>>460
たいして変わらんし、多少変わったとしても次の
ReDim Preserve myArray(newIndex)
に比べたらゴミ

2019/10/27(日) 11:24:16.68

>>449
currentregionプロパティで下記のは可能でしょうか？

ABCDEFGHI(列名)
q 12 33 q

A～I列の内、B～H列内で数字がある最初と最後の列番号を取得したいと思ってます。

通常では448に書いたようにB～H列を指定して.ENDでxltoleft/rightを使い2行と2つの変数に結果を入れる事でできたのですが、計算式だとNGだったので(どちらもH列が帰ってきました)

2019/10/27(日) 12:19:45.36

>>456
なんで作者本人に連絡取らんの

2019/10/27(日) 13:19:15.84

>>463
計算結果が数値の最初と最後の列ってこと？
xlToLeft; xlToRight; はセルが空かどうかでしか判断できないから使えないでしょ？
開始列と終了列からそれぞれなめて、IsNumeric(.value) で判定しないとダメじゃないかな。
なんかいい方法あるけ？

2019/10/27(日) 14:10:23.14

>>456
よく見てないけど計算量最適化できてなさそうだね

2019/10/27(日) 14:42:36.79

>>463
CurrentRegionでは恐らく無理
下のようにループを左右両側から回して調べるしかないんじゃないかな
列番号をチェックする行に含まれるセルを選択している状態という前提です

Dim RngA2I As Range 'チェック対象行のA列からI列までの範囲
Dim i As Long 'ループ用インデックス
Dim LMost As Long '左端列の列番号
Dim RMost As Long '右端列の列番号

With ActiveSheet
Set RngA2I = .Cells(.Cells(Selection.Row, 1), .Cells(Selection.Row, 9))
End With

For i = 2 To 8
If IsNumeric(RngA2I.Item(i).Value) Then
LMost = i
Exit For
End If
Next

For i = 8 To 2 Step - 1
If IsNumeric(RngA2I.Item(i).Value) Then
RMost = i
Exit For
End If
Next

2019/10/27(日) 14:55:15.64

>>466
中身わからんのに計算量を推定できるとかエスパーかよw

2019/10/27(日) 16:12:11.26

>>463
With Range("B:H")
'最初
.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
'最後
.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End With

値が入ってるセルが1つも無いとエラーになるから事前にチェックしておく

2019/10/27(日) 16:59:16.22

>>469
これで数値結果を返すセルを拾えるの？
文字列結果を返すセルも捕まえるみたいだけど、、

2019/10/27(日) 17:02:26.90

数字限定？なら>>469は忘れて

2019/10/27(日) 17:36:39.44

今の時代でマルチスレッドが使えない時点で終わってる

◆QZaw55cn4c (ﾜｯﾁｮｲ 7f47-U9uJ) · 2019/10/27(日) 17:40:21.78

>>472
マルチスレッドプログラミングは難しいので、なにか適切な抽象化方策が言語でとられるべきかと

2019/10/27(日) 20:13:15.91

>>468
Redimで連続アドレス領域を都度確保するのが効率悪いって言いたいだけなんだが

2019/10/27(日) 20:14:07.93

>>467
やはり両方から確認しにいくのが確実ですよね。
他の部署から来るデータで2シートだけは単純に空白と数値だけなのですが、他のファイルにある1シートだけは計算式入ってたので…

まぁ、最初と最後だけ確認してから他の作業をさせればいいので、これ参考にさせていただきます。
ありがとうございました。

2019/10/27(日) 20:16:11.25

>>469
数値限定なんです。
さらにその数値から●を他のシートに入力していくとかいうメンドクサイ作業も待ってるという…(そっちは早々に作りましたが)

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

2019/10/27(日) 21:21:01.09

>>474
どこからその都度確保なんて出てきたんだ？
>>456のリンク先のコードはサンプルでクラスモジュールのコードがそのままとかエスパーならわかるの？

2019/10/27(日) 23:13:53.49

>>476
その作業、AccessとかPowerQueryでやった方がいいんじゃないの？
VBAだと車輪の再発明になる気がするぞ

2019/10/27(日) 23:43:54.17

面倒臭いからVBA使うんだろ

2019/10/27(日) 23:53:42.61

AccessやPowerQueryを知らずに言ってるんだろうがテーブルへのクエリをExcel VBAでスクラッチする方が遥かに面倒だぞ

2019/10/27(日) 23:55:56.34

448のどこがaccess、powerquery向きなのか理解出来ない

2019/10/28(月) 01:11:50.89

最初からデータをDBへ入れて扱っていれば「端の値を取る」などというトンデモ要件はそもそも出てこないということだろう
業務ロジックの中でシートの右だの左だの何列目だのとシート上でのレイアウトに依存したコードが散乱するのは典型的な悪夢

2019/10/28(月) 08:44:07.27

448です。
元は手作業でやっていたのを自動化？したいとかで外の会社とかも関わってるので、DB化とかそういう話になるとメンドクサイ事になるらしいです。
自分は他から来ているので、あまり口出しできない状態。

あと、作業の行にカレンダーがあって、そのカレンダーに数字が入力されてる感じです。
なので月ごとに場所が変わったりするので…
そんなのが数百行あるので、少しでもその作業の先頭列が分かれば…と思っていたのですが…

2019/10/28(月) 08:46:00.28

>>482
確かに…
Excelで手作業だったって事もあって、項目によっては行が結合されていたりとかもあってさらにメンドクサイ状態。

そのへんなんとかしてほしいのだが、そうなると外の会社との折衝にもなるらしいのでNGっぽい…orz

2019/10/28(月) 11:38:38.31

プログラミングスレで聞くのが妥当か分からないけど
マクロの記録を使って「データ分析」ツールをSheet1、Sheet2....Sheet13と起動していくマクロを作ろうとしてるんだけど、どういうわけか「データ分析」で生成された表が現れるSheetと現れないシートにムラがある
規則性はないように思われる
なにかわかることありますかね

2019/10/28(月) 12:42:09.32

Mac版でcontrol+Shift+5で%が付くようにしたのですがこの設定を元に戻すショートカットってありますか？

2019/10/29(火) 04:42:35.22

userformの選択されているcheckboxのcaptionを変数にまとめてautofilterでデータ抽出しようとしているのですがうまくいきません
ちなみにそのような方法でautofilterをかけることは可能でしょうか？
よくわかんない質問ですいませんがよろしくお願いします

2019/10/29(火) 05:23:49.56

>>487
たぶん。
確か、配列にして渡すんだったと思う。
任意の複数選択フィルタリングを記録すればどう書けばいいかすぐわかるハズ。

2019/10/29(火) 17:33:39.24

看護師の勤務表を作りたいけど何からやればいいか分からないです。教えてください

2019/10/29(火) 17:46:30.71

単純に勤務表ってだけなら表計算でできることなのでエクセルスレへどうぞ（厳密には表計算ですら無いけど）
VBAは大雑把に言うと表計算だけでは実現でいないことをやるための技術です

2019/10/29(火) 17:51:20.57

VBA使わずに自動で1ヶ月分作れるんですか？

**デフォルトの名無しさん** (ｽﾌｯ Sd1f-omNk) · 2019/10/29(火) 18:01:49.00

Office365のカレンダーがいいよ

2019/10/29(火) 18:03:40.61

その場合は必要
後出しで条件を追加すると話が長くなるし回答も二度手間になるので最初にやりたいことをきっちり説明すべし

2019/10/29(火) 18:11:33.96

配列に入ってる値をセルに一個ずつ入れたいのですが、

for i = LBound(配列) to UBound(配列)
Cells(i.1).value = 配列(i)
Next i

こう書くと型が違いますとエラーになります
どうすればいいですか？

2019/10/29(火) 18:12:54.47

勤務表を自動で作成したいとなれば、アルゴリズムの知識がないと実装できないと思う。ナース・スケジューリング問題とか調べてみるといい。参考になる文献が出てくる。ただ、いっそのこと有料ソフトを買った方が早い気がする。

2019/10/29(火) 18:23:00.38

>>494
そらi＝0から始まるけどセルに座標0はないんで

2019/10/29(火) 18:26:41.51

>>494
range("a1").resize(Ubound(配列)ｰLbound(配列)) = _
worksheetfunction.transporse(配列)

2019/10/29(火) 18:28:33.17

訂正
transpose

2019/10/29(火) 18:35:46.74

Cells(i+1,1).valueかCells(1,1).Offset(i,0).valueにする

2019/10/29(火) 18:36:33.04

>>489
馬鹿は死ね

2019/10/29(火) 18:41:35.33

エクセル以外のソフトをインストール出来ないってアホみたいな環境はソッコーで辞めた方が良い
VBAなんて将来性もないクソ言語を覚える意味は全くない
本当に他の開発環境は無理なの？
冗談だよね？

2019/10/29(火) 19:27:11.79

>>500
おう。お前が死んどけ

2019/10/29(火) 19:48:08.52

>>494
取り敢えずみんなが言ってる通り配列のインデックスは0から始まるし
Cellは行にしても列にしても1から始まるのでそこんとこ気をつけなければいけません。

後はセル指定する際にはブック、シートも指定しておく癖をつけておくことをお勧めします。

2019/10/29(火) 19:57:54.69

>>501
仕事場によっては、特に銀行系は
使うもの以外勝手にインストールしてはいけないところが多々有ります。

例えばJavascriptだけ使ってればいいのに
勝手にVisualStudoやTOMCATとかをインストールすると怒られるどころか
コンプライアンスの面から仕事場を退場させられる場合もあります。

EXCELは大概どこの仕事場にも入っているから
インストールしなくても使えるだけで、
当然入れてはいけないと言われたら勝手に入れるべきではありません。

むしろ「勝手にどんどん何でもインストールしていいですよ～」なんて仕事場があったら
そこのセキュリティはどうなってるのか疑うべきと思われます。

2019/10/29(火) 20:03:23.90

マクロを許可してる方がセキュリティ意識ゼロ

2019/10/29(火) 20:06:27.60

開発メンバーは基本何でもできるからしょうがないだろ
Ｅｘｃｅｌマクロがだめなら開発環境の言語使うわ

2019/10/29(火) 20:10:23.13

cmdとか使える時点でセキュリティも何も合ったもんじゃないと思うけどね

2019/10/29(火) 20:22:40.07

>>496
>>497
目から鱗ですありがとうございます
早速明日試してみます

2019/10/29(火) 20:28:16.99

>>503
ご教授ありがとうございます
めっちゃすっきりしました
やっぱり１人でうんうん悩むより聞いた方が早いですね
勉強になります

2019/10/29(火) 23:06:14.03

鱗付いてるとか爬虫類かよ

2019/10/29(火) 23:35:46.46

>>502
死ね、クズ

2019/10/30(水) 10:46:19.82

>>510
爬虫類のわけないだろ

2019/10/30(水) 10:55:31.55

>>441
>>443
少し組める人は絶対にSelectを使わないという姿勢で組むものです。
何故なら無駄である上に速度が低下するので百害あって一利無しだからです。
しかし、ごく一部Selectしないと実現出来ない処理があります。
形式を選択して貼り付ける処理などがそれです。
それから>>443さんのおっしゃるようにユーザーに選択している場所を提示するためにわざとSelectする場合もあります。

2019/10/30(水) 11:02:18.69

>>501
結構多いし、そもそもExcelだけでどうとでもなるから。
能力の低い奴はExcelのせいにするけど、こっちは君が対応出来ないことも出来るんだよ。

確かにどうにもならないことならその時にはじめて他のソフトを検討するけどね。

2019/10/30(水) 11:04:17.20

>>503
>>509
配列の設計はどうにもならんの?
別に1始まりにも出来るし、2次元にすればもっと良い。

2019/10/30(水) 11:07:13.09

>>504
勝手に入れる奴はそもそも居ないでしょ。
Excelは大概どこの仕事場にも入っているのは事実だけど、それが理由ではない。
入っていなければ最初から選択肢に無いし、このスレに書き込んでもいない。

2019/10/30(水) 17:43:14.91

>>487
>>488
Dim i As Integer
Dim n As Integer
Dim 検索値(0 To 3) As String
n = 1

For i = 1 To 4

If Me.Controls("CheckBox" & i).Value = True Then
検索値(n) = Me.Controls("CheckBox" & i).Caption
n = n + 1
End If
Next i

ActiveSheet.Range("$A$1:$J$41").AutoFilter Field:=2, Criteria1:=Array(検索値), Operator:=xlFilterValues

素人ながらにこんな感じで書いてみたのですが、こうするとチェックした項目だけでなく空白セルもautofilterもかけてしまいます。
どうしたら空白セルを除外することができますか？

2019/10/30(水) 20:32:13.04

end ifの前にredim preserve 検索値(i)は？

2019/10/30(水) 20:57:54.67

VBAのユーザーフォームはWindows98の時代で止まったまま
あれじゃあ恥ずかしいよね

2019/10/30(水) 21:12:32.09

2019でファイルを開いた時に、勝手にコンボボックスのクリックイベントが発動するんだけど、何で?
もちろん、ワークブックのクラスには何も書いてない状態。
2013ではそんな事なかったと思うんだけど。
このせいで、一切編集せずに閉じようとしても、いちいち保存するか聞いてくる。

2019/10/30(水) 21:15:30.81

>>517
だいたいそんな感じですね。
以下で行けました。(Dim文、略)
Control名いじるのが面倒なので、FrameにChkBox
つっこんで回しています。

iCnt = UserForm1.Frame1.Controls.Count - 1
ReDim ARR(iCnt)

i = -1
For Each vBuf In UserForm1.Frame1.Controls
　　With vBuf
　　　　If .Value = True Then
　　　　　　i = i + 1
　　　　　　ARR(i) = .Caption
　　　　End If
　　End With
Next

ActiveSheet.Range("範囲").AutoFilter _
　　Field:=1, _
　　Criteria1:=ARR, _
　　Operator:=xlFilterValues

2019/10/30(水) 21:19:32.90

>>520
アドインでアプリケーションレベルのイベントハンドラ動かしてるとかじゃないの

2019/10/30(水) 21:22:06.27

ワークブッククラスの話までできるなら切り分ければいいのに。

2019/10/30(水) 21:37:14.35

>>522
アドインを無効にしたり、
ワークブックオープンイベントの最初にEnd入れてみたりしたけど、ダメだった。

>>523
最悪は違う手を考えるけど、こんな変な仕様だったのか?と思って。

2019/10/30(水) 21:39:34.26

↑ワークブックオープンイベントの最初にEndって、アドインの方ね。

2019/10/30(水) 21:45:25.88

↑クリックイベントだけじゃなくて、チェンジイベントもだった。
何もチェンジしてないっての。

2019/10/30(水) 21:54:49.68

>>525
Worksheet_Activateイベントあたりが悪さしてるんじゃないの
シートイベントってほんとにゴチャゴチャになりやすいから

2019/10/30(水) 21:55:52.89

ある数値が範囲(***-***)や、範囲と単体の組み合わせを、カンマで区切っているのを降順に配列に格納したいけど、なんか上手くいかないです。

全部VBAでやろうとしているから…なのかな？と思い始めてます。
それ用のシートを作って、そこに1つずつ入れてソートさせた方が簡単…なのでしょうか？

2019/10/30(水) 22:06:31.14

>>528
10, 5, 1-3, 6 7-9, 11
みたいに数字が並んでるのを、カンマ区切りの文字列ではなく数字の評価をした上で降順ソートしたいってこと？
まず今手元にあるコードを書いてくれ

2019/10/30(水) 22:18:54.50

>>527
それも見たけど、ないなぁ。
ていうか、コンボボックスが置いてあるシートがアクティブじゃなくても発動するし。

2013では、VBAでオブジェクトにフォーカスを移すと、
初回のみカーソルが見えなくなる現象が起きたと思うけど、
その対策として何かやってたりして。

2019/10/30(水) 22:21:08.45

>>528
VBAでもシートに書き出してからでもどっちも大した手間は無いよ
まず範囲を1-3なら1,2,3と展開して全体をカンマ区切りだけにしてから、
前者なら配列に格納→配列の中身を降順でソート
後者ならシートに貼り付け→ソート機能使えばいい

2019/10/30(水) 22:34:12.92

>>530
実装レベルでの不具合の可能性もあるし、ブックのxmlだけが壊れてる可能性もある
コンボボックス以外のオブジェクトの挙動は試してみた？

2019/10/30(水) 23:11:10.26

>>529
すまん、コード会社にあるから書けないけど、やりたい事はそんな感じ。

最初にカンマでsplitして配列に入れてからfor～nextで0からuboundで最後まで繰り返して「-」になってる範囲を数字にバラしてその間の数字を作って配列に入れ直して…

とかやってたらコードぐちゃぐちゃ、頭の中もぐちゃぐちゃに…orz

2019/10/30(水) 23:22:56.07

>>533
馬鹿には無理ってことだよ

**デフォルトの名無しさん** (JP 0Hff-Aa88) · 2019/10/30(水) 23:42:55.97

ソートを自前で書くのは速度的にもメリット無いし作業シート用意した方がいいと思う
そういうとこで苦労するのはちょっと時間の無駄かなとも思うし

2019/10/30(水) 23:56:52.02

>>533
配列を作るところまでは多分いけるんだろうけど、問題はソートなんじゃないの？
配列のソートをサポートしてる.NETのArrayListオブジェクトはVBAでも使えるので、CreateObject("System.Collections.ArrayList")でインスタンス生成してAddメソッドで配列を渡して使うといいよ
Revereメソッドを噛ませてからToArrayメソッドで再度配列化して配列変数に再格納すれば一発で降順ソートされた配列を取得できる

2019/10/31(木) 01:50:11.06

Excel2016/32bitで作成したマクロをExcel2019/64bitのパソコンに移動すると途中でExcel自体が落ちてしまいます
この場合、2019にしたこと、64bitにしたこと、どちらが原因と考えられるでしょうか？

2019/10/31(木) 06:48:15.19

そのくらいしらべられないの？

2019/10/31(木) 08:04:58.17

>>537
動かないマクロファイルのファイル形式は？

2019/10/31(木) 10:03:55.60

>>538
調べてもわかりませんでした
>>539
xlsmです

2019/10/31(木) 10:04:33.47

ちなみに動く時もありますがたまに落ちるという不安定な状態です

2019/10/31(木) 11:41:33.83

また馬鹿が来た

**デフォルトの名無しさん** (ｽﾌｯ Sdea-t/t6) · 2019/10/31(木) 12:23:33.00

>>540
何をどう調べてどういう結果になったの？

2019/10/31(木) 12:30:33.86

533です。
結局分割をしたデータをセルに置いてソートをさせて配列に入れるコードで問題なく動きました。
ありがとうございました。
変にVBAてわ全てやろうとせずにすればここまで時間かけずに済んだのですね…orz

2019/10/31(木) 12:42:50.28

どうせマクロ作った奴が手抜きしててエラーになったらApplication.Quit()してるとかじゃね
みんなエスパーじゃないんだからコード上げられないなら自分でチマチマデバッグしなよ

2019/10/31(木) 16:13:16.49

>>518
>>521
ありがとうございます

2019/11/01(金) 00:20:58.64

VBSスレが過疎り過ぎているのとWSHのスレが無いことから、ここでVBSの質問させて下さい。

WshShellのRunメソッドを使ってTortoiseSVNのダイアログを呼び出すプログラムを書いているのですが、引数1を入れてもウィンドウが最前面に来てくれません。
Runした後にAppActivateメソッドを使ってもだめです。誰か、解決策をご存じの方、よろしくお願い申し上げます。

2019/11/01(金) 00:46:23.89

tortoiseSVNとやらを触った事ないけど、
tortoiseSVNだけ起こる事象なの？他のNotepadとかでは試しました?

2019/11/01(金) 01:08:36.00

spy++とかで窓id探ってapi使ってhandleできないかな

2019/11/01(金) 01:38:24.11

>>536
レコードセット以外にも色んな方法があるものだね。勉強になったありがとう。

2019/11/01(金) 16:54:14.94

こちらのページ(https://www.sejuku.net/blog/99122)に記載されている
PDFをテキスト抽出してエクセルに貼るマクロを利用したいのですが、
ファイルとフォルダのパスを固定した記述になっているため、
ベースとなるPDFファイルを選択できるようにしたいのですがどなたか教えていただけないでしょうか。
↓のアスタリスク部分をどうにかすればと２時間戦いましたがどうにもできませんでした...

'PDFのデータをExcelに読み込むメイン処理
Sub Main()
'PDFファイルをテキストに変換するための定数を用意
* Const fileName = "*****"
* Const folderPath = "C:\Users\*****\"
Const pdfFilePath = folderPath & fileName & ".pdf"
Const txtFilePath = folderPath & fileName & ".txt"
'PDFファイルをテキストに変換
Call convPDFtoText(fileName, folderPath, pdfFilePath, txtFilePath)
'テキストデータをExcelに読み込む
Call importTxtData(txtFilePath)
End Sub

2019/11/01(金) 18:51:05.73

>>551
GetOpenFilenameでパスとファイル名を取得

2019/11/01(金) 19:03:05.01

>>536
.Netのオブジェクトが使えるというのは
正直目からウロコだった。

いいね。.Net使いの俺からすると
機能に制限があるとは言っても有り難い。

ただ、参照設定して使っても
インテリセンスが効かないのが
ちょっと玉に瑕だけど。

2019/11/01(金) 19:13:41.95

>>532
試してないけど、クリック→ドロップボタンクリックにしたら発動しなくなったから、もうそれでいいやw
1クリックで2回動くのが嫌だけど。
いちいち、2回に1回Exitする処理入れないといけないし。