Excel VBA 質問スレ Part78

2022/10/30(日) 13:43:16.31

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑同じ内容を3行貼り付けること

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

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

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

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

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

**デフォルトの名無しさん** (JP 0Hcf-Z2Iz) · 2022/12/02(金) 11:44:11.14

他の言語でやってるとね～やっぱ欲しいなって思う場面が出てくるんだよね～
別にテクニカルな事をしたいわけじゃないのよ～

**デフォルトの名無しさん** (JP 0Hcf-Z2Iz) · 2022/12/02(金) 11:44:35.67

まぁ一つ言えばこの一連の流れで>>211は頭一つ抜けて無能だね～

2022/12/02(金) 12:18:39.53

>>214
やりたい理由なんていろんなケースであるからそれをいってもしかたないでしょう
自分だって他言語では使う事もあるしね

2022/12/02(金) 14:20:37.10

神様達、ありがとうございました
無いと分かればそれはそれですっきりです

2022/12/02(金) 19:29:27.52

ある条件を満たせば使えるようになるのは内緒だ

2022/12/02(金) 20:03:26.58

教えるつもりがないならだまってろ

2022/12/02(金) 20:23:06.13

>>220
いや、無理無理
黙ってられない年寄りばかりだから。

2022/12/03(土) 10:30:49.38

神様達、よろしくお願いします
classモジュール変数に、public で配列を置きたいのですが
配列は、オブジェクトモジュールのパブリックメンバーとして利用できません
と怒られました

クラスをnewして、メンバーとして配列にアクセスする方法はありませんか？

例）=================================
標準モジュール
dim cls as Class1
set cls = new Class1

debug.print(cls.ar(0)) <---こう呼び出したい

=============================
クラスモジュール

public ar() as variant

private sub Class_Inisialize()
ar(0)="xxx"
end sub

**222** (ﾜｯﾁｮｲ 7df0-7kHv) · 2022/12/03(土) 10:34:59.98

神様たち、すみませんでした。解決できました。ごめんなさい

2022/12/03(土) 10:52:40.84

>>218
出来ないことはないが
VBAでは過去の遺物、負の遺産として
使用が暗黙的に禁止されている。
どうしても使いたければ
VBA GOSUBでググれ

2022/12/03(土) 12:46:31.23

それサブルーチンだし関数内関数っていっていいの？
引数も渡せないでしょ

**222** (ﾜｯﾁｮｲ 7df0-7kHv) · 2022/12/03(土) 13:22:57.30

GOSUBはミートソースでも食べられないと教わりました

2022/12/03(土) 13:27:47.06

> VBA GOSUBでググれ
全米がずっこけたわ
もう老害はこう言うのでマウント取るしかないんだな...

**デフォルトの名無しさん** (JP 0H06-7kHv) · 2022/12/03(土) 13:46:56.21

>>227
これ若造やぞ

2022/12/03(土) 13:59:22.91

知ってる？
マウント取ってくるのは大抵年寄りだぞ
何故ならば体が思うように動かないからやたら口達者になるから。

**デフォルトの名無しさん** (JP 0H06-7kHv) · 2022/12/03(土) 14:03:12.32

そもそもこれマウントでもなんでもなく単に間違えてるだけでしょ

2022/12/03(土) 14:08:18.64

GOSUBでも似たようなことできるけど○○できないとか制限あるよっていえばいいのに
最初の４行なんて余計でしょもったいぶって言う割には回答がおかしすぎて
マウントとかそういうレベルでもない

2022/12/03(土) 14:14:01.12

↑ こう言うことを言い出すのもたいてい老害w

2022/12/03(土) 14:16:30.51

高階関数のことならVBA関数では不可
ワークシート関数であればLAMBDA(とLET)

**デフォルトの名無しさん** (JP 0H06-g9pY) · 2022/12/03(土) 14:26:56.17

>>232
君、韓国人みたいな発言してるよ
気をつけたほうが良いと思う

2022/12/03(土) 14:33:59.75

いきなり〇国とか言い出すのも老害によくある行動

2022/12/03(土) 14:59:55.68

質問者はなんで出来ると思って質問したのか？

**222** (ﾜｯﾁｮｲ 7df0-7kHv) · 2022/12/03(土) 15:06:08.67

アドバイス頂けただけありがたいです。
神様皆様ありがとうございます

マクロの記録なのですが、

マクロ記録→検索→全部→マクロ記録終了
そしてマクロを選択して編集すると
Macro
と書かれているだけでマクロが記録できません
記録してどんなワークシート関数を使っているのかみたいのですが
何か必要な設定があるのでしょうか？
現在excel2019を使っています

**222** (ﾜｯﾁｮｲ 7df0-7kHv) · 2022/12/03(土) 15:11:13.12

>>236

class.propertyみたいな呼び出し方で
配列や、インスタンス代入変数を参照できたらいいなって思ったんです
function やsubを直接参照するのが気持ちよくなくて

**デフォルトの名無しさん** (JP 0H06-7kHv) · 2022/12/03(土) 15:32:48.63

>>237
特にない
365だけど、昔も今も普通に記録されるよ
https://i.imgur.com/3UPitt6.png

**デフォルトの名無しさん** (JP 0H06-7kHv) · 2022/12/03(土) 16:01:02.34

>>238
vbaは化石だから色々と諦めるしかない

2022/12/03(土) 17:35:51.41

>>235
それ、朝鮮総連のマニュアルだよ。
「あなたは朝鮮人の可能性があると言って相手の反応を見ましょう」

2022/12/03(土) 18:08:54.01

>>224
それ、ちょっと思いついてネタ的に作ってみようかと思ったが
そもそもブロックレベルのスコープもたないVBAでやると大変なことになるだけだったわ

2022/12/03(土) 18:30:19.13

>>232
回答もできず
できることで負けてるから若さで勝ち誇りたいプライドもかわいそうだな
そのまま年取るなよ

>>242
ある程度まとまったコードを例えば分岐のいくつかでやりたいとかの場合は有効かもしれんけどね

2022/12/03(土) 19:16:17.53

>>238
Class内にDictionaryなりCollectionなりをSetしておいてItemをため込み、
Property Getで取得することはできそうな気がする。。。

ごめん、検証してないし希望とは違うかもしれんけど。

2022/12/03(土) 22:12:00.65

複数行にまとめてVLOOKUP関数を埋め込みたいのでFormulaR1C1プロパティを使っているのですが、マジックナンバーを避けるために検索範囲や列番号を変数で表現すると一行がやたら長くなってしまいます
出来るだけ簡潔に書きたいのですが、何かいい方法はありませんかねえ

2022/12/03(土) 22:38:24.22

>>245
やたら長いとやらを貼らないと意味が分からない

**デフォルトの名無しさん** (JP 0H06-7kHv) · 2022/12/03(土) 22:54:43.08

>>245
indirectかませてるって事？

2022/12/04(日) 05:27:51.07

>>245
長くなるというのがどういう状況かわからんけど、範囲をテーブル化して
構造化参照するとかListobjectで指定するとかは？

2022/12/04(日) 05:34:26.58

テーブルとか職場の人が嫌がるのですが

2022/12/04(日) 07:20:23.13

>>249
あーテーブル嫌がる人けっこう多いよねーｗ
>>245の件としてレスするけど、テーブル書式設定をクリアしてテーブルっぽく見せないってごまかしは置いてくとしてｗ
変数って書いてるからVBAコード内での一行なのだろうと仮定して、変数にすると長くなるのであれば変数名を短くするしかないかと。
多少なりともわかりやすくというなら列番号はEnum定義しておくとか。そのくらいかな。
的外れなレスだったらごめんね。

2022/12/04(日) 07:55:31.27

構造化参照も理解できない方たちが事務やってるの多いもんな

変数てLET関数のことかな
VBAの変数名の付け方が参考になるんじゃないか
むしろVBAだと変数名は少し長くなっても後から理解できるようにしておくというのが多いけど

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/04(日) 08:09:42.33

>>240
新旧の互換性を維持する系の言語は
いろいろ個性が強くなりますので
できないことはできないで教えていただけてすっきりです

>>244
アドバイスありがとうございます
クラスモジュール内のプロシージャーをすべてprivateにして
各プロシージャーの処理結果をモジュール変数の参照のようにしたいのです
そうすれば多少は依存性が解消できて見通しよくなるような気がしてます
何かいい方法ないでしょうか？

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/04(日) 08:10:27.13

>>239
ありがとうございます
何か自分の設定がおかしいのかもしれません
調べてみます

2022/12/04(日) 10:02:49.72

自分はテーブル大好き派なんだが嫌いな人多いんだ

2022/12/04(日) 12:51:49.12

嫌いどころかデータ蓄積の入力用シートすら作ってない不思議な人達ばかりよ現場は。
ひとつの表で完結させようとしている。

良くそれで仕事を完遂できるなと

2022/12/04(日) 13:25:18.39

すみません
凄く基本的なマクロの質問になると思いますが質問させてください
シート2のA2からA列の最終行までの値をコピーして
シート1のB3に貼り付けをしたいのですがどうしたらいいでしょうか？
範囲を最終行に応じて自動的に変化させたいです

他にもB2から最終行まで、D3から最終行までとするにはどうしたら良いでしょうか？

2022/12/04(日) 14:09:55.48

>>252
クラス内のプロシージャを全てPrivateにして外部のモジュールから参照するのはしようと思えば出来る。

2022/12/04(日) 14:24:22.61

>>256
VBA 最終行でググれ
https://www.excel-ubara.com/excelvba1/EXCELVBA318.html

> シート2のA2からA列の最終行
なら
With Worksheets("シート2")
LastRow =.Cells(.Rows.Count, "A").End(xlUp).Row
End With

2022/12/04(日) 15:39:27.71

>>256
クリップボード経由しないならこんな感じじゃない
Dim iLastRow As Long
iLastRow = Worksheets("Sheet2").Range("A1").SpecialCells(xlLastCell).Row
Worksheets("Sheet1").Range("B1:B" & CStr(iRow - 1)).Value = Worksheets("Sheet2").Range("A2:A" & CStr(iLastRow)).Value
ちなみに代入元と代入先の行数を合わせる必要があるからそこは必要に応じて書き換えてね

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/04(日) 15:42:10.01

>>257

dim cls as class1
set cls =new class1

cls.arry(0)
cls.dictionary("x")

みたいなことできますか？
プログラムはかなりできあがってきたんですが
出来るようなら書き換えたいです

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/04(日) 16:08:10.76

神様の皆様、よろしくお願いします
動的配列を使って要素を追加するケースで質問です

dim ar() as variant
redim ar(0)
for i=0 to 9
ar(i)=i
redim preserve ar(ubound(ar)+1)
next
redim preserve ar(ubound(ar)-1)　// A

A部分はar(10)を削除しようとして記述しています
この記述は、ar配列の最終要素を削除する動作を保証しているのでしょうか？
それとも何かの影響でarの最終要素以外の要素を削除することはあるのでしょうか？

2022/12/04(日) 16:43:26.32

>>261
VBE上でローカルウィンドウを出してF8を繰り返し押せば確認できるよ

2022/12/04(日) 16:53:49.22

>>261
自分でシミュレーションしてみ

' i = 9 から...

ar(i)=i ' ar(9)=9
redim preserve ar(ubound(ar)+1) ' redim preserve ar(9+1)
redim preserve ar(ubound(ar)-1) ' redim preserve ar(10-1)

2022/12/04(日) 16:55:07.12

>>258-259

ありがとうございます
凄く助かります

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/04(日) 17:09:39.48

>>262
>>263
ありがとうございます
例えば１万回は最終要素を削除できたけれど
１０億回すると削除されない、もしくは最終要素以外を削除してしまう
という挙動はあり得ないのでしょうか？

言い換えてると
vbaの仕様として、ubound(ar)は配列の最終要素を参照しており
ubound（ar)-1は、最終要素の１つ手前を参照することを保証していると考えていいでしょうか？

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/04(日) 18:25:54.71

>>265
良い。問題無い。

保証されないのはforeach
あれは順番が変わる事があるので、foreachで同じ事をやるのは絶対にNG

2022/12/04(日) 18:42:02.29

請求書をつくるとき取引先ごとの売上をいれてるシートをピボットテーブルで日付ごとに合算してそれをコピペして請求書シートに貼ってるんですが
これをうまい具合に自動化することはできませんか？

2022/12/04(日) 18:44:43.55

そういうコードをかけばいいんじゃない？

2022/12/04(日) 18:57:53.90

ピボットテーブルの特定セルを参照する関数なかったっけ？

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/04(日) 19:30:14.40

>>267
取引先ごとの売上をいれてるシートをピボットテーブルで日付ごとに合算してそれをコピペして請求書シートに貼ってる
↓
請求書シートの所定の場所に、ピボットテーブルを表示させる

でいいんじゃないの？
取引先や日付範囲でフィルタすれば全自動になると思う

2022/12/04(日) 20:19:07.12

>>270
そのまま別のデータをコピペすることもあるので
ピボットテーブル直接表示させたらズレませんか？

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/04(日) 20:33:04.86

>>271
どうズレるかはよく分からんけど・・・

ズレるのが怖いなら、
単純に参照すれば良い。黄色の部分は本当にシンプルな式だけで良いと思う
https://i.imgur.com/rwpq1hu.png

2022/12/04(日) 20:47:06.88

>>267
ピボットテーブルのフィルターに「取引先」を設定して
コピペするときにリンク貼り付けすれば
後はフィルターを切り替えるだけじゃないだろうか

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/04(日) 22:29:31.05

>>266
ありがとうございました
これでUboundを業務で使えます
これからもご指導よろしくお願いします

2022/12/05(月) 00:46:19.76

>>272
>>273
いずれもコピーは必要なんですね
ここに貼り付ける、ときたら
勝手に集計したのを貼ってくれといいのですけど

ちなみに零細で月に10枚程度しか請求書ださないとこは
ExcelのなんちゃってDBで十分ですかね？

フリーSQLも含めACCESSやファイルメーカーもたかってみたがデータの柔軟性がなさすぎて困った
これとこれをグループ化とかも簡単にできないし

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/05(月) 01:25:43.19

>>275
＞ちなみに零細で月に10枚程度しか請求書ださないとこは
＞ExcelのなんちゃってDBで十分ですかね？
じゃなくて、成長率
毎年の成長率が高い企業は、本当に気をつけたほうがいい

＞フリーSQLも含めACCESSやファイルメーカーもたかってみたがデータの柔軟性がなさすぎて困った
Excelがおかしな事をしているだけなんだよ
それがたまたま日本の零細～中小に合致しているだけ

2022/12/05(月) 01:33:53.42

>>276
ファイルメーカーつかってたが
レコードを日付でまとめて一覧ししてレポートだす機能もないんだよ…

2022/12/05(月) 01:36:52.68

外部からインポートもコピペではできないし
テーブルを全く計算も不得意ときてる

2022/12/05(月) 03:27:44.54

安い販売管理アプリすら買えない貧乏会社は潰れろ

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/05(月) 06:57:51.15

今どきはフリークラウドの管理ソフト（販売系、経理系、労務系）のサービスが山ほどあるでしょ
提供会社が無償提供やめたり、提供自体やめると面倒だけど
データ移行性を確保できるのならそっち方法がいい

2022/12/05(月) 07:47:07.80

>>279
>>280
それこそつかいにくい

2022/12/05(月) 11:40:12.06

>>246
一例をそのまま貼ると、こんな感じです。

Dim VLOOKUP商品名 As String
VLOOKUP商品名 = "=VLOOKUP(RC" & eCols_明細.得意先コード & "," & WsName得意先テーブル & "!R" & eRows_通常.開始行 & "C" & eCols_得意先テーブル.得意先コード & ":R" & EndRow得意先テーブル & "C" & eCount_得意先テーブル & "," & eCols_得意先テーブル.得意先名 & "," & 完全一致 & ")"
めちゃくちゃ長くて可読性が悪いなと……

2022/12/05(月) 12:03:42.76

変数に代入している際の話なら
VLOOKUP商品名 = ""
VLOOKUP商品名 = VLOOKUP商品名 & "AAA"
VLOOKUP商品名 = VLOOKUP商品名 & "BBB"
VLOOKUP商品名 = VLOOKUP商品名 & "CCC"
とかきりのいいところで分割して代入すればいいんじゃない？

2022/12/05(月) 12:08:08.63

>>282
それ自力で展開できないようじゃやばいよ

2022/12/05(月) 12:14:29.23

>>283
一応テーブルを参照する部分(VLOOKUP関数の参照範囲の箇所)を変数に代入することで少しは可読性があがったのですが、そもそも複数行へのVLOOKUP関数の埋込みをFormulaR1C1プロパティを使わなくても出来ないものなのかなあ、と思いまして
>>284
「展開」とは？

2022/12/05(月) 12:36:26.38

>>267
「GROUP BY 複数条件」じゃないの？

GROUP BY 取引先, 日付

2022/12/05(月) 18:30:47.05

>>285
「そもそも複数行へのVLOOKUP関数の埋込みをFormulaR1C1プロパティを使わなくても出来ないものなのかなあ」ってのがよくわからん
どんな機能を使おうが、セルに代入したいなら必ず一度はコードに記載しなきゃいけないんだから「eCols_明細.得意先コード」とかの変数名を短くするくらいしかないでしょ

2022/12/05(月) 20:42:26.49

>>282
VBAからvlookup関数を埋め込んだことが無いので処理のイメージがつかめないんだけど、
今やろうとしていることはvlookup関数の字面を文字列を繋げて作り上げようとしてる感じなのかな？

そもそもVlookup関数の構文は　VLOOKUP（検索値, 範囲, 列番号, 検索の型）なのだから変数を使うのであれば
検索値や範囲を変数に格納して繋げれば良いのでは？　例えば,,,

Dim val as String: val="値"
Dim rng as Range: set rng ＝ range(”A1”,"B10")
Dim col as long: col = 2
Dim FormulaStr as string

FormulaStr = "=VLOOKUP（" & val & "," & rng.Address & "," & col & ", false）"

みたいな感じ？値や範囲をセルによって可変にしたいなら変数に格納する値を変えればいいわけだし。。。
こういうことじゃないのかな？

2022/12/05(月) 20:47:00.71

そもそも数式をVBAで入力することってあんまりないよな

2022/12/05(月) 21:46:02.27

>>289
そうですねぇ、あんまりないですね。
経験としては、計算式があるのにどうしても直打ちされて消されてしまうシートに
ボタン一発で計算式を復旧させる機能を付けた時くらいですかねｗ

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/05(月) 22:43:24.64

やっぱりVlookやsumiｆみたいな
組み込み関数のほうが、普通にコードするより速く強力なんでしょうか？

2022/12/06(火) 00:26:23.27

コードが短くなるから

2022/12/06(火) 05:54:39.62

>>291
Excelに備わっている関数は強力だと思うし使い勝手が良かったりするものもありますね。
私の場合はcountifやmatch、max、minあたりはVBA上でもWorksheetfunctionからよく使います。

2022/12/06(火) 06:19:16.90

>>291
かなり最適化されてるしマルチスレッドで動作するから同じ処理ならVBAより相当速い

2022/12/06(火) 07:43:11.23

>>275
零細だったらそれこそappsheetでもいいんじゃないですか？

2022/12/06(火) 08:29:25.79

関数を直接入力するとその後にシートに何かしら入力するたびに再計算されるから面倒だわ
その再計算が気にならないってんならvbaで処理しても気にならないレベルでしょ
countifなんか特に再計算に3分とかかかる場合もあるし

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/06(火) 09:00:18.69

>>296
ピボットあたりにしてみては？
coutif遅いよ

2022/12/06(火) 11:03:13.75

>>297
意味が分かりにくくてすまん
だから関数を直接セルに入力するようなことはせずに、vbaでworksheetFunctionとかDictionaryとか使って結果だけを出力するなって意味
vbaで関数を入力するのは引き継ぎとか他人に渡すのを想定したときくらいかな

2022/12/06(火) 11:13:52.38

2行目もよくわからんし
VBAで関数を入力とかイミフなんだが

2022/12/06(火) 11:40:45.39

Application.Calculation = xlCalculationManual はとりあえず書いとくでしょ

2022/12/06(火) 13:28:57.18

つかVBAで関数使えるなんて最近知ったわ

2022/12/06(火) 13:35:48.17

>>301
お馬鹿自慢要らない

2022/12/06(火) 18:35:08.85

どんな機能も馬鹿には使えない

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/06(火) 22:10:52.72

>>300
昔書いてたけど、最近はほとんど書いてないな
PCスペックが大幅に向上し、負荷のないコードが自然に掛けるようになった

2022/12/06(火) 23:34:20.45

2行目がひどすぎる

2022/12/07(水) 01:18:20.40

なにが？

**デフォルトの名無しさん** (JP 0H88-7kHv) · 2022/12/07(水) 07:13:11.50

演算処理能力、メモリ容量、基盤の伝送量と速度が大幅に向上し
組み込み関数による処理、VBA関数による処理などの差によるメモリ効率、処理速度を考えず
組み込み関数に依存しないコーディングを書けるようになった

言い換えると3行になるな
メモリ効率を悪化させてしまった

2022/12/07(水) 11:24:59.17

>>304
関数が入っているセルを参照するようなコードの場合
例えばCells(4, 3)にCells(3, 3)の値を10倍するような関数が入っているとして
Cells(3, 3).Value = 40
Debug.Print Cells(4, 3).Value
とやった場合、自動になっていればCells(4, 3)は400になった値が参照できて
手動ならもともと入っている値が参照できるという状態でしょ
スペックが向上、負荷のないコードだから書かないというのは危険な考えでは

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/07(水) 11:32:32.02

>>308
再計算に左右されるコードを書く事がそもそもおかしいんですわ

2022/12/07(水) 12:12:32.61

配列がらみのFunctionを作ったときにメモリを空けようと「一時的な配列」を初期化しているのだけれどもしかしてあんまり意味ない？
たとえばこのように↓

Function 戻り値配列 (引数)As Variant

Dim TempArr As Variant
(……何らかの処理)

戻り値配列 = TempArr
Erase TempArr ← これ

End Function

同じ要領で“TempDic”をRemove allしたら戻り値の連想配列も空になっちゃったけどこれはなんでなんだろう

**デフォルトの名無しさん** (JP 0H06-4FAg) · 2022/12/07(水) 12:16:33.70

>>310
function抜けたらメモリ勝手に空くから特に意味はない
それに今は潤沢にメモリあるから、メモリの空き容量なんて気にしなくていいよ

2022/12/07(水) 12:21:53.20

>>309
なるほど
こういう回答するやつとは通じ合えないのがわかったわｗ
しかも>>311の回答も適当だしな

2022/12/07(水) 13:54:56.12

>>312
vbaって配列の要素に変数を入れ込んでから配列を初期化すると、
要素変数への参照は切れるけれどメモリ領域は解放されないのでは？
それともvbaって配列要素への参照切れで割りあてたメモリを開放するの？

2022/12/07(水) 14:42:43.86

調べてから質問しよう