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/11/21(木) 15:35:37.98

http://officetanaka.net/excel/vba/tips/tips38.htm

Sub Sample1()
Dim RE, strPattern As String, r As Range
Set RE = CreateObject("VBScript.RegExp")
strPattern = "SUM\("
With RE
.Pattern = strPattern ''検索パターンを設定
.IgnoreCase = True ''大文字と小文字を区別しない
.Global = True ''文字列全体を検索
For Each r In ActiveSheet.UsedRange
If .Test(r.Formula) Then r.Interior.ColorIndex = 3
Next r
End With
Set RE = Nothing
End Sub
このソースのDim REの部分ってこのサブルーチンだけを動かすようなマクロの場合いらなくね？

2019/11/21(木) 15:37:08.52

>>764
間違えたdimでRE変数宣言するのいらなくない？
それとは別の話でset RE nothingはこのサブルーチンしか動かさない場合は必要なくない？

2019/11/21(木) 16:45:06.69

>>765
変数そのものがなくても良いって話なのか、宣言がいらないって話なのか？
まあどっちもやりようによってイエスだが、それが推奨されるかは別の話

2019/11/21(木) 17:16:22.54

>>766
プログラム的に何か考慮してるのか聞きたい
プロジェクトでどうするべきか見たいなところは興味ない

2019/11/21(木) 17:22:58.39

vbaの設計的にはSet RE = CreateObject("VBScript.RegExp") これだけで初期化できてるからdimで宣言する必要があるのかコンピューターサイエンスサイドの意味で聞きたい

2019/11/21(木) 23:25:18.32

普通は変数宣言を強制するようになってるだろ。

2019/11/22(金) 00:36:07.32

スモリートインハゲラクター

2019/11/22(金) 01:53:36.59

>>768
モジュールレベルで変数宣言を強制している場合にはプロシージャ内のDimで宣言していないとコンパイルエラーになる
型指定なしのDim宣言ってのは実際にはVariant型変数の宣言なので、代入時に型評価をしているだけだけどね
RegExp型のメモリ領域をDim宣言時に確保するとなると参照設定が必要でそれはそれでまた別の問題が生じる

2019/11/22(金) 11:54:02.74

クラスモジュールを使ってオブジェクト指向(厳密には違うらしいですが…)を理解したいと思っています
Newでオブジェクト生成して、get set letを使いながらプログラムを書くという認識で合っているでしょうか？

2019/11/22(金) 12:01:14.10

>>771
モジュールレベルで変数宣言を強制している場合には必要なのね、ありがとう良くわかった。
set RE = nothingなんだけどこのサブルーチン実行完了したらメモリとか解放されるからnothingやらなくてもスタック領域占有しないと思ってるんだけど実際のところどうなの？そもそも"VBScript.RegExp"の占有ってスタック領域であってる？
そもそもVBAのこの

2019/11/22(金) 12:14:16.35

発達障害か何かか？

2019/11/22(金) 12:16:14.12

>>774
教えてよ知ってるなら質問してたからこれも

2019/11/22(金) 12:19:40.92

実際に正常終了する前提ならset RE = nothingしなくてもメモリリーク起きないのか知りたい

2019/11/22(金) 12:25:56.88

自分で検証して納得しなさいよ

2019/11/22(金) 12:44:22.42

>>777
やるけど"VBScript.RegExp"の占有領域はスタックかどうかだけ教えて欲しい
スタックメモリだと思ってるんだけど

2019/11/22(金) 12:55:31.14

>>774
相手するなよ…

2019/11/22(金) 13:00:58.08

>>779
いやここVBA質問スレだよね？
その姿勢は本分を果たしてなくないか

2019/11/22(金) 13:03:14.92

アインシュタイン「6歳の子供に説明できなければ、理解したとは言えない。」

2019/11/22(金) 15:54:34.05

6歳って小1とかだよな？
小1が5chか・・・

2019/11/22(金) 16:07:33.42

名探偵かよ

2019/11/22(金) 16:59:48.42

>>782
小1がやってもいいだろ
例えだよね？

2019/11/22(金) 17:00:31.99

お前ら答えられないからって逃げすぎw

2019/11/22(金) 18:58:21.55

CreateObject("VBScript.RegExp")

VBScript のオブジェクトを作っているのだろ。
別のプロセスか、DLL から作っているのだろ

とても、スタックとは思えない

2019/11/22(金) 19:08:12.45

そもそも占有領域とはなにを指しているのか
VBAのローカル変数はスタックにとられる
それ以上はそのオブジェクト次第

2019/11/23(土) 09:54:08.90

>>773
VBScriptの正規表現オブジェクトはIE付属のエンジンだからヒープにインスタンス作成してるだろうし、参照カウンタ0になったら自動破棄されてるんじゃないの
このサブルーチンからの参照がなくなっても別の参照が発生してたらインスタンスは残るでしょ

だから、このサブルーチンが正常終了するかどうかとメモリリークが起きるかどうかは別問題だと思う
サブルーチン外に制御が渡った段階でこの正規表現オブジェクトがどこかのプロセスによって参照されていて、そっちの制御フローの中に猛烈なメモリ負荷が発生する処理が存在すれば、メモリリークの原因になりうる
execループで空文字をマッチさせていてindexが進まずに無限ループしたりとかね

それと、あなたの認識通り、End Subでスタック内のローカル変数のアドレス空間自体が解放されるので、End Subの直前にこの変数SEにNothingを代入する理由はない
モジュールレベルの変数やグローバル変数を使う場合は逆に、制御フロー上の要求としてサブルーチン終了時に参照アドレスをクリアしなければならない場合もある

2019/11/23(土) 10:41:17.70

Windows10で、SetLayeredWindowAttributesのLWA_COLORKEYを正常動作させる方法ある?
Windows7でやる方法は知ってるので、7の場合の回答は不要。

2019/11/23(土) 13:40:54.04

with cells(i,j)
.cut .offset(1,0)
.value=k
end with

cells(i,j)にkが入らず、cells(i+1,j)にkが入るんだけど仕様？

2019/11/23(土) 13:47:27.73

>>790
普通に

Cells(i, j).Value = k

では駄目なのか？

2019/11/23(土) 14:19:57.78

>>790
cut貼り付けでセルそのものが移動してるから

2019/11/23(土) 14:44:54.38

ストリートインハゲラクターはここに来ないの？

**デフォルトの名無しさん** (ｽﾌｯ Sd33-cCMV) · 2019/11/23(土) 17:04:16.03

>>791
>>792
仕様なんですね
ありがとうございます

2019/11/23(土) 17:11:07.86

仕様というか、代入先を自分で変えてることは伝わってるんだろうか

2019/11/23(土) 19:56:27.67

>>790
仕様ではなくて君のコードの動作の問題
同じ綴りのオブジェクト式の記述をまとめる文法ルールとしてWithを理解するのは大きな間違い

Withで任意のセルを取得して和のセルに貼り付けたらEnd Withまでずっと貼り付けられた先のセルを参照する
その状態で.Valueに値を代入したら、当然貼り付けられたセルの値が変わる

With Cells(i, j)
.Cut .Offset(1,0)
End With
Cells(i, j).Value = k

とすれば何の問題もない

2019/11/23(土) 20:39:38.41

>>795
わかってて聞いてるんでしょ
わかってなきゃわざわざ「仕様？」なんて聞き方しないだろうし

With で参照してるセルを変更した時の挙動を書いてるドキュメントは見たことないから仕様かどうかはよくわからん
誰か見たことある人いる？

2019/11/23(土) 22:14:08.35

アンカー間違えてた
>>796は>>794宛ね

2019/11/23(土) 22:28:15.56

>>790
仕様
cut後もcells()で返ってくるオブジェクトは同じ
そういや上書きされるcells()はどうなるのか、と試してみたらnothingになっていた
この辺はExcelのカットアンドペーストなどでおなじみだけど、いざやってみると不思議な気分だ

Sub foo()
i = 2
j = 3
k = "k-"
Set before = Cells(3, 3)
With Cells(i, j)
.Cut .Offset(1, 0)
.Value = k
End With
Debug.Print before.Value
End Sub

2019/11/23(土) 22:52:50.40

>>799
不思議でも何でもない
変数によるオブジェクト参照の仕組みをもっと正確に理解すべきだろ
カットは貼り付け先のセルの削除と貼り付け元のセルの埋め込みを伴う処理だぞ
貼り付けを行った段階で、変数のスタックに格納されていた貼り付け先のセルのポインタが無効になるので、貼り付け後に変数の中身を評価するとNothingになるだけ
セルの仕様ではなくオブジェクト参照の仕組みからして当然

2019/11/24(日) 00:00:31.22

しかし、VBAをやり始めたら仕事がはかどるな
面倒くさい勤務表の処理もマクロで一発やからな

2019/11/24(日) 02:13:33.02

まるで働いているみたいな言い方だな

2019/11/24(日) 02:19:56.28

VBAで劇的に工数削減できるような単価の低そうな仕事をしているのなら、
今の仕事の効率化なんかよりプログラミングに習熟してITエンジニアに転職したほうが金を稼げるのではないだろうか

2019/11/24(日) 02:30:35.85

>>803
そのやりかたは？
フリーランサーになればたくさん稼げる？
在宅ワークも可？

2019/11/24(日) 11:11:52.55

>>804
いかがでしたでしょうか？

2019/11/24(日) 11:24:51.53

>>803>>804
ここで回答するのもアレだが
VBAは基本的に常駐で保守のゴツイ仕事のみ。仕事自体が少なく稼げない。稼ぐなら別のプログラム言語の方が良い。
在宅の案件は更に無い。ゼロと思ったほうが良い（web業者がついでにAccessのVBAをメンテする、といった事例は聞いたことがある）

2019/11/24(日) 11:58:45.04

VBAはマクロ記録も出来るし、やりたいことをちょいググればいくらでもサンプルコード載ってるからね

2019/11/24(日) 13:15:25.69

Excel VBA限定の話だろそれ
Outlook とかWordとかPPTのVBAだと途端に情報集めの難易度が上がる
Access VBAは衰退傾向だし

2019/11/24(日) 15:48:03.50

>>789
動作しなくなったの?
あらためて試して無いから分からんけど。

2019/11/24(日) 15:54:08.01

>>807
そういうレベルの話してるから安い話になるんじゃないの?

ググってすぐどうにかなることなんて殆ど無い。
というかググって答えが見つからないことは多い。

2019/11/24(日) 16:27:31.40

検索力の問題

2019/11/24(日) 17:31:24.17

>>811
そんなレベルの低い話はしてないんだがw

2019/11/24(日) 17:34:20.62

困ったらググれ

2019/11/24(日) 18:56:22.86

>>789
というか、それってExcelそのものに対して透明化したいって話?
それは某掲示板で話題になってWindows10から出来ないみたいな話を聞いたような。

2019/11/24(日) 18:58:17.94

>>809
正常動作しなくなったのは7の時点だけど、その時はAeroを切ってどうにか切り抜けた。
しかし10はどうしたものかなと。
LWA_ALPHAの方は全く問題ないんだけど。
複数のPCで確認したから、おま環ではないと思う。

2019/11/24(日) 19:01:21.70

>>814
透明化したいのはExcelそのもの。
結構便利なんだよ。
ウインドウを2枚重ねても、奥側のウインドウを操作できるから。

2019/11/24(日) 20:13:58.03

使えなくなったのか
大昔ハマって透明アプリばっかり作ってた記憶

2019/11/24(日) 21:06:06.38

>>817
使えないっていうか、変な動作をするんだよ。
ThisWorkbook.Application.Hwndでハンドル取ってるのに、
なぜか最初に配置したコマンドボタンのキャプションだけが透過するとか。

2019/11/24(日) 21:33:06.21

>>816
透過はするけど透明にならないんだろ。
無理矢理やる方法は思い付いたけど試して無い。

2019/11/24(日) 21:55:17.62

ストリートインハゲラクター

2019/11/25(月) 01:13:03.20

透過するアプリがあったなあ昔、なんだっけ。

2019/11/25(月) 03:00:00.82

透明じゃ見えないよ

2019/11/25(月) 08:39:02.81

ググったらサンプルコードが見つかるんじゃなかったのか?

2019/11/25(月) 11:42:30.29

初歩的な質問ですみません
標準モジュールとユーザーフォームでプログラムを書いています
1つのモジュール内にプロシージャが沢山あり、読みにくくなってしまいました
本を読むとクラスモジュールでプログラムが読みやすくなると書いてありました
どのような時にクラスモジュールを使ったらプログラムは読みやすく出来ますか？
サブルーチンやForNext,if等の繰り返し使うものはクラスモジュールを使った方がいいでしょうか？

2019/11/25(月) 11:52:41.48

>>824
そんなことをここで聞くような人には無理

2019/11/25(月) 14:15:33.91

>>825
わかりました
もっと勉強してきます
失礼しました

**デフォルトの名無しさん** (ｽﾌｯ Sd33-mWXg) · 2019/11/25(月) 14:41:38.68

ぶしつけで恐縮ですが、シート内の改行を一気に削除する方法を教えていただけないでしょうか。

cellsで指定してreplaceメソッドってやってもできないので、誰かよろしくお願い申し上げます。

2019/11/25(月) 15:12:03.96

新しいシート作って改行をｌ削除したデータをコピーすればいいんじゃないかな

2019/11/25(月) 15:16:08.90

Dim R As Range
For Each R In ActiveSheet.UsedRange
R = Replace(R, vbCr, "")
R = Replace(R, vbLf, "")
Next

あるいは

Cells.Replace What:=vbCr, Replacement:=""
Cells.Replace What:=vbLf, Replacement:=""

2019/11/25(月) 15:30:30.80

>>827
VBAスレなんで余談だけど
VBAでやるんなら>>829
手動なら検索窓に「ctrl+J」を入れて全て置換って方法もある

2019/11/25(月) 15:42:49.49

いちおう説明
excel内の改行コードはLFが基本らしいんだけど
マクロ使ったりでCRLFも入力出来るらしい
なのでCRとLFで分けて処理させてみた

2019/11/25(月) 16:52:28.97

結局>>789はどうにもならんのか?

2019/11/25(月) 17:03:29.94

>>824
クラスモジュールというのはユーザーが独自にメソッドやプロパティ、イベントを定義して使えるオブジェクトのことで、本質的にはユーザーフォームと変わらない設計とコーディングに結構手間がかかる
オブジェクト化によるカプセル化がどうしても必要なら試してもいいが、肥大化したモジュールのコードの可読性改善くらいの目的であればやらない方がいいかもしれない
クラス内に定義された関数を呼び出すにも、そのクラスの型をもつ変数を宣言してインスタンスを生成してメソッドを呼び出なければならなくなるのでね

モジュールがごちゃごちゃして嫌なら、モジュールに記載している関数を機能カテゴリに分類して、各機能カテゴリごとモジュールを分ければいいんじゃないのかな
文字列操作系、メール送信機能系、メッセージダイアログ系、みたいな感じの切り分けで

2019/11/25(月) 18:09:55.21

>>833
ありがとうございます
おっしゃる通りで今問題なく動くプログラムをわざわざ変える必要は無いですね
まずはモジュールを分けるところから始めてみます
今後プログラムを作る際の参考にさせていただきます

2019/11/25(月) 19:07:37.29

グラサンのハゲはここにこないの？

2019/11/25(月) 19:23:35.97

シートやセルと変数の定義が重複するようなときに一カ所まとめられて便利

2019/11/26(火) 07:21:06.34

>>788
ヒープエリアに展開されるのはその通りだけど
VBAにガベージコレクションの概念は無いよ

2019/11/26(火) 08:19:11.49

>>837
なにをガベージコレクションと言ってるのかわからんけど、VBAはCOM扱ってるから参照カウンタは使ってるよ
まあCOMをVBAの機能と言うかについてはいろんな意見があるだろうけど

2019/11/26(火) 18:13:05.92

お前らの部屋もガーベージだらけ

2019/11/26(火) 19:01:16.26

ちゃんとコレクションしてるわ

2019/11/26(火) 19:11:03.72

ほこり被ってるけどな

2019/11/26(火) 20:19:14.53

>>837
参照追跡方式の本当のガベコレは利用できないけどCOM標準の参照カウンタ方式のリソース管理システムはバックグラウンドで使ってるだろ
循環参照が発生してるとリソース解放できないやつね

2019/11/26(火) 20:33:43.32

A列の値が「ア行」、B列の値が「あいうえお」のうちどれかであればC列を赤く塗り、
同じく「カ行」で「かきくけこ」のうちどれかであれば青く塗る、というような処理をしたくて
以下のようにしたところ一応うまくいったのですが、
「ア行 and あ」or「ア行 and い」or ……
というような複数条件の書き方はこれであっているのでしょうか？

If InStr(Range("A2").Value, "ア行") > 0 And InStr(Range("B2").Value, "あ") > 0
Or InStr(Range("B2").Value, "い") > 0 Or InStr(Range("B2").Value, "う") > 0
Or InStr(Range("B2").Value, "え") > 0 Or InStr(Range("B2").Value, "お") > 0 Then
Range("C2").Interior.Color = RGB(255, 0, 0)

ElseIf InStr(Range("A2").Value, "カ行") > 0 And InStr(Range("B2").Value, "か") > 0
Or InStr(Range("B2").Value, "き") > 0 Or InStr(Range("B2").Value, "く") > 0
Or InStr(Range("B2").Value, "け") > 0 Or InStr(Range("B2").Value, "こ") > 0 Then
Range("C2").Interior.Color = RGB(0, 0, 255)

2019/11/26(火) 20:46:08.04

グラサンのハゲはここにこないのー？

2019/11/26(火) 21:20:11.71

>>843
間違ってる
AndとORは左側からの計算になるんで
false and false or true …がtrueになる
Or の連続部分を括弧で囲まないといけない

2019/11/26(火) 21:29:30.19

>>843
> 「ア行 and あ」or「ア行 and い」or ……
> というような複数条件の書き方はこれであっているのでしょうか？
ダメ
AndよりOrの方が優先順位が低いので
InStr(Range("A2").Value, "ア行") > 0
And InStr(Range("B2").Value, "あ") > 0
Or InStr(Range("B2").Value, "い") > 0
Or InStr(Range("B2").Value, "う") > 0
Or InStr(Range("B2").Value, "え") > 0
Or InStr(Range("B2").Value, "お") > 0
は
(InStr(Range("A2").Value, "ア行") > 0 And InStr(Range("B2").Value, "あ") > 0)
Or InStr(Range("B2").Value, "い") > 0
Or InStr(Range("B2").Value, "う") > 0
Or InStr(Range("B2").Value, "え") > 0
Or InStr(Range("B2").Value, "お") > 0
のように解釈される
なのでA2が "ア行" でなくてもB2が "い" だと成立しちゃう
詳しくは
https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/operator-precedence
を見て

2019/11/26(火) 21:38:29.33

>>843
そもそも
> A列の値が「ア行」
と言うなら
Range("A2").Value = "ア行"
と書けばいい
InStr(Range("A2").Value, "ア行") > 0
だとA2が "マレーシア行きのバスはここから出ます" でも成立しちゃうけどそれはいいのか？

2019/11/26(火) 21:41:08.57

あらそうだったんですね。ということは

InStr(Range("A2").Value, "ア行") > 0
And (InStr(Range("B2").Value, "あ") > 0 Or InStr(Range("B2").Value, "お") > 0) Then

という感じにすればいいんでしょうか？

2019/11/26(火) 21:43:24.14

>>845の左順って間違いだったは
変更はそれでOK

2019/11/26(火) 21:46:05.70

ああなるほど。文言は固定でかぶってる言葉がなかったので大丈夫だったんだと思います
すでにあった文言の組み合わせの違うVBAをいじったもんですからそれを大枠はそれをそのまま
使ったんですけどそう言われればそうですね

2019/11/26(火) 21:50:37.69

If Range("A2").Value = "ア行"
And (InStr(Range("B2").Value, "あ") > 0 Or InStr(Range("B2").Value, "お") > 0) Then
Elseif Range("A2").Value = "カ行"
And (InStr(Range("B2").Value, "か") > 0 Or InStr(Range("B2").Value, "こ") > 0) Then

こうでしょうか？

2019/11/26(火) 21:55:48.15

もいちょい頑張って正規表現使ったらいいんじゃないでしょうか

2019/11/26(火) 22:05:12.70

正規表現だと
If Range("A2").Value = "ア行"
And (Range("B2").Value Like "[あ, い, う, え, お]" Then
これであってますか？

2019/11/26(火) 22:24:52.56

>>853
厳密には"[あ-お]"または"[あいうえお]"
それだと,でも反応する

2019/11/26(火) 22:25:20.59

正規表現はRegExp使う奴だよ

2019/11/26(火) 22:34:52.14

簡略化するためにあいうえおを使いましたが、実際は2～10文字程度の文字列なので、
And (Range("B2").Value Like "東京|大阪|名古屋" Then
こうすればいいのかな？

2019/11/26(火) 22:41:27.30

>>856
| はRegExp使わないとムリだね

2019/11/26(火) 22:46:43.63

確かにただ　|　を使っただけでは反応しませんね
とりあえずOrいっぱい使って直します、ありがとうございました

2019/11/26(火) 22:50:11.95

TestメソッドがTrueを返してきたら処理するって形にすればいい

2019/11/26(火) 22:55:07.89

>>856
いちいち聞く前に試した方が早いよ

2019/11/27(水) 06:59:40.83

質問スレなんだから構わないだろ
気に入らないなら無視しとけアホ

2019/11/27(水) 15:37:51.09

他人が作ったマクロなんて読みづらいに決まってんじゃん…
今更グダグダ言ってもあとの祭りさ。

2019/11/27(水) 15:57:59.39

自分が作ったマクロもだぞ
数年前どころか一ヶ月もするとわからなくて四苦八苦することがある

2019/11/27(水) 16:57:52.22

過去のソースは別人が書いたと思って