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(日) 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サイズ、色とする)
って出来ますか？

2022/06/28(火) 21:45:50.92

いや、関数内関数？
まぁ似たようなものはあるよ。
GOSUBってのが。

ただ、これも負の遺産のひとつだから
あまり使うことはお勧め出来ないけど。

2022/06/28(火) 21:52:47.26

gosubは全然違うでしょ

2022/06/28(火) 22:11:43.10

関数内関数いわゆる高階関数について、VBA関数には高階関数のための機能は無いけどワークシート関数のLAMBDAが該当する

2022/06/28(火) 22:18:47.50

確かに！その発想は正解だが・・・だが・・・

2022/06/28(火) 23:36:42.40

>>773
COM経由で操作できるっぽい。こんなのが引っかかった
https://minatoya.work/419/

2022/06/29(水) 07:26:36.96

>>776
お前名前に騙されやすいタイプだろw
LAMBDA は単なる関数定義で VBA スレ的にはユーザー定義関数と変わらんぞ
ユーザー定義関数はマクロブックにしか入れられないから LAMBDA が作られた

2022/06/29(水) 08:15:54.31

他のプログラミングと同じだと思われているのかな LAMBDA 関数は

2022/06/29(水) 08:48:09.67

ユーザー定義関数では(コーディング時に)関数を自作することはできるが、(実行中に)関数内で関数を定義したり
(実行中の)関数の引数に関数そのものを与えるたりすることはできない
そもそもユーザー定義関数とLAMBDA関数はまったく別のもの

Excelの新関数「LAMBDA」（ラムダ）が一般提供開始～Excel数式が本格的なプログラミング言語に
https://forest.watch.impress.co.jp/docs/news/1387035.html

2022/06/29(水) 08:51:20.38

>>778
ありがとう
そこのページ起点に頑張ってみる

2022/06/29(水) 08:55:02.95

関数型パラダイムをサポートしている言語か、最低でもC言語などで関数ポインタを使ったことがある人でないと高階関数の概念は理解が難しい

2022/06/29(水) 10:34:47.30

privateで良くないか

2022/06/29(水) 12:27:16.10

>>781
高階関数の話ならそこに書いてあるMAPとかSCANがLAMBDAを受けられる様になったことを言うべき
あと関数内関数と高階関数は全く別物だし実行中に定義できるかかどうかも関係がない

2022/06/29(水) 13:48:20.01

記者がアホなんやで

2022/06/29(水) 14:05:01.02

ネットの記事って怪しかったり間違っているのが多々あるよね

2022/06/29(水) 14:22:16.06

ネットの記事だけがそうだと思ってる？

2022/06/29(水) 14:30:17.44

はい次の方