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/06/26(日) 09:56:38.78

>>672
num=cells(行,列).value
dirname=dir(thisworkbook.path&"\"&num&"*",vbDirectory)

2022/06/26(日) 09:59:07.88

dirname=dir(thisworkbook.path & "\" & num & "*", vbDirectory)

2022/06/26(日) 10:18:26.90

>>673
PowerQueryでもAdobeのAcrobatなしで
pdfをある程度変換出来るけど？

2022/06/26(日) 10:25:43.07

>>673
adobeに聞けとしか

2022/06/26(日) 11:39:03.04

EULA読んでダメって書かれてなきゃいいだろ

2022/06/26(日) 12:11:58.60

クラスが入ってるCollectionの要素を置き換えるにはどうすればよいでしょうか？

Set col(2)=New MyClass()

とやってもできないんです。

2022/06/26(日) 12:23:08.37

置き換えはダメだからRemoveしてAdd

2022/06/26(日) 13:30:17.16

>>680
そうですか
ありがとうございます

でもなぜ置き換えできないのでしょう？
そのしくみを理解したいのですが
スキルがあがるステップだと思うので

2022/06/26(日) 13:35:51.31

IEの入力欄に値いれるマクロ使ってたんだけど、エッジに移行して動かなくなったんよ
エッジの入力欄いじる方法ってご存知ないですか？
調べてもよくわからなくて

2022/06/26(日) 13:39:54.64

VBAを投げ捨ててEdgeの拡張機能を作ろう

**デフォルトの名無しさん** (ｽﾌｯ Sd5f-tCSL) · 2022/06/26(日) 14:11:21.83

Sendkeys

2022/06/26(日) 15:05:50.62

まだInternetExplorer.Application は有効でしょ
サポートは切れたが動かなくなる訳ない

2022/06/26(日) 15:26:20.76

なぜMsedge.Applicationにしないの？

2022/06/26(日) 15:32:17.63

クラス(例:MyClass)が入ってるCollectionの要素(つまりMyClass)をある条件でsortしたいのですが
簡単な方法はないのですよね？
これは標準モジュールにそのソート用のsubかfunctionを書くのが一般的ですか？
「ある条件」というのが複数あるので、複数個それを作るのが面倒。
あといま思い付いたけど、そのCollectionをメンバとして含む親クラスをつくり、その親クラスにその複数のsortに相当するfunctionを作るのがカッコイイです？

2022/06/26(日) 18:21:23.39

たぶん用途的にArrayListが良いと思う
>>687

2022/06/26(日) 21:43:05.30

VBAのマクロ名てなにか制限ある？
Sub n98()
MsgBox "a"
End Sub
というマクロを作ったんだが、実行できない。実行ボタンがグレーアウトする。
しかも、マクロボタンを押すとセルカーソルが彼方に飛ばされるんだが。
https://i.imgur.com/7iCfenT.mp4

**デフォルトの名無しさん** (HK 0H9f-b1XO) · 2022/06/26(日) 22:29:47.48

>>689
ワイはそのコードコピペしたらちゃんと動いたよ
モジュールのコード上でＦ５キー押してみたら？

**デフォルトの名無しさん** (HK 0H9f-b1XO) · 2022/06/26(日) 22:31:36.52

マクロ名の制限ももちろんあるよ
ググればすぐ出てくるはず

だけどこのコードのマクロ名は問題無い

2022/06/26(日) 22:39:05.59

>>690
VBEでF5なら動くが、
エクセル本体で開発タブのマクロボタン押すとああなる。
ちなみにバージョンは2013。
マクロ名が英字2字＋数字だと、マクロボタン押すとそのアドレスのセルに飛ばされるな。
もちろんマクロは実行できない。
そんな制限あったけ？
セルのアドレスて「予約語」になるのか？

2022/06/26(日) 23:12:48.18

>>689
excel2010だけど全く同じ現象が起こるわ
Ｆ5からじゃくてＡｌｔ+Ｆ8な

2022/06/26(日) 23:30:30.80

この画面開いたらn98セルに行って勝手に選択（コピー？）してくれる
https://i.imgur.com/8G6jMt0.png

逆に、alt+f8でマクロを開いた状態でセル選択するとテキストボックスに反映されるので、
ここは参照かなにかで連動してるっぽい
https://i.imgur.com/cIRhTsz.png

2022/06/27(月) 07:11:11.91

>>694
予約語とかセル番地名、数字始まりは駄目

**デフォルトの名無しさん** (HK 0H9f-b1XO) · 2022/06/27(月) 07:18:40.52

>>695
セル番地もダメだったのか、ウソついてごめんなさい

2022/06/27(月) 07:20:43.06

セル番地名ってダメだったのか

2022/06/27(月) 07:28:39.14

For each i in collection
　if a=1 then
　　if b=1 then
　　　if c=1 then
　　　　Debug.print "うんこ"
　　　end if
　　end if
　end if
next i

こんな階層はしないほうがいいですか？もしそうならどうすべきですか?
ちなみに実際の条件文はa=1などのように単純ではなくてもっと複雑怪奇で長い文字列です。

2022/06/27(月) 07:42:09.30

インデント揃えてれば良いんじゃない？
それかある程度深くなったら別ルーチンや関数に飛ばすとか

2022/06/27(月) 07:44:02.36

if a=1 and b=1 and c=1
にすればすっきり

2022/06/27(月) 08:00:18.06

>>700
VBAはショートサーキットじゃないから遅くなる

2022/06/27(月) 08:05:04.58

>>695
それどこかに書いてある？
まあ予約語や数字始まりがダメなのはわかるけどセルアドレスは盲点だわ

2022/06/27(月) 09:20:29.88

>>698
何がしたくてそのIF文を書いてるかわからないけどELSEに対しての記述がなければ>>700みたいな書き方にしてもいいけど
自分はその程度のネストは許容範囲だけどね
それとなんでしないほうがいいと思ったかも書くと他の回答もらえるかもね

2022/06/27(月) 09:26:36.50

年取ると4階層なんかもう見たくない

2022/06/27(月) 09:27:26.36

Excel2021でも再現したわ
セル番地はモジュール名にできないのか、知らんかった

捨て変数とか作業ファイル名なんか普通にaaa1とか使ってるから、いつひっかかってもおかしくなかったのに
思い返してみると、なぜか捨てSubはtest1とかmacro1にしてた

2022/06/27(月) 09:31:15.07

今わかってる範囲では、モジュール名にできるけど挙動が少しおかしくなる
避けられるなら避けたほうが無難だろうね

2022/06/27(月) 09:34:13.54

>>692
英字1文字でも3文字でも飛ばされる
なんかの裏技に使えそうな気がする
遠くのセル範囲をちょっと確認したいとか

2022/06/27(月) 09:36:09.63

>>698
いわゆる早期リターンとかガード節ってやつ

For Each i In Collection
If a <> 1 Then GoTo continue
If b <> 1 Then GoTo continue
If c = 1 Then
Debug.Print "OK"
End If
continue:
Next

2022/06/27(月) 09:50:33.84

>>708
自分としてはもともとの判定を変更するような回答はどうかと思ったりするんだよね
複雑怪奇とかいてるのでわかりやすい回答なら「if not (a = 1) then」にしたほうがいいのではないかと
それとそこまで書いたらcの判定もcontinueで飛ばしたほうがすっきりするかと

2022/06/27(月) 09:53:16.46

>>707
マクロじゃなくてジャンプでいいってなるんじゃ

2022/06/27(月) 10:00:09.47

>>698
内容がわかるようにコメントつければいいんじゃね

2022/06/27(月) 10:15:26.20

>>705-706
モジュール名にはできないけど>>694のは手続き名だからモジュール名で修飾(※)すれば呼び出せるよ
※ マクロ名の欄に直接 Module1.n98 のように入力する

2022/06/27(月) 10:19:02.79

>>712
スマン、勘違いしてた

2022/06/27(月) 10:24:13.47

>>698
一般論としてネストは浅い方が理解しやすいからそのコードであれば>>700や>>708みたいにした方が読み易い
ただ>>701が言うようにVBAにはショートサーキット（短絡評価）がないので遅くなるのはいいとしても
If N <= UBound(A) And A(N) = 1 Then ...
みたいなのは
If N <= UBound(A) Then
If A(N) = 1 Then ...
のようにする必要がある

2022/06/27(月) 10:29:53.48

まあ、複雑な判定するならメソッドに切り出すのが間違いないわな
VBEだとIDEのサポートないから若干めんどくさいけど

2022/06/27(月) 11:00:14.42

>>698
賛否あると思うが
For Each i In Collection: Do
　If a <> 1 Then Exit Do
　If b <> 1 Then Exit Do
　If c <> 1 Then Exit Do
　Debug.Print "OK"
Loop: Next
みたいな書き方もある

2022/06/27(月) 11:01:13.33

最後の行間違えた
Loop Until 1: Next

2022/06/27(月) 11:05:37.12

あんまりにもネストするなら、判定文(if複数)だけ別プロシージャ(Function)として外出しして、TrueかFalseを返させるようにするかな
IsGoukaku みたいなプロシージャ名にすると何したいかがわかってメンテ楽だし

2022/06/27(月) 11:29:16.42

セル番地がダメならダメでいいけど、あんな挙動をするのはバグだよ。
本来はメッセージだして弾くべきじゃね？
office365でも同じならだれかMSにチクってみてくれ。
以前、MSの掲示板にWinの不具合を書いたら、
速攻で消されたが、
こっちでも確認できたから開発にレポしといたていうメールが来て感動したわ。

2022/06/27(月) 11:31:14.56

>>719
365でも同じだよ

2022/06/27(月) 11:34:53.87

>>700
if a=1 and b=1 and c=1
にすると内部では判断を3回することになる。

if a=1 then
if b=1 then
if c=1 then
にすると判断は少なくなるのだから、ネストするほうが正解じゃね？
もちろん、ifを書く順番は判定が少なくするようにする必要はあるが。

2022/06/27(月) 11:48:23.44

>>719
ゆうてVBAとかアプデすんの？

2022/06/27(月) 11:52:04.31

してるよ

2022/06/27(月) 11:52:52.11

セキュリティ以外で？

2022/06/27(月) 11:57:18.86

うん

2022/06/27(月) 11:59:27.42

頻度はwindoewsやエクセルに比べたらかなり低いけど、やってるよ
確かちょっとずつ便利になってる。本当にちょっとだけ。関数が増えたりはしていないはず。
ソース探したけど見つからねぇ

2022/06/27(月) 12:02:08.29

ふーん。。
まあ元号はあったね。。
古臭いよね。。

2022/06/27(月) 12:04:52.44

言語仕様っぽいページはあった
https://docs.microsoft.com/en-us/openspecs/microsoft_general_purpose_programming_languages/ms-vbal/d5418146-0bd2-45eb-9c7a-fd9502722c74

結局代替がないからなあ

**デフォルトの名無しさん** (ｽﾌｯ Sd5f-J46v) · 2022/06/27(月) 13:50:25.11

>>718
俺も大体これでやってる

2022/06/27(月) 19:05:07.99

>>698
そもそもi使ってねーじゃん

2022/06/27(月) 19:57:12.47

どなたか教えてください。

売掛金消し込みのマクロを作成しています。VBAでソルバーを自動化したいと思っています。

目的セル、制約条件の参照セルの開始行が40行ごとに下がっていき、指定値は数値ではなく、セルを指定したいです。

とりあえず、全部の変数をvariant型にしてますが、なかなか上手くいきません。

2022/06/27(月) 20:18:45.52

731です。

sub ソルバー()

dim 振込1 as variant
振込1=worksheets("ソルバー").range("n2:n8")

solverok setcell:= worksheets("ソルバー").range("i3"),
maxminval:=3,
valueof:=振込1,
bychange:=worksheets("ソルバー").range("e2:e40"),
enginedesc:="grg nonliner"

solveradd celreff:=worksheets("ソルバー").range("e2:e40"),relation:=4

solversolve userfinish=true

2022/06/27(月) 20:22:23.17

で、何がうまくいかないの？

2022/06/27(月) 20:26:50.30

>>733

ソルバーを実行しても、目的セルが0のままで指定値にと一致する金額の組み合わせが見つかりません。

どの請求分の入金なのか、突合したいです。

2022/06/27(月) 20:35:36.69

>>734
ソルバーわからんから確認させてください
振込1って変数は配列に入れた値を指定したいものですか？それともセル自体を指定したいものですか？
前者であればそれでいいですが、後者なら変数はRange型にする上で代入時に
Set 振込1 =〇〇
としないといけませんが…

2022/06/27(月) 20:37:19.75

連投すみません。

変数宣言して、for〜next構文でe列を("e"&i+40)や、n列も("n"&i+40)みたいにしたいんです。

d列には請求金額、e列は空白、目的セルにはsumproduct関数が入ってます。

指定値セルはn2:n8で、それぞれ違う金額が入力されてます。入金額とn2:n8内の金額が一致する組み合わせの請求金額をVBAを使って、求めたいです。

2022/06/27(月) 20:39:44.13

>>735

ありがとうございます。
ヘルプを確認したら、指定値はバリアント型で書けとあったので、なんとなくvariantにしていました。

2022/06/27(月) 20:45:55.27

>>737
マルチ死ね

2022/06/27(月) 20:58:49.57

とりあえず、rangeで回すんじゃなくてcellsで回したら？
n列はcells（i,14）
e列はcells（i,5）で

2022/06/27(月) 21:09:33.28

>>739
sub または functionが定義されませんエラーが出ます…

2022/06/27(月) 21:10:43.06

>>739

cellsも以前試したんですが、上手いこといきませんでした

2022/06/27(月) 23:45:06.09

こんな奴が作るプログラムで金勘定とか笑えるわ
己を知らないって馬鹿の特徴なんだよな

**デフォルトの名無しさん** (ｽﾌｯ Sd5f-J46v) · 2022/06/28(火) 00:04:39.14

ソルバーってカタカナで書くの余計分からないよ
素直にSolverで良いだろうに

上の困ってるのは一旦マクロの記録で動作確認お薦め

2022/06/28(火) 00:07:49.75

>>742
２行目はよく解る www

2022/06/28(火) 05:51:37.94

オレオレ詐欺の手伝い？

2022/06/28(火) 07:36:23.45

>>736
なんで会計ソフト使わないの？

2022/06/28(火) 07:54:25.17

Excelは万能ではないから会計ソフトでやるべき事柄

2022/06/28(火) 08:48:25.11

この世で最高のコードを見かけた

With Range("F27：F56")
.Value = .Value
End With

2022/06/28(火) 09:10:04.94

>>748
それの意味解る？
ちょっと前に同じ様なコード載せてた人いたけど
複数範囲は一度に設定できないって嘆いていたよ

2022/06/28(火) 09:10:36.27

>>732
solverを使うときのパラメーターの設定の方法が間違ってんじゃない。多分値渡しではなくsetを使うと思うよ。
まずマクロの記録もう使ってなんでもいいからsolverで問題を解いてみて、solverを使うときの正しいVBA の書き方を確認しなさい。

2022/06/28(火) 09:52:29.46

>>698
Ifなんたらかんたらを使わないで
B2になんか文字が入っていたらC2を
B3になんか文字が入っていたらC3を…

をスマートに書くならどう書きます？

https://i.imgur.com/5VtTNti.jpg

2022/06/28(火) 11:02:29.10

>>751
こんな質問してマウントとりたいんだろうか・・・
こんな質問するならそのスマートな回答も書いておけばいいのに

2022/06/28(火) 11:15:10.25

VBA使わなくてもセルに関数書いときゃいいんじゃね

2022/06/28(火) 12:14:32.96

>>751
お前Access VBAスレにExcelの質問書き込みするんじゃねーよ
迷惑だ

2022/06/28(火) 17:20:05.57

すみません、神様達、教えてください

エクセルVBAでは
関数内関数はどうやって作れますか？
また複数の関数を目的別に整理する方法として
パッケージやモジュール単位にまとめたり
マップに収納したりはできるのでしょうか？

2022/06/28(火) 17:52:33.73

>>755
できる

vbeエディタウインドウを凝視しているとやり方が判るはず

2022/06/28(火) 17:59:52.62

>>756
具体的にやり方を教えてあげなよ

2022/06/28(火) 18:51:45.14

>>755
関数内関数ってこれのこと？
https://www.sejuku.net/blog/28904

**デフォルトの名無しさん** (JP 0Ha3-tCSL) · 2022/06/28(火) 19:15:28.84

>>756
ちょっと凝視してみます

>>758
疑似コードですけど例えば、
Function out()
　Function in1()
　End Function
Dim a=Function in2()
End Function
out=Array(in1,a)
End Function

みたいな感じで
関数outの中に関数in1を宣言したり
変数に関数を代入して、returnしたりしたいのです

2022/06/28(火) 19:22:25.73

ローカル関数もサポートされてないし
関数をオブジェクトとして扱えないので変数への代入も無理です

**デフォルトの名無しさん** (JP 0Ha3-tCSL) · 2022/06/28(火) 19:22:27.90

>>756
標準モジュール単位で関数をまとめればいいってことでしょうか？

**デフォルトの名無しさん** (JP 0Ha3-tCSL) · 2022/06/28(火) 19:29:24.91

>>760
ありがとうございます
なるほどです
すっきりしました、神様ありがとう

モジュールを使いながら関数を整理するか
関数用にファイルを作って管理するかします

2022/06/28(火) 20:14:33.61

>>761
正解です

2022/06/28(火) 20:14:53.99

それからモジュールには適当に名前を付けることができますので

2022/06/28(火) 20:17:06.37

関数内関数ってのは、関数の中で定義する関数のこと
phpだと

sub foo1
if a = b then

　sub foo2
　　foo2=1
　end sub

else if

　sub foo2
　　foo2=2
　end sub

end sub

みたいなことができる
あんまやらんけど

2022/06/28(火) 20:22:34.06

>>759
関数内関数をそこで書いているようなやり方ではできないけれど、どうしてもやりたければ vbe オブジェクトを定義してそこにコードを書き込むという疑似なやり方はできる。あるいはコードを作成するプロシージャを作成してそのプロシージャを呼び出して引数を渡してコード作った後、そのコードを呼び出すみたいなことはできるのかもしれない。
コードを実行中に別のコードを作成するという経験がないんでエラーが出るかどうかは分からない。

ただ面倒だし見通しも悪くなるんで外側で関数を定義した方が簡単だと思うんだけれどなぜ関数の中で関数を定義したいのでしょうか。

**デフォルトの名無しさん** (JP 0Ha3-tCSL) · 2022/06/28(火) 20:32:39.05

>>766
ありがとうございます
vbaがエクセル前提の言語と考えると
私が考えているような関数は使うべきでもないのだなって思いました

神様たちのアドバイスに感謝です
ありがとうございました

**デフォルトの名無しさん** (JP 0Ha3-tCSL) · 2022/06/28(火) 20:42:12.06

>>765
ありがとうございます

**デフォルトの名無しさん** (JP 0Ha3-tCSL) · 2022/06/28(火) 20:50:26.07

>>766
なぜ関数の中で関数を定義したいのか

すべての関数を同列に記載すると見通しが悪い気がして
例えば数式に関するものなら、calcurationでまとめ
出力に関するものなら、printでまとめ
保存に関するものなら、saveでまとめ
っていうように、目的別、機能別、階層別に関数をまとめると
後で見直したときに分かりやすいかなって

さらにfacadeみたいに間口を作れば
拡張も修正もしやすいかなって思いまして

2022/06/28(火) 20:56:44.33

>>755
＞関数内関数はどうやって作れますか？
関数内関数はない
classは貧弱だけど、ある
ただvbaそもそもの概念として、エクセルのスクリプトであるという点がある
あんまり大きなclass作りたいなら、別言語の方が良いと思う
エクセルvbaはゲームとかもキツイ（メインloopが超貧弱でvbaを起動しっぱなしだとよく落ちる）

＞また複数の関数を目的別に整理する方法として
＞パッケージやモジュール単位にまとめたり
＞マップに収納したりはできるのでしょうか？
標準モジュール単位でまとめられるけど・・・
エクセルvbaの標準モジュールは「どのシートにも共通する処理」のような意味合いで用いられる

よくも悪くも、vbaはエクセルのためにあるので
あまり壮大な事をするのには向いていない

**デフォルトの名無しさん** (JP 0Ha3-tCSL) · 2022/06/28(火) 20:59:53.87

>>770
class調べてみます！
モジュールとclassが使えれば
見やすいコードかけるかも！

そんな壮大なプログラムを作るつもりなないのですけど
一処理、一関数にしたいとなると
ある程度の単位で処理をまとめていきたいなって

ご丁寧なご解説に感謝です
ありがとう、神様！

2022/06/28(火) 21:13:22.89

>>750
アドバイスありがとうございます！

入金消し込みの自動化をせよと上司からの指示で、初心者なりになんとかやってるんですが、会計ソフトの会社でもお手上げな仕組みの売掛金管理のようです。

setとcellsでなんとか、試行錯誤してやってみようと思います！

またわからなくなったら、質問させて頂きます。

2022/06/28(火) 21:26:40.99

十年近くぶりに触るし上級者だったとかじゃないけど
VBAで
今開いてるPhotoshopの選択しているレイヤーを基準にExcelの指定セル範囲(例:A1からA20)を1セルごとにレイヤーを1枚作りセルの中身を文字ツール(テキスト)でレイヤーを作成。
例:ExcelのA1に「あいう」、A2に「えお」、A3に「123」が入力されていたとし、指定範囲選択(A1〜A3)を、開いてるPhotoshopにレイヤー追加しながらテキストレイヤー「あいう」、「えお」、「123」を作る(計3枚)。その際、Excelに近い見た目の形で…この場合、横に少し間隔を空けて「あいう」「えお」「123」が並ぶように作成する。(文字の大きさや色は直前に使ったpxサイズ、色とする)
って出来ますか？