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/22(火) 21:05:06.99

ネットでサンプルコードを漁っていると配列の型を宣言するときに括弧を省いているのが多いけれどなんでだろう
Dim Array As variant
Array = Range(“A1:C10”)
とか
Dim Array as Variant
Array = TempArray
みたいに

括弧無しの方が処理が早いのだろうか
そもそも括弧有り無しの違いってなんなんだろう

**デフォルトの名無しさん** (JP 0Hfe-stRS) · 2022/11/22(火) 21:39:01.76

>>182
vbaの配列、特に静的配列はカスって事を念頭においておいてほしい。
他の言語はここまでめんどくさくない

静的配列は要素数が決まっていて拡張性が無い。
Dim array_a(5) As Long

動的配列は要素数を変動させることができるがめんどくさい
Dim array_a() As Long

これは配列をvariant型の変数に入れてるだけで、配列の型とは関係がない
Dim array_b As Variant
array_b = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
array_b = Array(1, 2, 3, 4, 5, 6)

どれが良いかというと、variantに入れておけば大体うまくいく
動的配列や静的配列を使うのもいいけど、めんどくさいだけだよ

2022/11/22(火) 22:02:02.71

sub hoge
　for i=1 to10
　　if i=5 then exit sub
　next
end sub

サブルーチンのforとかloop中に
判定文でexit forどころか、exit subするのって、マズいんですかね？

メモリ喰っちゃう？

2022/11/22(火) 22:10:43.92

コメント書いとけばいいんじゃね

2022/11/22(火) 22:18:04.27

>>183
ループを使って配列の要素を一つずつ埋めて行く場合は()付きで配列として宣言しなきゃダメじゃん？
だったら一括代入する場合も()付きで配列として宣言した方がわかりやすいかなあと思うんだよね

**デフォルトの名無しさん** (JP 0Hfe-stRS) · 2022/11/22(火) 22:24:40.04

>>184
特にマズくない
最近は8GBぐらいメモリあるんだし多少食っても全然平気

>>186
vba結構長いこと使ってきたけど、そんなコード書いたことないなぁ
ちょっと分からないわ

2022/11/22(火) 22:47:31.59

>>187
たとえば「シートの表を一旦二次元配列に格納して不要な行を取り除いてからシートに転記する」ってコードの場合、次のようになるでしょ？

Dim RawArr As variant
RawArr = Range("A1:E100").Value

Dim EditArr() As Variant
Redim EditArr(1 to Ubound(RawArr ,1), 1 to Ubound(RawArr ,2))
'↑これは括弧付きで配列として宣言しなければならない

Dim R as Long, C As Long, Rcount As Long
Rcount = 1
For R = 1 to Ubound(RawArr ,1)
If Not RawArr(R, 1) = "不要" Then
Rcount = Rcount +1
For C = 1 to Ubound(RawArr ,2)
EditArr(Rcount, C) = RawArr(R, C)
Next C
End If
Next R

Range("A1:E100").Value = EditArr

このコードにおいて「EditArr」を括弧付きで配列として宣言するならば「RawArr」もそれに準じた方がわかりやすいのではと思うんだよね
超細かくて申し訳ないけれど

2022/11/22(火) 23:39:01.43

jscriptの配列をそのままvbscriptにしたら
メッチャんこ速くなったからそっちの性能はいいんじゃね？

2022/11/23(水) 00:24:02.68

>>188
これってのが
Dim EditArr() As Variant
なら、
Dim EditArr As Variant
でも動いてるようだが？
そのまま走らせたら両方ともそこ以外で実行時エラー出てるけど

さらに言えば、ReDimするなら(Option Explicit onでも)Dimすらなくても動いたような

2022/11/23(水) 03:52:19.28

>>188
俺も>>190と同じく
Dim EditArr() As Variant
Redim EditArr(1 to Ubound(RawArr ,1), 1 to Ubound(RawArr ,2))
だろうが
Dim EditArr As Variant
Redim EditArr(1 to Ubound(RawArr ,1), 1 to Ubound(RawArr ,2))
だろうが同じって認識だわ
動的配列っていうのをわかりやすくするために()つけておくか、くらいの感じだと思ってる

2022/11/23(水) 08:25:42.71

>>184
別にマズくはないよ。
メモリ使ってたとしても使ってる変数が
みんなPrivateのものなら関数抜けた時点で解放されるし

2022/11/23(水) 17:09:12.41

【速報】上司「お前のことはExcelの印刷くらい信用している」→パワハラ

　広告代理店・電報堂（本社・東京都足立区）に勤務する50代の部長が20代の部下にパワハラを繰り返し、
部下をうつによる休職に追い込んだとして民事訴訟を起こされていたことが22日分かった。
　訴状によると部長は部下に対し日頃から「お前のことはExcelの印刷と同じくらい信用している」
という発言を繰り返しており、その結果「どれだけ仕事をしても信用が得られない」
と感じた部下は今年5月からうつの症状を発症するようになり、現在は休職しているという。
　なお上司は取材に対し「信用していると言っただけなのになぜパワハラ扱いされるのか理解できない」
と述べた。

https://kizuna.5ch.net/test/read.cgi/curry/1663509806/

2022/11/23(水) 17:31:53.36

まあ言い方だろうな
Excelの印刷はたまに端や上下が切れたりするけどちゃんと手当てしてやればそこそこ使える
ワードなんかと違ってレイアウトを崩さずにページに収めるとか使える機能もあるしな

**デフォルトの名無しさん** (JP 0Hfe-stRS) · 2022/11/23(水) 17:46:45.69

それ完全に釣りだぞ

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
それこそつかいにくい