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/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
互換、環境の違い……

2021/05/01(土) 13:30:58.50

ピボットを設定して
たて列を月
よこ列を単価

合計は "値で合計"にする <はじめは個数で合計>になってる
いちばん間違えやすいところ

2021/05/01(土) 14:28:03.48

>>573
> >>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
あらためて
ありがとうございます。
整数なら無視できるようになりました！
あとは Sheets("s2").Cells(1, 3) にSheets("s１").Cells(1, 2).Valueが整数じゃなければSheets("s１").Cells(1, １).Valueが入れば完璧です
ちょっと自分でググってみます！もしできないようならまた質問させてください

2021/05/01(土) 15:00:22.43

>>584
誰か建ててくれると期待してはや数年。。。

2021/05/01(土) 15:05:23.71

PowerQueryスレは建ったんだけど、
https://mevius.5ch.net/test/read.cgi/bsoft/1571540368/
ピボットスレ、建ててみる？

先日、総合相談スレでPowerPivot絡みの質問はあったが。

2021/05/01(土) 15:15:39.62

>>586
ピボットだと=で積み立てで足していくようにできなくね？
合計しか出てこない

2021/05/01(土) 16:16:38.30

あーそうかも
やはりピボットスレが立つまで待ちますか

2021/05/01(土) 16:42:05.48

>>581

それは、たとえば、元表にB1＆countif(B$1:B1,B1)みたいな作業列を作って
飛ばしたい表に、iferror(index(D:D,match(作業列,コード&column()+1,0)),"")
みたいな数式を貼り付けるのではだめですか？

1つのセルに、数式として入っている必要性が、あるのですか？

2021/05/01(土) 17:05:58.85

>>592修正、matchの書式も違うし

月も必要なので、B1＆E1 を作業列Fに、count(F$1:F1,F1)を作業列Gに入れて

飛ばしたい表のA列はコード、B列は月、C列以降にiferror(index(元表!$D:$D,match($A1&$B1&column()-2,元表!$F:$F,0)),"")

こんな感じではだめですか？

2021/05/01(土) 17:25:51.43

>>592
合計なら楽なんですが、どうしても=+で数字を積み立てないとダメなんですよね、、
だから数式では解決できない気がします
vbaでやるなら配列に詰め込んで最後に結合させるのかなあとか思ってるんですがなかなかいいコードが思い浮かばない、、

2021/05/01(土) 17:29:33.26

>>593
ちなみに飛ばしたい表のB列は月というか
　　　　　月
　　　　　　
コード～

みたいな形になってて、月は横軸の大項目としてしか存在してないんですよね
色々厄介な作りになってまして、、

2021/05/01(土) 17:33:09.92

知能に障害がある人ばかり質問するようになった感じ

2021/05/01(土) 17:49:10.58

コロナ自粛でGW中だから暇なんだよ
暇な人に相手して欲しいんだよ

2021/05/01(土) 18:04:53.11

知恵袋でやれば良いと思う

**579** (ﾃﾃﾝﾃﾝﾃﾝ MM8f-m/Zw) · 2021/05/01(土) 18:07:44.96

>>581
> みたいな感じで月が同じでコードが一致してる場合だけ積み立てていくことってできますか？
月が同じってどこと同じなの？
コードのようにどこかで指定するってこと？
とりあえずコードがA列、金額がB列、月がC列に入っているとして
Dim 元表 As Sheet: Set 元表 = Range("A1:C10")
Dim 金額() As String: ReDim 金額(元表.Row To 元表.Row + 元表.Rows.Count - 1)
Dim 書込行 As Long: 書込行 = LBound(金額)
Dim 行 As Long
For 行 = LBound(金額) To UBound(金額)
If Cells(行, "A").Value = コード And Cells(行, "C").Value = 月 Then
金額(書込行) = Cells(行, "B").Value
書込行 = 書込行 + 1
End If
Next
ReDim Preserve 金額(LBound(金額) To 書込行 - 1)
Range("D1").Formula = "=" & Join("+",
金額)
みたいな感じでいけると思う

>582
ごめん、テキトーにググって書いてるから間違えてるみたいやな
Application.WorksheetFunction.Transpose
にしといて

2021/05/01(土) 18:46:01.00

>>599
最初の型はSheetじゃなくてRangeじゃね
あとこの人の言ってるのは普通に文字列としてValueプロパティに式を書き込めばいいような気がするけど

上で語ってる人ってあんま教えに来ないよな

2021/05/01(土) 18:57:24.37

>>599
試しにやろうとしたら最終行が型不一致でエラーになりました・・

**579** (ﾃﾃﾝﾃﾝﾃﾝ MM8f-m/Zw) · 2021/05/01(土) 19:57:34.66

>>600
> 最初の型はSheetじゃなくてRangeじゃね
すまん、そうだわ

> あとこの人の言ってるのは普通に文字列としてValueプロパティに式を書き込めばいいような気がするけど
ちょっと今試せないからやってみてくれ

>>601
すまん
Range("D1").Formula = "=" & Join(金額, "+")
だったわ

2021/05/01(土) 20:06:26.75

>>602
ありがとうございます。
今度は下から二行目でインデックスの範囲外エラーになりました、、
書込行が1行目だとredim～のところの書込行-1が0になるからですかね？

2021/05/01(土) 22:02:09.39

>>603
自分で考えることくらいしろや、ゆとり

2021/05/01(土) 22:18:52.25

dim hako() as variant

redim hako(ここはデータ数を格納)

for i = 0 to 回数

hako(i) = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(1,1), Worksheets("Sheet1").Cells(1,1)).Value

next

こんな感じで配列の中にセルの文字入れて行こうとするとエラーになるんですが原因分かる人居たら教えてください
便宜上cellの位置は1,1にしてますけど実際は縦が動いていきます

2021/05/01(土) 22:41:00.37

>>541
> これ、要するにString型変数の値は参照値って事？
> 察してはいたけどByval xxx$って嘘じゃん

逆にC知ってると、文字列がプリミティブってのにものすごい
違和感があるよ。「ああ、内部で変換してんだな。そういうものだと
思っておこう」と、自分を納得させる。

**579** (ﾃﾃﾝﾃﾝﾃﾝ MM8f-m/Zw) · 2021/05/01(土) 22:45:28.00

>>603
条件に合うものがないと ReDim 金額(1 To 0) の様になってエラーになる
VBAではReDimで空の配列は作れないので条件に合うものがない場合を場合分けして処理するしかない
例えば条件に合うものがない場合はD1を空にするなら最後の２行を

Dim 式 As String
If LBound(金額) < 書込行 <= Then
ReDim Preserve 金額(LBound(金額) To 書込行 - 1)
式 = Join("+", 金額)
Else
式 = """"""
End If
Range("D1").Formula = "=" & 式

とかにすればいい

2021/05/01(土) 22:53:14.11

>>605
＞hako(i) = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(1,1), Worksheets("Sheet1").Cells(1,1)).Value
の、
Worksheets("Sheet1").Cells(1,1)が指してる先が数字でないなんてオチはないよね？

2021/05/01(土) 23:00:47.42

>>608
今見てみたんですが
ちゃんと数字を指してました

2021/05/01(土) 23:06:25.35

>>607
すごい！
ちょっと動くようになりました。
二行目のif文の<=って消していいですかね？
消さないとエラーになってしまったので
ただ、式=join（"+",金額）のところで型が一致しないエラーになりました

2021/05/01(土) 23:16:54.43

いつものあいつ確定

**579** (ﾃﾃﾝﾃﾝﾃﾝ MM8f-m/Zw) · 2021/05/01(土) 23:44:05.17

>>610
> 二行目のif文の<=って消していいですかね？
消し忘れだ、すまん

> ただ、式=join（"+",金額）のところで型が一致しないエラーになりました
デグレードしてる俺が悪いんだが、>>601からの話と同じ

2021/05/01(土) 23:54:50.36

>>612
凄いです！！
出来ました！！
ちょっと実際の表でも細かい数字入れてみて検証してみます。
GW潰してずーっとこの問題を考えてました。
一筋の光が見えた気がします！！ありがとうございます。

2021/05/02(日) 09:42:00.36

>>590
PowerPivotでDAX関数使えば出来るかも。

2021/05/02(日) 09:58:00.77

で、建ててみた

Excel ピボット Power Pivot DAX関数質問スレ
https://mevius.5ch.net/test/read.cgi/tech/1619916663/

2021/05/02(日) 10:03:52.79

いきなり宣伝てアンタ

**579** (ﾃﾃﾝﾃﾝﾃﾝ MM8f-m/Zw) · 2021/05/02(日) 10:19:11.69

>>605
> エラーになるんですが
なぜエラーメッセージを書かないの？

>>608
数字でもダメでしょ
hako(i) = Worksheets("Sheet1").Range(1, 1).Value
になるだけだし