Excel VBA 質問スレ Part74

2021/12/20(月) 01:40:59.22

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑同じ内容を2行貼り付けるナリ

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

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

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

2021/12/20(月) 21:37:30.62

>>2
というような、荒らしが出没しています
質問者は気軽に質問してok

2021/12/20(月) 22:50:29.00

>>3
とRubyキチガイこといつものあいつが書いてます

2021/12/21(火) 00:54:48.99

本当にそう
Excelをろくに使えないヤツは質問禁止

2021/12/21(火) 02:17:40.60

俺が超初心者の質問に答えるのを誰も止められない

2021/12/21(火) 07:05:08.85

誰かやつを止めろー

2021/12/21(火) 08:50:24.71

ここで回答者のメンバーを紹介しとこう！！
丸投げの竜！
宿題のケン！
エスパー塚本！
後出しの抜か八！

2021/12/21(火) 14:03:53.42

Excelで値のVLOOKUPが簡単にできないのはどう考えてもおかしい

2021/12/21(火) 19:59:23.56

set phoneticが遅いんですが何か良い方法ありませんか・・・

2021/12/21(火) 20:21:57.86

シoア「使わなければどうということはない」

2021/12/21(火) 21:27:31.69

帰る前か寝る前に走らせておけば翌日には終わってる

2021/12/21(火) 21:31:17.19

奴隷かな

2021/12/22(水) 01:37:54.14

ここってExcel質問板の親戚なのでつか？

**デフォルトの名無しさん** (ｽﾌｯ Sd8a-kSWT) · 2021/12/22(水) 07:24:45.48

そんなやつは知らん
赤の他人

2021/12/22(水) 17:49:42.98

互いにディスっている噂はマジもんだったのね
あっちは崩壊してる

2021/12/22(水) 18:18:46.40

Excelの機能だけを使うかVBAで書くかは時と場合によるだろ
どちらかというとVBAの方が少しだけ得意とか少しだけ知ってるって人が回答側に回ってるだけでExcelの機能を蔑ろにはせんよ
俺なんか未だにピボットテーブルとか苦手だから使える人って凄いなって常々思ってる

2021/12/22(水) 20:20:19.34

SUMIF関数こねくりまわすよりピボットテーブルの方が頭使わない

2021/12/22(水) 21:22:09.96

>>17
回答者としての素直な感想だけど、
「ピボットテーブル分からないとかMOS取ってないじゃん。それぐらいとっとけよ」だ
MOSエキスパートなんて一週間あれば取れる
絶対にとっておいてほしい

2021/12/22(水) 21:34:22.82

パワハラ野郎が一週間っつってるからだいたい一年か
むずかしそうだな

2021/12/22(水) 23:34:45.09

>>19
MOSなど取らなくてもピボットテーブルくらいは使えるけど、一週間でとれるMOSをわざわざ取る意味はありますか？

**デフォルトの名無しさん** (ｽﾌｯ Sd8a-kSWT) · 2021/12/23(木) 00:13:10.86

>>21
マウントの第一歩は資格から

2021/12/23(木) 00:13:50.78

>>21
面接する側としては、MOS取ってるとすごく楽

「x年エクセル使ってきました！」→罫線引けるだけなのか、vlookup使えるのか、vba使えるのか、全然分からん
を

「MOS取ってます」→ピボット使えるのが一発で分かる。他に聞きたい事を色々と聞ける

技術も良いけど、「何を勉強すべきか」をわかってるという点も安心材料になる

2021/12/23(木) 00:41:43.27

資格取るかどうかぐらい自分で決めろよ、迷う程度なら無くても困らんだろ

2021/12/23(木) 00:50:29.82

>>19
MOS上級取ったがピボットテーブルが何だったか覚えてないわ～
だって使わねーもん忘れるわ

関数も使ったセル数に比例して重くなるからあまり使わない

2021/12/23(木) 01:25:21.58

派遣かよ(笑)

2021/12/23(木) 05:40:54.98

>>23
MOSとやらの資格を持ってるとVBAもやってるか分かるの？
てことはMOSにVBAの問題も出るってこと？

2021/12/23(木) 06:26:23.14

うちの上さんにピボット教えたら課長に昇格したで
もともとは非正規社員だったのに

2021/12/23(木) 06:56:30.81

レベルの低い会社だな

2021/12/23(木) 08:39:33.26

鶏口牛後

2021/12/23(木) 08:45:45.04

ピボットは高速だからいいよ

2021/12/23(木) 10:02:50.98

>>27
横だけど出ないよ
人それぞれだと思うけれど正直VBAのほうが小回りが効く

2021/12/23(木) 15:44:49.15

VBAできればそんな機能やらなんとか関数なんて覚えなくていいんじゃないの？
職場に全部VBAでExcelを運用している上司がそう言っていた。
「Excelはマクロで動かせば良いんだよ」って

**デフォルトの名無しさん** (ｽﾌｯ Sd8a-kSWT) · 2021/12/23(木) 15:48:58.80

それぞれ長所と短所があんだから、適切に使い分けれないのは無能

2021/12/23(木) 17:12:00.17

>>33
クソみたいな会社だね

2021/12/23(木) 17:27:03.01

VBA＝上級者なんでしょ？

2021/12/23(木) 18:14:09.06

上級者＝人間です

2021/12/23(木) 18:37:09.81

そんなことより明日はクリスマスイブですよ

2021/12/23(木) 18:50:00.82

今日はイブイブですよ

2021/12/23(木) 19:03:15.92

上皇陛下生誕記念日ですよ

2021/12/24(金) 11:39:11.20

>>38-39
プレゼントくれ

2021/12/24(金) 13:14:14.04

どうせクリスマスイヴもクリスマスもExcelの練習してるんでしょ？
自分もだけど泣

2021/12/24(金) 13:38:02.83

Win11でIEが廃止されたんで、VBA/VBscriptでIEを操作できなくなったと思ったんですが、
最新のWin11だとIEが復活してないですか？
https://i.imgur.com/owo3i91.jpg
Win11を最新にしたら、IEが起動して操作ができるようになってる。

2021/12/24(金) 14:20:05.78

>>43
廃止されてない
最初からそうアナウンスされてたからVBA勢もとくに慌ててなかった

2021/12/24(金) 14:48:58.10

VBAの廃止はちょっと困るなぁ
VBSの廃止も少し困るけどいざとなったら
Javascriptに逃げる

2021/12/24(金) 23:15:00.50

VBAで文字列x内のn文字以降で，文字Aと文字Bの間が数字だけの部分のBの位置を知る方法があれば教えて下さい．

2021/12/24(金) 23:19:31.78

>>46

n=4

aaaergjoeriA3543Behwfhewu ←これのBの位置
bbbA3fewfew354Bffewo
cccfjewpfAf3e54f3w54Bfjeei

正規表現でA・Bの間が数字だけ、それをfindで見つける気がするけど飲みすぎて無理だー

2021/12/25(土) 00:32:33.67

>>47
酔いが覚めたらお願いします

2021/12/25(土) 00:49:36.50

>>48
ヒント貰ったのだから後は自分で考えろ

2021/12/25(土) 00:58:09.02

>>49
そこまではヒントをもらわんでも俺でもわかる。
この先がわからんから聞いているのよ。

それにお前関係ないやつだよな。
貢献もしてないのに横からでてきて文句をいうな。
マスク警察みたいなやつだね（笑）

2021/12/25(土) 01:35:14.15

>>50
馬鹿には無理ってことか
いや、Ruby基地外だろ

2021/12/25(土) 09:39:15.77

自分で自分の首を絞めるスタイル

2021/12/25(土) 09:45:38.27

逆にそこまでわかってて何がわからないの

2021/12/25(土) 10:05:17.74

検索する能力がない
応用力がない

2021/12/25(土) 10:16:27.24

向いていないからVBAやめろと言っているようなもんか

**デフォルトの名無しさん** (ｽﾌｯ Sdaf-bp1O) · 2021/12/25(土) 10:26:38.11

仕事ってのは、できるやつに頼みたいと思っても手持ちの人材をなんとかやりくりするしかないわけで
ベストは不可能だから、どうやったらベターな結果が得られるか考えるのが管理職の仕事
世の中そんなもん

2021/12/25(土) 10:40:38.77

Find はセルにしか使えない。instr関数は正規表現をサポートしてた。like 演算子は文字位置を返さない。何で行き詰まってる。

2021/12/25(土) 10:42:03.53

すまん誤字が多かった。

Find はセルにしか使えない。instr関数は正規表現をサポートしてない。like 演算子は文字位置を返さない。なんで行き詰まってる。

2021/12/25(土) 10:42:44.93

正規表現使えばいいじゃん（いいじゃん）

2021/12/25(土) 10:51:58.85

>>58
WorksheetFunction.Find

2021/12/25(土) 10:58:39.79

正規表現でA-Bの文字列を取り出して
その文字列をfindかinstrにかければAの位置がわかる
あとはA-B文字列の長さを出せばBの位置もわかる

2021/12/25(土) 11:14:22.36

飲みながら正規表現はキツイ

Sub foo()

a = "aaaergjoeriA3543Behwfhewu ←これのBの位置"
b = "bbbA3fewfew354Bffewo"
c = "cccfjewpfAf3e54f3w54Bfjeei"
n = 4 'n文字以降
str_start = "A"
str_end = "B"

'RegExpオブジェクトの作成
Dim reg As Object
Set reg = CreateObject("VBScript.RegExp")

'正規表現の指定
reg.Pattern = ".{" & n & "}" & str_start & "[0-9]*?" & str_end 'パターンを指定
reg.IgnoreCase = False '大文字と小文字を区別するか(False)、しないか(True)
Debug.Print reg.Pattern

'main1　この引数が検索対象
Set matches = reg.Execute(a)

2021/12/25(土) 11:14:27.46

'無かったら終了
If matches.Count = 0 Then
MsgBox "見つからないので終了します"
Exit Sub
End If

'main2
For Each Match In matches
文字列長 = Len(Match.Value)
発見場所 = Match.firstindex
結果 = 文字列長 + 発見場所
Debug.Print 結果 & "文字目に見つかりました"
Next Match

End Sub

2021/12/25(土) 11:44:00.82

>>62,63
おお、ありがとうございます。
これです。

RegExpでVBAでも正規表現が使えるところまでは調べたのですが、具体的な使い方がわかりませんでした。
勉強します。

このような素晴らしい解決策をご教示していただける前は下のような手作り感満載の関数でしのいでおりました。
あとから見た時にわからなくなりそうなのでもっと定式化された解決策を探しておりました。

なお必要なのは最初にマッチした位置だけでいいです。

Function patternSearch(x As String, pattern As String, start As Long)
Dim i As Long
Dim isStartEndMatching As Boolean
Dim isNum As Boolean
Dim cond_StartEnd As String '前後の文字の一致を調べる
Dim cond_Numeric As String '間が数字であることを調べる

cond_StartEnd = Left(pattern, 1) & "*" & Right(pattern, 1)
cond_Numeric = Mid(pattern, 2, Len(pattern) - 2)

For i = 1 To Len(x) - (start - 1)
isStartEndMatching = Mid(x, start, i) Like cond_StartEnd
isNum = IsNumeric(cond_Numeric)
If isStartEndMatching And isNum Then
patternSearch = start - 1 + i
Exit For
End If
Next i
patternSearch = 0
End Function

2021/12/25(土) 11:52:30.46

>>62
>>46 > VBAで文字列x内のn文字「以降」で

2021/12/25(土) 11:57:08.35

>>64
動けばなんでもok

>>65
笑

2021/12/25(土) 15:45:55.24

マクロなんて動けばおkだよね

2021/12/25(土) 16:59:38.36

>>62
美しい

2021/12/26(日) 14:03:55.68

動かなくても問題ないし

2021/12/26(日) 14:10:17.85

>>67
我流のマクロは応用が効かないからね．
正規表現を使ったバージョンだと違う検索条件のときも，検索文字列だけ修正すればよい．
私の場合は上記の目的の後，全てのセルの特定文字だけ別の文字に置換して赤字に色付する目的に応用できた．

ところで話変わるけど，スペースのフォントは色付けできないんで，別の文字に置換して色付けするしか無いけど，何が良いかな．
□が一番近いかなという気もするが．

2021/12/26(日) 14:15:53.43

スペースに色を付けられないならセルに色を付ければいいじゃない

2021/12/26(日) 14:20:38.69

アンダーバーダメ？

2021/12/26(日) 16:17:30.13

実際我流が大半なんでしょ？

2021/12/26(日) 16:43:46.10

VBAでスクール通って学んだとか聞いたことがない

2021/12/26(日) 16:46:40.63

こいこいさんのチャンネルでVBAのスクールの話が出たことはあるな

2021/12/26(日) 17:13:23.67

記録したマクロ見てれば自然に解っちゃうしな

2021/12/26(日) 17:43:53.58

>>73
そもそもプログラムには「本流」が無いので「我流」がない。
※強いて言えば、その団体（勤務先企業）のやり方はある

プログラムなんて「最終的に要望通りに動けば何でもいい」
汚いとか綺麗とかは、本質的には、無い
vbaはかなり特殊で、エクセルの補助として動くプログラム。多少動きが悪くてもエクセルのルーチンでカバーできる

ただ昨今「ルーチンが変わりやすい」という風潮がある
その前提だと、「保守のコストをいかに低減させるか」という事に価値が生じてくる
それを「エクセルのオペレーションでカバーする」「vbaを改修する」どちらで負担するかは、それを運用している会社による。場合によっては個人。

別の風潮として、保守コストを下げる際に「上の方で設定して、後のプログラム部分では変更しないようにする」って風潮がある
これは絶対ではないが、妥当ではある

それらを総合すると「保守コストを極力低減させるコードを書く。設定は上の方で行っておく」というコードに価値がある

実は、>>62はわざと綺麗に書いている。
一箇所だけ明確に失敗している部分がある

2021/12/26(日) 19:31:54.80

>>72
スペースの書式の下線をつけたのだが不思議な現象が発生。
文字列の中間の場合は表示されるが、最後の非スペース文字よりあとのスペースの下線は表示されない。
エクセルのスペースの書式は予想外の振る舞いをする。

2021/12/26(日) 20:21:51.00

>>75
こいこいガチすごい人だな

2021/12/26(日) 21:36:01.96

>>78
検索すると、2003ではスペースにも下線がついたらしい
仕様が変わったのだろうか

**デフォルトの名無しさん** (ｽﾌｯ Sdaf-bp1O) · 2021/12/26(日) 23:39:01.85

Excel2021だけど、どこにスペーズがあっても普通に下線付くぞ
別の何かとバッティングしてるだけでは？
https://i.imgur.com/gimsTIF.png

2021/12/26(日) 23:42:16.71

365使ってるときに同じこと思った気がするけど今は確認できない

2021/12/26(日) 23:43:33.04

>>81
私がやっているのは文字ごとに書式を設定すること。スペースだけに下線をつけることを試してる。

2021/12/26(日) 23:46:55.18

DatePart構文の引数firstweekofyearを1月始まりじゃなくて4月始まりする方法ってない？
4月1日から第一週目として請求書を何週目に発行したか把握したいVBA組みたいんだけど

4/1〜4/7に収まる数字を第一週
4/8〜4/15に収まる数字を第二週…
っていちいち組むしかない？

2021/12/27(月) 00:04:55.38

>>84
日付シリアル値と3月31日のシリアル値の差を7で割って整数単位でラウンドアップする方法ではだめですか

2021/12/27(月) 00:06:52.13

>>85
シリアル値は考えたけど面倒そうで…簡単に何週目かを算出したい

2021/12/27(月) 00:09:03.03

組むっていうか自分で関数作ればすぐじゃね

2021/12/27(月) 00:14:15.86

datediffでintervalをwにして
4/1とその日を比較するだけかな？
やってみないとわからんけど

2021/12/27(月) 00:15:08.58

>>86

> 面倒そうで…
？

**デフォルトの名無しさん** (ｽﾌｯ Sdaf-bp1O) · 2021/12/27(月) 00:30:35.81

>>84
年またぎの処理はしてない
やりたかったら先に3/31を引いて1/1始まりに補正するのが一番簡単だと思う

Sub Macro1()
　　Dim d As Date
　　Dim w As Integer

　　d = DateValue("12/28")　　'求めたい日
　　w = Int((d - DateValue("3/31") + 6) / 7)　　' 4/1基準で何週目か
　　Debug.Print w
End Sub

2021/12/27(月) 02:58:33.09

先輩方にアホすぎるくらい初歩的な質問です。
マクロコード書くときに字下げって常識と聞きますが必須なんすか？
YouTubeとか観ていると字下げしてない人もいたもんで気になって

2021/12/27(月) 06:49:37.64

>>91
必須ではないけどやった方が見易いよ
If hoge = piyo then
foo = bar
End If
ってしておけば後から見てもどこからどこまでがIfやForなどの中身なのかって分かりやすいじゃない？

2021/12/27(月) 07:19:24.39

>>91
慣れてくれば入れるようになるが・・・無いと見づらくて仕方ない日本語でいうと句読点も改行もないようなもんだこれでもいいならどうぞ

2021/12/27(月) 08:17:15.04

外部ファイル100個、1ファイルにつき60要素を1つのExcelブックにまとめたいけど、高速なテンプレはどっち？
①外部ファイルオープン方式
　オープンして2次元配列に格納したらすぐに閉じる（見た目一瞬開いているのが見える）

②外部参照方式にする
　’サーバーパス[ファイル名]シート名!‘表示セル
　この式をまとめたいExcelブックにひたすら書く

まとめたい要素数によって①②方式を変えるのが正解ですかね？
今の所①で作ることが多いのですが、ファイルオープンに時間がかかってるような気がしています

2021/12/27(月) 09:39:51.64

>>94
データの種類によるけど参照するのがテキストファイルなら外部参照なんて使いたくないから個人的には1だが

2021/12/27(月) 09:45:56.70

>>91
これがコンピューター業界の常識
https://img.youtube.com/vi/qNHfKNjX8Us/maxresdefault.jpg

2021/12/27(月) 09:49:52.38

関数の直後に改行入れないで中括弧入れられると解りにくい

2021/12/27(月) 10:45:38.45

>>94
実際には二つやって比較するしかないと思うんだけど、直感的には両方ともファイルの内容を一度メモリーに展開して①の場合はそれを表示するプロセスがあるんで若干①が遅いような気もするんだけど。

一方②の場合は普通にメモリーに展開するのではなくて特殊な用途用のメモリ領域の確保というプロセスがあるかもしれないんでこれがあってしかも配置情報の各能があるばあいは遅くなるかもしれない。

実際にダミーのファイルを作って比較してみたらどうですか。ダミーファイルを作る作業は VBA を使えば簡単でしょ。

2021/12/27(月) 11:13:03.57

>>94
その二つには大きな違いがある
(1)の方法はVBAを実行した瞬間のデータで固定される
(2)の方法だと外部ファイルが更新されるたびに影響を受ける
この選択を間違えると場合によっては致命的なので、どちらが適切かよく考えること
つまり、この2つは速さで比較するべきじゃない

そもそも、わずか100ファイル、60要素程度で速さがどうとか考えること自体がおかしい
データ数が何十万以上のオーダーにならないとはっきりした差なんか出ない

2021/12/27(月) 11:45:31.10

>>99
勘違いしてたわ。
②はリンクでデータを取得する方式ね。
俺はてっきりVBAの中で対象ファイルを開かずに対象ファイルのデータを直接取り組む方式と思ってたわ。
たしかに、「参照」と書いているから99さんの言うとおりだね。

2021/12/27(月) 12:05:29.87

>>83
アンダーラインの位置を順番に変えるマクロで試してみたら、たしかに行末のスペースで線が消えた
仕様なのかバグなのか判断つかんな
Excel2021
https://i.imgur.com/chvCa5D.png

2021/12/27(月) 12:07:22.46

リンクでまとめるのは俺なら速度以前にやらないな