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

2022/11/23(水) 17:54:50.35

香具師　←　化石を久しぶりに見つけたｗ

2022/11/23(水) 18:00:15.39

>>195
うん、知ってる
他でも見たし

2022/11/23(水) 20:28:09.45

ジジババかよw

2022/11/23(水) 22:51:52.84

そんなこと言ってると生きてる間に孫の顔みれんぞ

2022/11/28(月) 07:46:50.71

独り者なら孫どころか子供もいないだろ

**デフォルトの名無しさん** (JP 0Hcf-Z2Iz) · 2022/11/28(月) 08:26:23.40

子供はいるけどもう20年ぐらい会ってないな

2022/11/28(月) 14:54:18.05

悲しい話と捉えていいすか？
生き別れ

2022/11/28(月) 19:26:05.44

面会に来てくれないのか

2022/11/29(火) 11:12:10.65

臭い飯の感想は？

**デフォルトの名無しさん** (JP 0Hcf-Z2Iz) · 2022/11/29(火) 11:36:14.54

うまああああああい

2022/11/29(火) 17:17:30.27

兄が元刑務官だけど、昔に比べればブタ箱の飯はだいぶ香ばしい匂いになったみたいだよw

2022/11/30(水) 08:01:42.75

スタンフォード監獄実験で普通の人でもそういう仕事につくと凶暴になる実験あったな

2022/12/01(木) 12:10:59.18

>>207
実はアレはヤラセだったと関係者が暴露してる

**デフォルトの名無しさん** (JP 0Hcb-Z2Iz) · 2022/12/02(金) 07:28:51.94

神様たち
エクセルVBAで関数内関数を作りたいのですがやり方教えてください

2022/12/02(金) 08:16:36.75

無理、はい次の方～

2022/12/02(金) 09:43:13.76

>>209
作る意味ないので別関数で呼び出せ

2022/12/02(金) 11:05:19.85

「作れない」と「作る意味ない」は違くないか
>>209
つくれないので別の方法を検討しましょう

2022/12/02(金) 11:25:08.14

作る意味があるケースもあるけど現状作れないし作れるようになりそうもないから議論してもしょうがない、はい次の方～

2022/12/02(金) 11:30:07.60

なんでそんな事ばかりやりたいのか

**デフォルトの名無しさん** (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でもいいんじゃないですか？