Excel VBA 質問スレ Part70

2021/03/18(木) 22:08:43.71

!extend:checked:vvvvv:1000:512
↑同じ内容を2行貼り付けるナリ

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

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

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

2021/04/28(水) 19:07:20.75

ベテランの初心者がきました

よろぴく

2021/04/28(水) 19:31:32.06

エラー429は自力で解決したのでもういいです。

2021/04/28(水) 19:41:38.21

理由はただの書き間違いでした。

2021/04/28(水) 19:45:56.67

429 = 死肉

2021/04/28(水) 23:01:14.39

>>479
Variant型の配列っていうのがあるんだね
配列でないVariant型ならデータが入っていればVariant型ではない（代入した型に変換される）
だからデータが入っているVariant型の配列って無いと思ってた
これ以上は切りが無くなりそうなので止めます
ありがとう

2021/04/29(木) 02:21:41.91

最初から配列であることを宣言した場合と、
Variantに配列をぶっこんだ場合は、
区別しないといけない

2021/04/29(木) 03:35:54.80

Variant型は調べれば調べるほど「？？？」ってなるから気をつけて
VB.Netでは廃止されたくらいの型だから

俺も最初調べててVariant型って内部型とか持ってるし
代入したものの参照でも抱えているだけのものと高を括ってたけど
Variant型の配列を作ってある要素には値型、ある要素には参照型を設定して関数の引数に値渡しで渡すと
きちんと値型の部分は値が、参照型の部分は参照が渡されるのは少し前にレスした通り

そもそも何で配列の値渡しは出来ないのにVariant型に入れると値渡しが出来るような思想で作られているのかも謎。そういうものだと思う他ない。

VBAがObject指向に片足を突っ込んでいるところで止まっているせいか、Objectでもそうでないものも代入出来る型として存在していて使い勝手が良い反面、
コンパイル時に内部型が違う場合でもエラーにならないから実行時に初めてエラーとなって気がついたり、
違う型のものを上書いてしまってエラーとならずに期待した挙動と変わってしまう場合が出て来る恐ろしい型でもある。

Variant型を使うなとは言えないけど
使う際にはその特性と挙動を十分把握していないと、ちょっと何か変わったことをしただけで期待した結果が得られないことになり得るから十分気をつけてね。

2021/04/29(木) 03:54:38.48

余談だけど他の言語にも
例えばJavaScriptやC#には変数宣言する際に、型にvarって宣言することがあるけど、これはVariableと言って全然別物で
最初に代入した値の型をJavaScriptでは暗黙的に、C#では型推論で持ち回るものだから、もし、今後そう言った別言語を扱うことがあれば
そういうものだと覚えておけば勘違いしなくて済むよ。

**デフォルトの名無しさん** (JP 0H98-On6t) · 2021/04/29(木) 06:09:50.46

Variant型っていうのはVBAやVBのために作られたわけじゃない
もともとWindowクラスが持ってる機能に対して、演算子をオーバーロードした物

2021/04/29(木) 06:17:04.92

>>491
> VB.Netでは廃止されたくらいの型だから
単にObject型になっただけ
使い勝手が違うのはVB.NETの方が方に厳しいから暗黙の型変換をしなかったりするため

**デフォルトの名無しさん** (JP 0H98-On6t) · 2021/04/29(木) 06:50:30.24

>>494
VariantとObjectは同じような物とか代替品として紹介してるサイトとかあるけど、これはあくまでも初心者向けの説明
実際はまったく違う物なので、個人的にはこうやって説明するのも反対したいところ

2021/04/29(木) 07:11:32.09

>>495
はあ？
違うと言うならどう違うのか書いてみなよ
まさかと思うけどVBAのObjectの話じゃないよな？

2021/04/29(木) 07:11:42.35

>>495
そうなんだよね
Variantの代わりがObjectだと言われると
うーん、まぁ間違っていないとは思うけど
物凄い違和感を感じるよね。

同じものかと言われれば絶対別物と
はっきり言うことが出来るけど。

2021/04/29(木) 07:17:49.07

>>496
そうだねぇ
とりあえずこんなところかねぇ

ちなみに、何でも入る魔法の型といえば、C#ではobject型がそれに当たる。しかし、object型とVariant型は同じではない。object型は参照型であり、値型の値を扱うにはボックス化という手順が必要になる。また、参照型である以上、メモリの確保と破棄というオーバーヘッドが必ず付いて回る。しかし、Variant型は整数のような値であっても、オブジェクトへの参照であっても、それをそのまま格納できる。ボックス化やメモリの確保、解放のオーバーヘッドもない。まさに万能の魔法の箱である。

https://www.atmarkit.co.jp/fdotnet/extremecs/extremecs_12/extremecs_12_02.html

2021/04/29(木) 07:22:43.60

>>493
＞演算子をオーバーロードした物
どの演算子？

>>498
これってヒープ領域に全部突っ込んでるってこと？

2021/04/29(木) 07:23:03.00

なわけないか

2021/04/29(木) 07:25:47.75

VBAとVB.netはまったく別物で比較しようなんて思わない

標準がバリアントなんだからできるだけバリアントを使うべきだし

値渡しの件も、VBAでは値渡しのが原則なんだから
値渡しのオンリーにすべきとおもうけど
どうしても参照渡したいならパブリックにするだけ

2021/04/29(木) 07:36:18.54

>>498
そこまで言うならStructureでVariant相当のものを作ればいいかと

>>499
> これってヒープ領域に全部突っ込んでるってこと？
そう言うこと

**デフォルトの名無しさん** (JP 0H98-On6t) · 2021/04/29(木) 07:48:54.45

>>499
算術演算子などで文字列を自動的に数値に変換する部分

>>501
厳密な説明をするなら、Variantは暗黙ではあるけど標準ではない
納得いかない人もいるだろうけど、強いて言うなら標準に近いのはObject型の方
Variant型はWindowクラスからの継承だから、VBでもVBAでも、もっと別の言語でも内部ではまったく同じ物で同じ処理が動いてる
何を使う「べき」かはケースバイケース

2021/04/29(木) 07:55:26.52

>>502
冗談で言ったらマジか
まぁオブジェクトは参照になるなら、ヒープ領域云々はそこまで考えなくてもいいか

vbaの文字列は文字の配列じゃなく単に値だったから、variantで文字列使いまくると良くない？
※そもそも値じゃなかったらごめん

2021/04/29(木) 08:01:54.76

>>503
へぇ
VBAやVB6みたいにVariant型みたいなもの
持ってる言語って他にもあるんだ
まだ当たったことないなぁ

2021/04/29(木) 08:12:11.05

VBのときに他の言語がどうこうと長話はじめるのは老害みたいなもの
気をつけたほうがよい

2021/04/29(木) 08:17:52.33

老害だもの

みつを

2021/04/29(木) 08:45:33.51

>>505
VBS
もしかして知っててわざと試してる？

2021/04/29(木) 09:13:27.94

>>508
そうかVBSは明示的に型宣言することないから忘れてた

**デフォルトの名無しさん** (JP 0H98-On6t) · 2021/04/29(木) 09:39:50.72

Visual StudioのC++のVariantクラスも構造体の中身はまったく同じ

2021/04/29(木) 12:13:26.48

そうなると
VBSにてintの宣言をしたいときはどうなるの

2021/04/29(木) 12:38:49.55

その辺考慮することでExcelのマクロとしてどんなメリットがあるの？

2021/04/29(木) 13:12:06.33

>>511 不可能

2021/04/29(木) 13:13:04.28

よくわからんけど一般的な仕事に使うのに知る必要のある情報じゃなさそう

2021/04/29(木) 13:16:00.97

>>512
Variantは多機能だからプログラミングの負担を減らすことができるけど、メモリ食いだから、たとえば大きい配列が宣言できなかったりする

2021/04/29(木) 16:00:22.26

AccessVBAエキスパートの試験では扱う範囲
ExcelVBAエキスパートでは気にしたくていい
試験はAccessVBAの方が2段くらい難しい
もちろんスタンダードの話な

2021/04/29(木) 16:53:35.51

エクセルで組んだ表や数式を
いつパソコンが壊れても
すぐ復元できるよう
メモに残しておきたいんですが
すでにある内容を
HTML文書みたいに文字だけに変換できたりってするんですか？

2021/04/29(木) 17:08:19.64

素直にバックアップとるかクラウドで良くないですか

2021/04/29(木) 18:10:50.49

>>517
壊れたパソコンには復元できないよ

2021/04/29(木) 18:18:45.59

再現っていえばいいのかなんといえばいいのか…
とりあえずもしもの時のためにレシピ？を残しておきたい

2021/04/29(木) 18:24:10.96

.slk マルチプラン形式ならテキストとして読めるぞ

2021/04/29(木) 19:07:29.38

>>515
大きい配列
エクセルのマクロで画像処理している人だよな
1セル1ドットにして輪郭をさがしてる

ちょっと感動

2021/04/29(木) 19:41:36.50

>>520
バックアップしろよ
馬鹿は帰れ

2021/04/29(木) 19:42:12.84

>>517
俺、自分宛にメールしてるわ。

Sub Test()
　：
End Sub

って

2021/04/29(木) 19:53:50.93

フラッシュメモリーに保存してるけどそれも壊れるかもしれないし

2021/04/29(木) 20:04:58.52

だからグーグルのクラウドにでも保存しとけよ

2021/04/29(木) 21:46:03.77

>>525
馬鹿は帰れ

2021/04/29(木) 21:47:52.03

>>525
VBAより先に勉強すべきことがある

2021/04/29(木) 21:57:15.15

>>514
こういう罠みたいな仕様を事前に目を通しておくと躓く事が少なくなる
一生役に立たん可能性も割とあるけどな

2021/04/30(金) 00:10:52.82

これを短く書きたいのですが、どう書けばいいですか？
Range(Cells(r, 1),Cells(r, 10)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.CurrentRegion.Borders.LineStyle = xlContinuous

2021/04/30(金) 00:13:18.87

>>520
カセットテープに保存しとけよ

2021/04/30(金) 00:38:44.27

>>530
やりたいことは
Range(Cells(r, 1),Cells(r, 10))
・・・
Range(Cells(r, 11),Cells(r, 20)
みたいなこと？
全容を見せてくれたほうが早いと思う。

2021/04/30(金) 02:19:19.76

>>530
やりたいことを言葉で説明した方が速そう

2021/04/30(金) 02:34:26.19

>>493
>もともとWindowクラスが持ってる機能に対して、

これ気になるんですが、何かソースありますか？

2021/04/30(金) 02:53:52.43

Excelのマクロをエクセル環境なしに実行したいのですが、
vbsをつかえばExcelは不要ですか？

2021/04/30(金) 03:01:21.54

variantって共用体なのね
データタイプを示すヘッダがあってその後に実体がある
https://excel-ubara.com/excelvba4/EXCEL_VBA_422.html

2021/04/30(金) 03:02:27.11

>>535
vbsってのもある。ファイルの名前の一括変換、辺りならvbsで可能
EXCELのa1セルをb1セルにコピー～みたいなのはEXCELが必要

2021/04/30(金) 03:04:35.08

>>537
すでにあるマクロ名を実行することは
vbsだけで、実行する側にExcelがなくても可能ですか？

2021/04/30(金) 03:23:39.09

vbsは型宣言が出来ない、
VBAで可能なライブラリ参照が出来ない、
暗黙の参照先等の違いがる
それらを使用していないコードなら実行出来る可能性がある
（要するに簡単にはいかない）

正直「みんな入れてるであろうエクセルで実行できる」っていうただ一つのメリットのみを目的ににして
わざわざ使いにくいVBAを仕方なしに許容してるのであって、
エクセル不要ならPythonなりJSなりC#で書くべき

2021/04/30(金) 03:29:54.28

>>538
内容による

vbsとvbaは似て非なるもの、だけど互換性はまぁまぁある
vbaをまるっとvbsにコピペして動くものもあれば、手直ししないと動かないもの、絶対に無理なものもある
まぁコピーペーストだけで一発で動くのは稀だとは思う。大体動かないだろう

また、vbsは無料でwindowsに標準でついているので、あーだこーだ言うよりはやったほうが早い

2021/04/30(金) 03:37:12.82

>>536
貼ってくれたURLのページにある
>String型変数のメモリアドレス

これ、要するにString型変数の値は参照値って事？
察してはいたけどByval xxx$って嘘じゃん
調べれば調べるほど無茶苦茶な言語だなｗ

ただ先頭に配列長が入ってるのは嬉しいな
LEN関数が高速だって事だし、実際に検証したらそうだった
これからは文字列の有無はLEN関数を使おう

**デフォルトの名無しさん** (JP 0Hcb-OedE) · 2021/04/30(金) 04:58:51.13

>>534
MSDNのWin32APIのリファレンス
https://docs.microsoft.com/en-us/windows/win32/api/oaidl/ns-oaidl-variant

これを見れば、Valiant構造体は共用体の入った構造体が共用体になってることが一目瞭然
VBAからは、初期化の手順を省略(自動化)したりして簡単に使えるようにしてある

2021/04/30(金) 06:01:15.47

Dim abc As Variant, maxVal Variant
abc = Range("A1:A100")
・
・
・
maxVal = WorksheetFunction.Max(abc(50 To 80, 1))

これはabc(50 To 80, 1)の部分でコンパイルエラーになりますが、
このように一発で配列内の特定の範囲の値を取得することはできますか？
やはりFor～Nextで回してやらないと駄目でしょうか？

**デフォルトの名無しさん** (JP 0Hcb-OedE) · 2021/04/30(金) 06:19:01.47

>>543
配列のままだとループして調べるしかない
作業用のワークシートに入れてからMAX関数を使えば一発

2021/04/30(金) 07:04:01.89

>>541
> 察してはいたけどByval xxx$って嘘じゃん
噓じゃねーよ、Byvalならちゃんとコピーされる（内部的には新規領域確保して内容コピー）

> 調べれば調べるほど無茶苦茶な言語だなｗ
お前さんの理解力が足らんだけ

**デフォルトの名無しさん** (JP 0Hcb-OedE) · 2021/04/30(金) 07:57:01.95

メモリ上で文字列領域の先頭に長さが入ってるのはMicrosoft BASICの伝統
C言語などで使われる\x00で区切る方式だと文字列の中にChr$(&h00)を入れられなくなるので地味に面倒だった

2021/04/30(金) 09:18:44.82

だから、有限なんですね

2021/04/30(金) 09:53:25.08

改行しろよ、読みにくいだろ

2021/04/30(金) 10:28:11.61

>>544
やはりループで調べるしかないんですね
>作業用のワークシート
なるほど、アクセスで言うところのワークテーブル的な考えですね
ありがとうございました。

2021/04/30(金) 10:56:55.90

WorksheetFunctionってその名の通りワークシート上の計算をするためのものだから仕方ないわな

2021/04/30(金) 13:14:51.01

>>543
それはabc(50 To 80, 1)なんていうのがおかしくて
maxVal = WorksheetFunction.Max(abc) ならちゃんと動く

>>550
WorksheetFunctionは実は配列受け取るものが結構あるんだぜ

**デフォルトの名無しさん** (JP 0Hcb-OedE) · 2021/04/30(金) 13:46:28.53

>>551
質問者は配列全体じゃなくて調べる範囲を指定したいんだよ
目的と違う回答してどうすんの

2021/04/30(金) 13:52:18.51

質問者を納得させるのではなく
自分の知識をドヤ顔で披露する場がこのスレ

2021/04/30(金) 13:55:12.85

古代BASIC語の使えるやつには想像すらできない構文だな
ナウい言語から入るとこんな発想が出来るのか
Enumeraterとか作ってもコールバック関数書くのが面倒くさいからどうしようもないよね

2021/04/30(金) 14:11:06.28

10 CONSOLE 0,25,0,1
20 SCRERN 0,0

2021/04/30(金) 14:13:33.94

>>552
それはいいすぎ
いろんな考えがあって質問者がいろんな回答から判断すればいいだけのこと

2021/04/30(金) 16:19:17.86

sheet1のB1が整数ならSheet2のC1
にSheet1のB1の整数をSheet2のC1に
表示し
Sheet1のB1が整数でない場合は
Sheet2のC1にSheet1のA1の整数を表示
させたいのですが、インデックス範囲エラーになったりして困ってます
Numeric関数なのはわかるんですが
標準モジュールに書くのか
Sheet1に書くのかわかりません
ご教授ください

Private Sub Worksheet_Change（ByVal Taget as Range）

If IsNumeric（Worksheets（"1"）.Range）

2021/04/30(金) 16:51:06.75

>>557
シート名(worksheet.name)はどっち？
・sheet1
・1
それとも1番目のシート？それなら
Worksheets(1)
だけど

2021/04/30(金) 17:19:44.60

Sheet1にコードを書いてます

2021/04/30(金) 19:10:46.17

そもそもnumericって整数かどうかじゃなくて
数字かどうかの判定じゃなかったか？

2021/04/30(金) 19:11:19.57

あとrangeじゃなくてtargetやろ

2021/04/30(金) 19:12:09.12

あっrangeであってたわ
range（target.address）かな

2021/04/30(金) 19:13:35.28

>>557
Worksheet_Change　これはワークシートに変更があったときに実行する内容を書くので
実行したいワークシートに書くのと、書いたワークシート以外のワークシートの変更は無視する

2021/04/30(金) 19:48:53.26

Sheet1のB1の値が変化したらSheet2のC1が変化させたいのでSheet1にコードを書いてチェンジイベントでいいのかな？
むずかしいですね

2021/04/30(金) 21:15:34.30

>>545
値渡しは参照値が渡されるって挙動と矛盾してるだろ
その辺の矛盾の解決のためか、
実際に配列は値渡ししかできないように強制されてる

2021/04/30(金) 21:20:31.83

>>565
値渡しは参照値が渡される
↓
値渡しは、変数に格納されてるのが参照値（メモリアドレス）なら参照値が渡される

2021/04/30(金) 21:52:12.56

>>565
元々文字列は内容変わったら領域確保し直したりするから内部的に参照持つのが普通
ByVal は文字列を複製して渡すしByRefは内部的な参照への参照を渡す
何の矛盾もないよ
中途半端に内部的な参照を気にしてもしょうがないよ

2021/04/30(金) 21:55:44.24

>>565
え？そうだっけ？
確か配列は参照渡ししか出来ないから
Variant型に突っ込んで渡して
値型のものを渡すようにするんじゃなかったっけ？

もっともそれでも参照型のものは参照が渡されるけど。

2021/04/30(金) 22:00:13.74

誰か教えてほしいんですが、
数字を単純に足すんじゃなくて=+100+200というふうに増えた数字を全部数式に残していくように一つのセルに入れていくやり方ってありますか？
例えば別のシートに100 200 350とあったら
記録用のシートのセルに=100+200+350と飛ばしたいです。
セルの中身見た時に650となってるのではなくて=100+200+350と表示されてほしい

2021/04/30(金) 22:02:58.83

>>569
単純にCStrで数字を文字列に変換して繋げばいいだけと思うけどそういう意味じゃない？

2021/04/30(金) 23:05:04.91

>>564
ふつうに関数でよくないの
=IF(B2=int(B2),A2,B2)
みたいなの

2021/04/30(金) 23:07:22.57

>>569
これも式を表示するモードがあったはず

2021/04/30(金) 23:09:00.86

>>557
sheet1モジュールに書く
シート名はs1とs2にしてるのでテキトーに変えて

Private Sub Worksheet_Change(ByVal Target As Range)

If _
Target.Address <> "$B$1" _
Or Not (IsNumeric(Target)) _
Then
Exit Sub
End If

main = Cells(1, 2).Value
main2 = Cells(1, 1).Value

If (Int(main) - (main)) = 0 Then
Sheets("s2").Cells(1, 3) = main
Else
Sheets("s2").Cells(1, 3) = Int(main2)
End If

End Sub

2021/04/30(金) 23:13:44.15

>>569
地味にやるしかない

Sub foo()
Cells(3, 1).Formula = "=" & Cells(1, 1).Value & "+" & Cells(2, 1)
End Sub

量が多いならfornextかなんかで処理すると良い

2021/04/30(金) 23:13:57.10

joinほしい

2021/04/30(金) 23:21:20.34

>>573
ありがとうございます！
今外にいるのでお家に帰ったら試して見ます

2021/04/30(金) 23:40:08.54

>>570
そんな感じで大丈夫です

>>574
数がかなりあるんでループさせて特定の月の数字を拾うところまではできてたんで、そのやり方試してみます。

2021/05/01(土) 00:46:43.32

CONCATがある

2021/05/01(土) 05:48:25.12

>>575
Joinあるやろ
手元にExcelないから試してないけど
Cells(3, 1).Formula = "=" & Join(Application.Transpose(Range("A1:C1" ).Value), "+")
でいけないかな

**579** (ﾃﾃﾝﾃﾝﾃﾝ MM8f-m/Zw) · 2021/05/01(土) 06:04:07.19

よく見たら行/列間違えてるわ
Cells(3, 1).Formula = "=" & Join(Application.Transpose(Range("A1:A3" ).Value), "+")
だな

Excel 2019ならVBA使わずに
C1: =TEXTJOIN("+", TRUE, "=", A1:A3)
でいけるかも

2021/05/01(土) 10:06:17.84

>>580
例えば元表

コード　200 金額　200 5月
コード　150 金額　300 4月
コード　200 金額　500 5月
コード 150 金額 400 6月
コード　200 金額　50 5月

と並んでたとして、飛ばしたい表に

コード200に一致した場合は=200+500+50
コード150 の4月は=300
みたいな感じで月が同じでコードが一致してる場合だけ積み立てていくことってできますか？
うまくいかなくて、、

2021/05/01(土) 10:22:39.67

>>580
別の質問だけど、
Application.Transpose
って
WorksheetFunction.Transpose
の事？

ApplicationってついてたらWorksheetFunctionは省略できるの？

2021/05/01(土) 13:16:18.88

こういう処理はピボットの得意分野だとおもう
ピボットスレにも聞いてみたらとうかな

2021/05/01(土) 13:18:22.06

ピボットスレってあったっけ？

2021/05/01(土) 13:20:57.76

>>582
互換、環境の違い……