Excel VBA 質問スレ Part76

2022/05/01(日) 02:23:41.78

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑2行に減ってるけど、同じ内容を3行に増やして貼り付けること

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

次スレは>>980が立てること
無理なら細かく安価指定

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

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

2022/05/19(木) 23:27:11.45

ゆっくりの顔はこれから無料で使用できなくなるんだよね？

2022/05/19(木) 23:45:08.95

できる
動画名にゆっくり茶番劇って入れたらダメ

2022/05/19(木) 23:51:09.33

>>141
ﾄﾝｸｽ。
オレのExcel2013だから今風のネタが作れないんだよね。
祝日込の予定表作ろうかと思ったら、自分のやつじゃpowerquery使えないことに気づいたわ。
だから誰もやってないネタとしてRPAもどきをやってみた。

2022/05/20(金) 12:02:22.42

2013でパワークエリ使っていた同僚いたけど、どうやったのだろ？？

2022/05/20(金) 14:24:00.71

>>145
使えるぞ
>>144が馬鹿なだけ

2022/05/20(金) 15:47:49.30

>>144
まぁ、やってみなはれ。
https://www.microsoft.com/ja-jp/download/details.aspx?id=39379&CorrelationId=85f847dd-369e-4417-b604-6a2f3c673084

**144** (ﾜｯﾁｮｲ 6f3d-fy+1) · 2022/05/20(金) 16:37:29.66

アドインを入れなきゃ使えんなんて知らんわ。
まあ、やってみるよ。

2022/05/20(金) 18:17:06.14

確かに。知っている人は少ないはず。2013にパワークエリアドイン。

2022/05/20(金) 21:07:42.32

>>149
使いたくて見つけられないのは馬鹿だと思うよ？

2022/05/20(金) 21:09:21.29

ニッチな動画すぎるわ
こういうのはたまに腕前を見せる程度でいいと思うぞ

2022/05/20(金) 21:34:55.10

エクセル動画って意外と伸びないんだよ
再生数あげてる動画も、数年かけてやっとこさ5桁とかそんなん

2022/05/20(金) 22:12:27.54

文字には勝てんよwww

2022/05/21(土) 00:43:35.28

YouTubeでしょ？
Excel動画の一部で100万回とか200万回再生動画あるけど、あれは例外かな？
配信者が人気度高いとか？

2022/05/21(土) 01:12:54.43

ぶっちゃけいちいち動画にするのやめてくれとしか思わんな
テキストに書いてくれと

2022/05/21(土) 07:53:33.56

それは見なきゃいいだけじゃね

2022/05/21(土) 08:06:57.86

>>156
？

**デフォルトの名無しさん** (ｽﾌｯ Sdba-eugl) · 2022/05/21(土) 08:54:23.72

たいがいのことは検索すればブログと動画と両方出てくる
好きな方を見ろとしか

2022/05/21(土) 09:03:39.72

>>153
文字だとさ、その分の数行を細切れに書くやつがいてさ、
その分だけ見せられても困る事が多い。
解説書気取りなんだろうけど。
VBAならいいけど、pythonとかだと全体が見れないとな。

2022/05/21(土) 12:06:30.15

>>159
ドーガってソース全体見せながら饅頭が喋るの？

2022/05/21(土) 13:24:13.30

64bitだとdatepickerが使えないんだけど代替有りますか？

2022/05/21(土) 22:05:53.36

>>161
無いんだな、これが
あと64bitじゃなくて2013か2016あたりから使えないはず

2022/05/22(日) 00:44:38.46

クリップボードに

あああ　いいい
123　　たたたた　　　ららら　12ああ

000　1
かかか　

あかさ　たなは　358 22558

のようにランダムな文字列や数字がはいっています。
空白や改行の数もランダムですがどちらも5個いないです。

あああ、や、いいい、の間は空白や改行のどちらかです。
文字列または数字を、空白か改行で区切って

横に10列ずつに並べたいです。
A1に「かかか」B1に「いいい」おなじように繰り返してJ1に「あかさ」、次はA2に「たなは」が入るようなイメージです。

うまく伝わっているかわかりませんが、どのようなコードを書いたらいいですか？

2022/05/22(日) 02:13:20.90

>>163
まずはちゃんと伝えるように書け

2022/05/22(日) 02:46:34.46

>>163
よくわからん部分もあるが
クリップボードからテキストフォーマットのデータをstrに読み込んで改行ごとに分割（spritかね）
分割した文字列を更にスペースごとに分割して2次元配列にいれる
あとはそれをセルに転写すればいいんじゃないの？
分けるのが改行と空白両方ならどっちかにリプレースしてからスプリット

2022/05/22(日) 06:43:53.78

>>163
とりあえずその状態で欲しい結果を書け

2022/05/22(日) 06:45:54.76

>>163
連続する改行と空白を消す。
for i = 1 to 5
a = Replace(a, vbCrLf & vbCrLf, "")
a = Replace(a, "　" & ”　” , "")
next

改行を空白に置換する

splitで分ける

配列をセルに入れる

2022/05/22(日) 06:46:20.24

>>166
A1に「かかか」B1に「いいい」おなじように繰り返してJ1に「あかさ」、次はA2に「たなは」が入るようなイメージで

2022/05/22(日) 08:05:25.80

どういう順番にしたらA1にかかかが来るのかがよく分からない

2022/05/22(日) 08:09:48.09

A1に「かかか」
↓
A1に「あああ」

間違えてるだけだろ、それぐらい見抜こう

2022/05/22(日) 08:43:32.82

書かれてる仕様を思い込みで勝手に書き換えたらいかんでしょ
作り込む前に確認を取らなきゃダメだよ

2022/05/22(日) 09:43:55.92

>>168
Dim S As String
With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
S = .GetText
End With
With CreateObject("VBScript.RegExp")
.Pattern = "[ \n]+"
.Global = True
Dim R As Long
R = 1
Dim C As Long
C = 1
Dim T As Variant
For Each T In Split(.Replace(S, " "), " ")
Cells(R, C).Value = T
C = C + 1
If 10 < C Then
C = 1
R = R + 1
End If
Next
End With

2022/05/22(日) 10:01:24.95

>>172
あらキレイ

2022/05/22(日) 10:11:15.36

>>173
綺麗に見えるだろ？
このコード、死んでるんだぜ…ﾍﾍｯ

2022/05/22(日) 10:37:59.11

>>163
VBAスレでいうのもアレだと思うけど

「改行、スペースで区切られたテキストデータを、10個ずつ(10列ずつ)で改行されるデータとして抽出したい」
って考えると正規表現可能なテキストエディタでやったほうが良いような気がする。

(　| |\r\n) を \t
\t+ を　\t
((.*?\t){2}) を $1\r\n

にみたいな感じで

2022/05/22(日) 13:05:55.15

>>163
同じくVBAスレでいうのもアレだし>>172でよいと思うけど

powershell -c "(gcb -raw) -replace '\s+', """"`t"""" -replace '((?:[^\t]+\t){9}[^\t]+)(?:\t|$)', """"`$1`r`n""""|scb"

VBAから実行するなりバッチファイル（.bat）にして実行するなりすると
整形結果がクリップボードに入るのでA1を選択して貼り付け。

すでにExcel上にあるデータなら>>172とかが有力だけど、
入力元がクリップボードならクリップボード格納前後（VBA前）で
テキスト処理に向いた言語や手法（>>175）で整形したらいいんじゃないの、と思う。
餅は餅屋、ってことで。
今回だとクリップボード操作と正規表現向き内容なのでPowerShellが向いてそう。

・実行環境のOS（Windows 7で実行されうるか）
・文字列にダブルクォーテーションが含まれうるか
・「空白」の正確な定義（レアな空白を「空白」に含んでよいか）
・『A1に「かかか」』の謎
・この整形処理前後の処理（処理全体、本当の処理目的）
などにもよるけど。

2022/05/22(日) 19:14:42.01

常にそうだとは言わないけど、VBAでいろんなことしようとする人って
会社内でセキュリティに縛られてVBAしか使えないような環境でこっそりやってる場合が多いんじゃね

うちなんてpowershellさえ使えない

2022/05/22(日) 21:09:09.39

>>177
VBAとVBScript/Jscriptはそれの定番だよ。
IEがなくなるとスクレイピングができなくなるよな。
そういう環境下だと、どうするんだろう。

**デフォルトの名無しさん** (ｽﾌｯ Sdba-SeQ4) · 2022/05/22(日) 21:19:32.54

163です。
レスくださった方ありがとうございました。

結論から言うと、>>172を加工してなんとなくうまくできました。
A1セルは「あああ」が正しいです。すみません。

2022/05/22(日) 21:33:47.57

それぐらい分かってるから大丈夫だ

2022/05/23(月) 17:38:33.02

表示するのにラグを発生させたい場合sleepかウェイトを試して見たのですがソースの途中に入れても実行最初に止まって全部表示されてしまうのですがなにかいい方法はないのでしょうか

2022/05/23(月) 17:51:32.07

何の表示だよ

2022/05/23(月) 17:58:05.38

セルに挿入です
for文を挟んでいるとどうもすべて止まって一括表示されてしまいます

2022/05/23(月) 18:38:04.00

ループ内で都度再表示させればいいんじゃねーの

2022/05/23(月) 18:45:16.94

excelのシートのA列に◎オプションボタンActiveXを付けた、B列には商品名（3000件くらいある）を入れた。
◎ りんご
◎ みかん
◎ ぶどう
◎ ばなな

ぶどうを選んだら、TRUEになった番号は「3番」って返ってきているのですが、3000件を設定するのが大変で一括で設定できる方法があれば何かヒントを教えてください。

2022/05/23(月) 20:02:47.45

>>181
なんのためのラグかを書けばもう少しマシなやり方を教えてくれるかも

2022/05/23(月) 20:48:26.86

>>183
DoEvents

2022/05/23(月) 20:58:57.01

Sub foo()
Range("a1:z100").Formula = "=rand()"
End Sub
こういうのを徐々に表示したいってなら、無理だ

2022/05/23(月) 21:43:20.23

Do Eventsってイマイチちゃんと意味を理解しない状態で使ってる気がする

なんか図形でアニメみたいな動きさせるとか
入れると安定したり

2022/05/23(月) 23:30:03.20

>>189
WindowsというOSの(ほぼ)すべてがイベントで動いてる
キー入力も画面表示もディスクアクセスも、何をするにもイベントが必ず使われる

普通に使ってるだけで、ユーザーの関知してないところで毎秒数十万回以上のイベントが発生してて、その一部は溜めたり無視することもできる
そこで溜まったイベントをまとめて処理するのがDoEvents

2022/05/23(月) 23:44:03.30

肥溜めの水で流すボタンだな

2022/05/23(月) 23:54:22.46

>>190
中途半端な知識の人の回答だな
所詮はExcel使いか

2022/05/24(火) 00:04:30.71

そうか？

2022/05/24(火) 01:52:28.78

ググって読んでもさっぱりわからんがプロシージャの共通化(´・ω・`)これかも。

2022/05/24(火) 02:50:59.45

>>190
ExcelのVBAがWindowsの全てのイベントを処理しているとでも思ってんの？
いつの時代のWindowsだよ

2022/05/24(火) 06:09:04.44

>>195
話せ。

2022/05/24(火) 06:13:25.21

タスク

2022/05/24(火) 13:00:25.03

60歳70歳の古参プログラマーじゃないんだからさ

2022/05/24(火) 21:38:10.53

Loop中など他からの割り込みの余地入れる＝DoEventsかと思ってました
厳密な意味は知らずに使ってました

2022/05/24(火) 22:07:09.92

昔はWindowsのイベントループそのものだったけど、今は>>199くらいの認識の方が正しいよ
今のExcelは裏でスレッド使いまくってるから複雑

2022/05/24(火) 22:24:18.97

少しまだ難しいですが、とりあえずはwindowsの処理で無視していた分を一旦返してあげるみたいな処理なのだと認識しました。

2022/05/24(火) 22:30:40.02

昔はシングルコアで激重だったから適度に返さないとすぐに応答なしになった
今はマルチタスクで割と重い処理も平気

2022/05/25(水) 05:02:47.05

個人用マクロブックのプロシージャからシートに挿入されているマクロのボタンを押す方法を教えて下さい

2022/05/25(水) 06:07:16.42

privateを削除するんじゃなくて?
物理的にクリック?

2022/05/25(水) 06:43:21.13

物理的にではなくシート側のボタンのクリックイベントのプロシージャを個人用マクロブックのプロシージャから動かしたいです。

2022/05/25(水) 06:45:16.14

application.runは駄目でした

2022/05/25(水) 06:47:11.61

イベントとか言うから初心者が勘違いする

2022/05/25(水) 07:53:58.12

プロシージャの中身を外出ししようとか考えたのか？

2022/05/25(水) 08:31:27.12

>>203
Call

**デフォルトの名無しさん** (ｽﾌｯ Sdba-ry9z) · 2022/05/25(水) 09:47:51.39

いやここはレイズだ

2022/05/25(水) 18:45:23.11

>>206
呼び出し先のブック名は入れてるのかもしれないけどモジュールが複数あってモジュール名は入れてない、とか？

2022/05/25(水) 19:54:25.66

説明が少なくて申し訳ありません。
マクロボタンのついた会社の帳票のマクロボタンを押して処理をした上に自分の個人用マクロブックの処理がしたいです。私は会社の帳票のマクロを変更する技術も権限もないのでこのような形を取っています

2022/05/25(水) 20:09:39.86

やっちゃいけないことじゃねーの

**デフォルトの名無しさん** (ｽﾌｯ Sdba-ry9z) · 2022/05/25(水) 20:44:02.25

会社のマクロにパスワードが掛かってる、かつ起点となるマクロはその会社の方のマクロって事かな？

1. 会社のExcelと個人で用意したxlsm（個人用ブックである必要無し)を準備する
2. vbs やps1の外部ファイルから会社のVBA実行→個人で用意したxlsm内のVBA実行

これで行けるんでねえの？

2022/05/25(水) 20:52:50.14

変更する技術がないなら触らない方が良いと思うよ

2022/05/25(水) 21:08:25.40

そもそも権限のないものを突破しようってのは倫理的にどうなのって気がするが
まずは上司なりなんなりに相談するところからじゃね

2022/05/25(水) 21:14:00.59

ややこことは辞めたほうがいいね

2022/05/25(水) 22:29:41.62

パスワードかかってるマクロなんて適当な野良アプリでわかるけどな

2022/05/26(木) 00:34:12.72

会社用のマクロ実行したあと、個人用マクロを実行するだけの作業なのに
あくまでも自分の分からない領域まで手を入れたいってのは分からんな
マクロ壊して分かりづらい変なトラブルになったら責任云々とかになりかねないかもしれんぞ

214のやり方がむりなら諦めるか上長に相談するべきやと思うで

2022/05/26(木) 06:53:49.33

>>219
100個ぐらいあるエクセルファイルを開いて、そのエクセルファイルのマクロボタンを押して一回目の処理をして次に個人用マクロブックに作った処理をしてエクセルファイルを上書き保存して閉じるを繰り返さなくてはいけないのでマクロ化したいのです。エクセルファイル側のプロシージャを個人用マクロブック側に持たせるのが一番簡単かなと

2022/05/26(木) 06:58:26.73

その会社の帳票マクロとやらのファイルを開いてボタン押せるんならSendKeys使えば何とかなるんじゃねーの？(鼻ホジ)

2022/05/26(木) 07:56:10.65

>>220
工夫するのは上長に100個もある無駄なExcelファイルについて改善を提案すべきじゃね
下手なやつの自動化は害にしかならない
それでだめなら自分の責任でこういうやり方していいかとか聞くとかそういう流れにはもうしたんやろ？

外部から呼ぶ214のやり方で無理なら諦めろ
改善して見込める君の給与メリットがそれほどの額じゃないんやろ

これ公務員とかならマジで税金の無駄やなw

2022/05/26(木) 08:40:43.23

公務員なんて文系の仕事だから、効率のいいデータ整理やプログラミングができるやつなんてほぼいない
しかし理系の人材に役所仕事させるのは税金の浪費以上にもったいない

2022/05/26(木) 14:50:09.50

>>206
wshでしょっちゅうやってるけど

Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
CurrentDirectory = fso.getParentFolderName(WScript.ScriptFullName)
Set ExApp = Wscript.CreateObject("Excel.Application")
ExApp.Visible = True 'エクセル可視/不可視設定
ExApp.DisplayAlerts = False '警告メッセージをオフ
Set Wb = ExApp.Workbooks.Open(fso.BuildPath(CurrentDirectory,"Book1.xlsm"))
ExApp.Application.Run "Macro1"

2022/05/26(木) 15:48:41.66

上司に黙ってデータ改竄とかしたら
会社の資産を横領できちまうな

2022/05/26(木) 18:34:16.06

農協で30年勤めたOLがやりそうなイメージ。

2022/05/26(木) 18:36:23.42

郵便局の場合、昭和のころから歴代の局長が引き継いでそう。
漁協の場合、いくつもの窃盗グループがありそう。
悪いこととは知らなかったって言いそう。

**デフォルトの名無しさん** (HK 0Hca-ib4B) · 2022/05/26(木) 22:05:05.56

今日いちにち会社で配列に関する記事を読んでましたがここ10年くらいVBAに触れてなかったせいもあり
よく理解できないためお力をお借りしたいです。

20万行くらいある空白の無いデータを処理したいのですが、私の書いたコードではどうしても速く処理できません。
F列が「Distribution (PCW; Sales value」とあったらその右のG列の数字を見に行き、3以下ならその行含め下に2行、合計3行削除したいです。

Sub TEST1()
Application.ScreenUpdating = False

Dim A
'セルの値を配列に格納
A = ActiveSheet.Range("A1").CurrentRegion

'配列をループ
For i = UBound(A, 1) To 2 Step -1

'「If」で検索
If A(i, 6) = "Distribution (PCW; Sales value)" Then
If A(i, 7) <= 3 Then
Cells(i, 1).Resize(3, 1).EntireRow.Delete Shift:=xlUp
End If
End If
Next

End Sub

**デフォルトの名無しさん** (HK 0Hca-ib4B) · 2022/05/26(木) 22:19:00.22

一応こんな感じのデータです

A列　　　　　B列　　　　C列　　　　　D列　　　　　　　E列　　　　　　　　　F列　　　　　　　　　　　　　　　　　　G列　　　　　　　
CTM_ 　　　12Area TM_2022 sku 　　　SKU Code Measure 　　　　　　　　　　　　　　TOTAL 4.18- 4.25- 5.2- 5.9-
業態合算 Hokkaido その他ｷﾖｳﾘﾂｾﾞﾘ-　　4901325124326 Distribution (PCW; Sales value) 8.64 7.03 6.63 9.19 11.70
業態合算 Hokkaido その他ｷﾖｳﾘﾂｾﾞﾘ-　　4901325124326　　Sales Store**** 2,386. 1,830 3,254. 2,244 2,460.5
業態合算 Hokkaido その他ｷﾖｳﾘﾂｾﾞﾘ-　　4901325124326 Sales Store***** 666 7777 888 4444 7777

**デフォルトの名無しさん** (HK 0Hca-ib4B) · 2022/05/26(木) 22:19:48.67

やっぱりずれた。。。(>_<)

2022/05/26(木) 22:28:17.29

>>228
Excelの行を1行1行Deleteするのはどうやっても重くなるから出力用の配列を作るのがいいかな

**デフォルトの名無しさん** (ｽﾌｯ Sdba-ry9z) · 2022/05/26(木) 22:30:55.09

配列を使ってんなら配列の中で削除処理しないとそりゃあ意味無いでしょ

**デフォルトの名無しさん** (HK 0Hca-ib4B) · 2022/05/26(木) 22:46:06.64

>>231
>>232

ごもっとです、、(;^_^A
今日は疲れたので明日ちょっと調べて試行錯誤してみます。レスいただきありがとうございました。

2022/05/26(木) 23:00:25.66

いつもやる方法があるけどどのくらい早いのかはよくわからん

**デフォルトの名無しさん** (ｽﾌｯ Sdba-ry9z) · 2022/05/26(木) 23:01:04.69

ユニークなキー列がもしあればディクショナリー使うのが速いよ
まあ難しければ配列でも全然良いけど

**デフォルトの名無しさん** (HK 0Hca-ib4B) · 2022/05/26(木) 23:26:48.34

>>234
少なくとも私のコードよりは速いんじゃないかな。。
15万行を10分以上かかってます(;^_^A

>>235
ディクショナリーとは初めて聞きました。でもユニークなキーがありませんでした。。
SKU Codeという商品コードは基本ユニークなんですが3行ずつ同じ商品コードが並んでるんです。。(;^_^A

2022/05/26(木) 23:42:29.81

>>236
じゃあこんな感じで作ったので10分以上がどのくらい早くなったか遅くなったか教えてください。

Sub TEST1()
Application.ScreenUpdating = False

Dim A
'セルの値を配列に格納
A = ActiveSheet.Range("A1").CurrentRegion

'配列をループ
For i = UBound(A, 1) To 2 Step -1
Debug.Print i

'「If」で検索
If A(i, 6) = "Distribution (PCW; Sales value)" Then
If A(i, 7) <= 3 Then
Cells(i, 6).Resize(3, 1).Interior.Color = RGB(255, 0, 0)

End If
End If
Next
'
ActiveSheet.Range("A1").CurrentRegion.Select
Selection.AutoFilter Field:=6, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Application.ScreenUpdating = True
Selection.Delete 'これはしないで目で確認して手動でDELETEしても良いかも

End Sub

2022/05/26(木) 23:50:53.02

>>228
・最初にセルの内容をvariantの2次元配列に格納。

・そのvariantを弄くり倒す。

・ほしい結果になったvariantを最後に書き戻す。

セルはフォントだの背景色だの罫線だの表上のアドレスだのとにかくいろんな情報がてんこ盛りなので、valueみてりゃいい処理ならできるだけ書き換えない。

2022/05/27(金) 00:18:49.05

あと、蛇足なんだけどソースでやってることそのものをコメントで書いてもあまり意味が…。

'配列をループ
For i = UBound(A, 1) To 2 Step -1

とか

'「If」で検索
If A(i, 6) = "Distribution (PCW; Sales value)" Then

は、いらないのではと。

もひとつ、削除系処理は下からループさせたほうが無難な気がしないでもない。

2022/05/27(金) 00:19:25.67

あ。ループは俺の読み間違いだ。ごめんなさない。

2022/05/27(金) 00:22:08.18

>>235
Dictionary使うならユニークなキーがなくても行番号をキーに突っ込めば大体応用効くよね
アイテムはArrayで格納すればいいし