Excel VBA 質問スレ Part67

2020/06/26(金) 02:01:29.56

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

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

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

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

2020/08/03(月) 21:53:06.72

また馬鹿が出てきた

2020/08/03(月) 23:28:33.53

>>776
簡単なのはループで入れる。

r=最終行 + 1 to 最終行 + 150
cells ( r, 1)= date
next r

**779** (ｱｳｱｳｴｰ Sa22-gQbI) · 2020/08/03(月) 23:29:45.08

for とか、変数宣言とか抜けてるけど、そこはがんばって補完して。

2020/08/03(月) 23:40:17.65

forなんて使わなくてもいいんじゃね

Sub test()
Dim i As Long
i = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(i + 1, 1), Cells(i + 150, 1)) = Date
End Sub

**779** (ｱｳｱｳｴｰ Sa22-gQbI) · 2020/08/03(月) 23:44:27.96

なるほど、それはそうだ。
失礼しました。

2020/08/04(火) 00:12:45.83

>>780
ありがとうございます！
あとは自力で頑張ってみます！

2020/08/04(火) 00:17:38.16

>>781
解答ありがとうございます！
1つの答えに対し複数のコードがあるって面白いですね！
初歩的なコードが書けるよう頑張ってみます。

2020/08/04(火) 00:51:16.26

そういう感想が出るってことは、答えを出すための手順を全く自分で考えたことがないのでは…

2020/08/04(火) 01:06:00.92

>>784
死ねよ

2020/08/04(火) 02:59:27.48

RNDやRAND関数は疑似乱数だから公平じゃない
そもそもコンピューターで乱数を生成するのは不可能

知識としては知ってるけど、代替手段を考えるのも面倒だしみんなRNDを使ってるから別にいいんだよ、ってこと

2020/08/04(火) 05:51:29.11

そのための乱数シードってのがある

2020/08/04(火) 06:07:40.64

同じシード与えたら同じ乱数列吐くよね。
で？何のためにあるって？

2020/08/04(火) 06:08:47.59

もう最大限のことも自分で考えない馬鹿には答えなくて良くね？
多分何度も聞きに来るぞ

2020/08/04(火) 06:09:01.26

最低限ねw

2020/08/04(火) 06:22:53.51

>>789
同じシードを与えないためだろ、使い方知らんのか？

2020/08/04(火) 07:55:02.77

>>787
コンピュータという広域の括りであれば
乱数を発生させるのは不可能ではないよ

2020/08/04(火) 09:23:33.17

数式で「真の乱数」を発生させることは不可能でしょ
何かしらの雑音を外部から取り入れるならあり得るけど

しかし真の乱数と区別できない疑似乱数というのはある
たかだか数万個の乱数を発生させる程度なら普通にRANDで構わんよ
コンピューター空間内（例えば64bit)なら32bitの疑似乱数を発生させ得る

2020/08/04(火) 09:36:35.85

excel起動時のシードが同じだから変えたほうがいいとおもう

2020/08/04(火) 12:47:00.19

>>794
数式で真の乱数を発生させることが出来ないのは多分そう。ただ、それでも真の乱数は発生出来る。

ただ、かなり特殊な環境で言語的にもかなり特殊なのでVBAだけやってる人にはまぁ分からないだろう。

ただそれは、一度世界で最も出回ったハードであり言語でもあって、それでは乱数を求めるのに至極当たり前の方法でもあったから
このスレにいるおじいちゃんにはひょっとして知ってる人もいるかも知れない。

2020/08/04(火) 12:54:38.56

乱数こそこのスレで教えてくれた最高の武器
15個～20個しか評価してないデータを50個に乱数使ってn増ししてるわ
プロシージャ名は乱れ雪月花にしといた

**デフォルトの名無しさん** (JP 0H62-3SEW) · 2020/08/04(火) 13:32:34.79

学術の巨大掲示板群 - アルファ・ラボ ttp://x0000.net
数学物理学化学生物学天文学地理地学
IT 電子工学言語学国語方言など

simulationライブラリで純粋な関数式プログラミングをする
UIライブラリ (C#, 2D) を作ったよ
連続と離散を統一した！
4Dエンジン
matrixのライブラリ
ある強力なFor関数
SQLライブラリ

☆ VM + ASM を書いた (C#, DX) * x86 ではない!
ttp://up.x0000.net/files/TSimulang.zip
☆ malloc / free を実装してみた (C#)
ttp://up.x0000.net/files/TMallocTest.zip

2020/08/04(火) 20:23:21.05

2016使ってるけど、いつの間にかSORTBYとかFILTERとか使えるようになってるのな。

2020/08/04(火) 20:58:22.81

そもそも乱数に偏りがあろうと応募者が狙った並びでない時点で機会は公平だけどな

2020/08/04(火) 21:08:04.57

決まった番号がでるとわかっているからその番号に身内を入れて置けるわな

2020/08/05(水) 07:02:57.17

>>797
偏差値低くね？

2020/08/06(木) 11:58:31.88

乱数で疑似データをつくるとばらつきが均等になるから偏差を求めたときに不自然に広がるよね

2020/08/06(木) 13:28:48.79

乱数２つ使うといいぞ

2020/08/07(金) 19:45:16.94

スレチだったらｽﾏｿだけど、excelのスピル？のせいで、ver違うexcel間で共有してたファイルが勝手に配列やら@やら数式が変換されてバグってんだけど、これ無効にできないの？

**デフォルトの名無しさん** (ｱﾒ MM75-cUya) · 2020/08/07(金) 20:09:47.44

>>805
キミにピッタリな格言を贈ろう。
It is no use crying over spilt milk

2020/08/07(金) 20:45:55.98

できないの？

2020/08/07(金) 20:47:34.25

>>807
最近追加されたアレね
分からん。田中先生にでも聞いたら？

2020/08/07(金) 21:11:23.51

え・・・まじで無理なん？
おわってんな

2020/08/07(金) 21:38:31.44

最近の機能の不具合だから知らねってことやろ

2020/08/07(金) 22:08:09.38

旧バージョンの配列式に勝手に＠が付くのは仕様じゃねえの
旧verの {=a1:a3} が新verでは =@a1:a3 に変換される
新verのスピル式 =a1:a3 は旧verでは {=a1:a3} に

VBA的にはFormulaプロパティとFormula2プロパティを使い分けることによって新旧verどっちにするかを指定できる

**デフォルトの名無しさん** (JP 0Ha3-3Z56) · 2020/08/08(土) 13:26:33.40

パーフェクトExcelVBAの初版本の正誤表とサンプルコードってどこかにありませんか？

2020/08/08(土) 14:27:44.52

出版社に聞いたら

2020/08/08(土) 15:27:34.88

>>813
たし蟹

2020/08/09(日) 10:06:54.07

コレクションにaddしたシートだけを一発で選択するにはどうしたら良いですか？

2020/08/09(日) 10:50:04.26

>>815
addしたあとから調べる方法はないからaddする時に変数に覚えておくしかない

2020/08/09(日) 13:28:26.53

セルA1~A5に1~5が入っていて、5個のaverage=3ですが、
1) 複数の連続セルを選択することで同じ動作をするfunction
2) function 自作平均値(セルを1個指定する as ???, 上のセル数 as byte, 下のセル数 as byte) as double
で、=自作平均値(A3, 2, 2)　としたら同じ結果を得る関数
ってそれぞれどう書けばよいでしょうか。
値を直接入力する予定はなく、セルアドレスを取得できれよいかと思いますが、
方法がわかりません。
参考urlでかまいませんので教えてください。

2020/08/09(日) 13:55:27.58

>>817
自作の関数の中で、ワークシート関数とまったく同じ結果が欲しいなら、Application.WorkSheetfunctionを使うのが一番確実

参考サイトに基本的な作り方は書いてあるから、計算式の部分だけ自分で作り直せばいい
https://kokodane.com/tec3_3.htm

2020/08/09(日) 21:56:13.30

ITに疎いクライアントのために
デモ用のVBAが動作しているところ録画して見せたいのですが
そのために向いているツールとかありますか？

2020/08/09(日) 22:02:16.81

まずは標準のステップ記録ツールでダメな点を書けよ

2020/08/09(日) 23:01:18.40

>>817
Function 自作平均値(セルを1個指定するAs Range, 上のセル数, 下のセル数)
自作平均値 = Application.WorksheetFunction.Average(セルを1個指定する.Offset(-上のセル数).Resize(上のセル数 + 下のセル数 + 1).Value)
End Function

文章が意味不明なんだけどこんな感じでいいの？

2020/08/10(月) 01:01:45.89

>>819
Excelの画面をビデオ録画する操作はExcelを起動してから田+Alt+R
ただしVBEのウィンドウは開いても重ねても録画されないので、ワークシート上にマクロの起動ボタンを置いておく必要がある

2020/08/10(月) 06:44:41.63

>>819
win10なら標準で画面録画ツールあるよ
win+rだったかな

2020/08/10(月) 08:03:08.52

822で解決してる内容を間違って紹介するの巻

2020/08/10(月) 08:12:31.43

>>820の時点で普通は調べりゃわかる

2020/08/10(月) 08:49:14.88

>>815
そういう時はDictionaryにすれば良いんでね?

2020/08/10(月) 08:51:12.59

>>819
そのVBAを自動実行するVBAを書く。

2020/08/11(火) 02:22:17.18

VBAの知識は全く無いけど無駄な事は少しでも省きたい
そこで質問なのですが、複数の結合されたセルの値を一括でクリアしたい時
Range(“A1”).MergeArea.ClearContents
Range(“A2”).MergeArea.ClearContents
Range(“A3”).MergeArea.ClearContents
Range(“A4”).MergeArea.ClearContents
ひたすら続く

これを短くするにはどうすれば良いでしょうか？

2020/08/11(火) 02:42:12.83

A1:A1000

2020/08/11(火) 03:03:48.27

あーっとすみません
AはBと連結していますのでそれぞれが横に連結しています

2020/08/11(火) 03:10:45.62

>>830
死ね

2020/08/11(火) 03:24:29.64

>>828
まずセルの結合が無駄の元だから禁止するのが一番の早道

2020/08/11(火) 03:32:11.78

>>832
やはりそうですよねぇ
既存のフォーマットに寄せようと思うとどうしても結合セルが必要に...
色々試行してみます。

2020/08/11(火) 07:15:12.76

悪いけどこの程度のこと質問するようじゃ手でやったほうが早いと思う
該当セルを全部選択して右クリックから値のクリア

2020/08/11(火) 07:29:46.25

>>834
それをマクロの記録で記録するのが一番楽だわな

2020/08/11(火) 09:13:15.51

>>835
この程度なら、毎回やるにしてもマクロ使わずに手でやって余裕でできるレベルだろ

2020/08/11(火) 09:43:23.03

できるかできないかじゃないのがわからない奴

2020/08/11(火) 10:26:55.02

>>828
きったないコードの書き方になるが
Din i as Long
For i =1 to 最後の数
Range(”A” & i).以下略
Next i
で簡素化

2020/08/11(火) 12:32:13.53

range("a1:最後").value=emptyじゃだめなのか

2020/08/11(火) 12:34:29.17

>>828
Dim i 　　　As Long
Dim lngMaxRow As Long

'/// 最終行取得
lngMaxRow = ActiveSheet.Range("A65000").End(xlUp).Row

'/// ループ処理
For i = 1 To lngMaxRow
ActiveSheet.Range("A" & i).MergeArea.ClearContents
Next i

2020/08/11(火) 14:42:39.58

.Clear と .ClearContents と .Value=Empty の違いがわからん
これをマウスやキーボードで再現する方法も
たとえばセルを選択してDeleteキーはどれに相当？

2020/08/11(火) 15:03:15.53

Clearはセルに入力されてる数式や値、そして設定されてる書式もすべて消す
.ClearContents は数式と値のみクリア、書式は残る
.Value=Emptyはニュアンスが逆で"何も無いを入力"、つまり消す。.ClearContentsと同じ

deleteキーは.ClearContentsや.Value=Emptyと同じ

2020/08/11(火) 15:15:55.64

不規則な結合セルがあると .MergeArea.ClearContentsはエラーになるけど .Value = Emptyなら動くから>>828はRange("A:A").Value = Emptyの一行で解決ってこと

2020/08/11(火) 15:48:36.99

foreachやればええやん

2020/08/12(水) 02:04:20.40

>>834-844 ありがとうございます！
皆さんが言うように全くの知識ゼロですが色々検索してなんとか思った通りのボタンが完成しました。
しかし全角やらスペースやら分かり辛いプログラムって難しいですね...

2020/08/12(水) 20:55:54.61

赤線先生が手取り足取り教えてくれるのに何が不満だと申すのかこのガキは

2020/08/12(水) 23:02:53.31

n行ごとにデータを拾って少し文字列等を足してからほかのシートに貼り付ける、
という作業を現在For文でやっています。行数が多いと多少時間がかかることも
あるので、配列でできないかと思い試してみたのですが、「n行ごと」の部分が
うまくいきません

Dim tmpArr(), tmp As Variant
Dim rowIndex, colIndex As Long

tmpArr = Worksheets(1).Range("A3:C101").Value
For rowIndex = 1 To UBound(tmpArr) - 2 Step 3
For colIndex = 1 To UBound(tmpArr, 2)
tmp = tmpArr(rowIndex, colIndex) & "結合データ"
tmpArr(rowIndex, colIndex) = tmp
Next
Next

Worksheets(2).Range("A3:C35").Value = tmpArr

上記の場合3行ごとに元の値に「結合データ」という文字列を足すことはできて
いるのですが、貼り付け先のシートに全行貼り付いてしまいます
3行ごとの場合に要らない2行目、3行目を除外して
Sheet1 1行目→Sheet2 1行目、Sheet1 4行目→Sheet2 2行目…
というように貼り付けるにはどこを変更したらいいでしょうか

貼り付けたあと要らない行を削除してみたのですが、却って時間がかかって
しまったため、それ以外の解決方法があれば教えてください、よろしくお願いします

2020/08/12(水) 23:09:52.81

>>847
tmparrの要素数見てみ
もう1つ別に、貼り付け用のVariant型配列用意して新しく格納していかないとそりゃ空白行できるよ

それと、stepよりmodで条件分岐スキップさせる方がスマートじゃないかい

2020/08/12(水) 23:38:22.31

>>847
こういうこと
あと、Dimの書き方もこうしないとだめ

Sub Macro1()
　　Dim tmpArr() As Variant, tmp As Variant
　　Dim rowIndex As Long, colIndex As Long
　　Dim arrIndex As Long　　' 配列用のインデックス

　　tmpArr = Worksheets(1).Range("A3:C101").Value
　　arrIndex = 0
　　For rowIndex = 1 To UBound(tmpArr) - 2 Step 3　　' セルアドレスは3ずつ増やす
　　　　For colIndex = 1 To UBound(tmpArr, 2)
　　　　　　tmp = tmpArr(rowIndex, colIndex) & "結合データ"
　　　　　　tmpArr(arrIndex, colIndex) = tmp
　　　　Next
　　　　arrIndex = arrIndex + 1　　' 配列のインデックスは1ずつ増やす
　　Next

　　Worksheets(2).Range("A3:C35").Value = tmpArr
End Sub

2020/08/13(木) 00:14:44.45

>>849
その書き方だとRowが大きいやつ排除されずに新しいシートに貼り付けられません？
それともC35までで出力は終わりだからいいんですかね？
メンテナンスしづらいかと思います

2020/08/13(木) 00:25:56.44

>>850
そういう時はセル範囲を変数にして、どっちかのインデックスを3で割るか掛けるかすればいいんだよ
それならメンテは一箇所で済む
データの範囲をxlUpとかUsedRangeなどで調べたりテーブル化すれば全自動にもできる

2020/08/13(木) 01:36:11.66

>>849
ありがとうございます
最初インデックスが有効範囲にないとエラーがでたので「arrIndex = 0」を
「arrIndex = 1」に変えたらできました
配列なので0から始まるということなのかと思ったんですが、1にしても
大丈夫なんでしょうか？出力した結果はForのものと同じだったので
貼り付け後に1行ずれるということはなさそうなんですが…

しかし確かに速いですね、配列
データを5000行×3列にしてテストしたら
For 約2.7秒
配列約0.03秒
といった感じでした

>>850,851
コピー元のセル範囲は変数にして見出し行引いてnで割ったりして貼り付け先の
範囲を出してます
今回は決まった数字の方がいいかなと思って99行を3行ごとで33行、みたいに
してみました

2020/08/13(木) 01:59:57.87

>>852
tmpArrにセル内容入れた時のインデックスを確認すればわかる

2020/08/13(木) 11:07:02.86

環境
windows 10 64bit
office 365 64bit
string型変数strにスペースを埋める単純なコードです。

Sub test()
Dim num_i As Integer
Dim num_l As Long
Dim num_ll As LongPtr
Dim str As String

Debug.Print "integer"; Len(num_i); TypeName(num_i)
Debug.Print "long"; Len(num_l); TypeName(num_l)
Debug.Print "longptr"; Len(num_ll); TypeName(num_ll)

num_i = 16: num_l = 16: num_ll = 16

str = Space(num_i): Debug.Print ">>"; str; "<<"
str = Space(num_l): Debug.Print ">>"; str; "<<"

''動かない
'str = Space(num_ll): Debug.Print ">>"; str; "<<"
''動く
str = Space(CLng(num_ll)): Debug.Print ">>"; str; "<<"
End Sub

このように64bitに対応していない組み込み関数って結構あるんのだろうか・・・
いつからか忘れたけど、 64bit推奨しているなら、これぐらい対応しろよ、毎糞がぁぁあ
excelの64bit環境って現状こんなものなの？

2020/08/13(木) 11:30:36.17

>>853
インデックスを確認したところ
rowIndex 1→1→1→4→4→4→7→7→7
colIndex 1→2→3→1→2→3→1→2→3
arrIndex 1→1→1→2→2→2→3→3→3
というように変化していきましたので大丈夫そうです、ありがとうございました

2020/08/13(木) 11:34:45.35

コンパイルエラーじゃねーか、何を問題にしているのかわからん

2020/08/13(木) 12:50:45.70

試してないから知らんけど
64ビット長の文字列とかサポートされてないから当然だと思うけどな

つか今のEXCELついに64ビットが規定になったのか

2020/08/13(木) 13:00:55.28

>>855
いや、なんでインデックス0だとエラーになるかって話・・・

2020/08/13(木) 14:08:49.69

>>858
あ、そっちでしたか
エラーが出た状態のそれぞれのインデックスは
rowIndex 1
colIndex 1
arrIndex 0
でしたので、
tmpArr(arrIndex, colIndex) = tmp
のところが
tmpArr(0, 1) = tmp
になってエラー、ですかね？

2020/08/13(木) 14:16:56.76

>>859
tmpArrにインデックス0は存在しないのに指定するからエラーが出ていて、セル範囲を代入した時は1から始まることを知らなくても変数の状態をチェックすればすぐわかることだが、もしかしてローカルウィンドウとかを知らないパターン

2020/08/13(木) 14:35:55.00

>>860
あんまり使いこなせてはいないですがローカルウィンドウは表示させています
849さんが教えてくれたコードの中でarrIndexを「0」に指定しているのにはなにか
理由があるんじゃないかと思ったんですが、書き間違いとかでしょうか？

2020/08/13(木) 14:49:25.52

>>861
意図は知らない

2020/08/13(木) 18:33:06.86

>>861
そういうのスルーできないとこの先進まんよ

2020/08/14(金) 07:43:46.87

VLOOKUPについての質問なんですが、

検索キー　検索範囲1 検索範囲２　　　　産地
りんご　　　　りんご　　　　　ぶどう　　　　　　岡山産
　　　　　　　　みかん　　　　りんご　　　　　　岡山産
　　　　　　　　いちご　　　　　もも　　　　　　　岡山産
　　　　　　　　りんご　　　　　ぶどう　　　　　　山梨産
　　　　　　　　みかん　　　　　なし　　　　　　　山梨産
　　　　　　　　いちご　　　　　りんご　　　　　　山梨産

りんごから別セルの検索範囲１を見た後に検索範囲２を見にいって産地の戻り値を得る関数を作りたいのですが、
検索キーのヒットで産地が重複した場合は表示を１度だけにして、期待する結果として「岡山県　山梨県」とだけ得られるような関数を作りたいです
VLOOKUPを＆で繋げると「岡山県岡山県」と列の一番初めの戻り値しか得られません
VLOOKUP以外でもやり方があれば教えて頂ければ助かります

2020/08/14(金) 11:35:06.45

>>864 マクロ作りました。
最大100件までヒット可能です。
長くなって申し訳ありません。
ご参考までに。（１/３）

Option Explicit
'/// 変数定義
Public j As Long 'ヒット件数
Public strArray(99) As String 'ヒットした産地の配列

Sub Main()

Call 初期化
Call 検索処理("B", 2)
Call 検索処理("C", 1)
Call メッセージ

End Sub

2020/08/14(金) 11:35:39.43

Sub 初期化() '///グローバル変数の初期化　　（２/３）
'/// 変数定義

Dim i As Long 'カウンタ
j = 0
For i = 0 To 99
strArray(i) = ""
Next i
End Sub

Sub 検索処理(pCol As String, pMovNum As Long)
'/// 変数定義
Dim i As Long 'カウンタ
Dim lngMaxRow As Long '処理数
Dim strKey As String '検索値
Dim strSanchi As String 'ワーク変数

'/// キー
strKey = ActiveSheet.Range("A2").Value
'/// 処理数
lngMaxRow = ActiveSheet.Range("B1000").End(xlUp).Row
'/// ループ
For i = 2 To lngMaxRow
If strKey = ActiveSheet.Range(pCol & i).Value Then
strSanchi = ActiveSheet.Range(pCol & i).Offset(0, pMovNum).Value
If 重複チェック(strSanchi, strArray()) = False Then
strArray(j) = strSanchi
j = j + 1
End If
End If
Next i
End Sub

2020/08/14(金) 11:36:01.63

Function 重複チェック(pSanchi As String, pArray() As String) As Boolean（３/３）
'/// 変数定義
Dim i As Long 'カウンタ

'/// ループ
For i = 0 To 99
If pSanchi = pArray(i) Then
重複チェック = True
Exit Function
End If
Next i
重複チェック = False
End Function

Sub メッセージ()
'/// 変数定義
Dim i As Long 'カウンタ
Dim strAnswer As String 'ワーク変数

strAnswer = strArray(0)
'/// メッセージ出力
For i = 1 To j
If strArray(i) <> "" Then
strAnswer = strAnswer & "," & strArray(i)
End If
Next i
'/// 1件でもあれば表示
If j > 0 Then
MsgBox (strAnswer)
End If
End Sub

2020/08/14(金) 12:26:16.36

Dim obj As Object
Dim rng As Range

Set obj = CreateObject("Scripting.Dictionary")
For Each rng In Range(検索範囲)
　If rng.Value = Range("a2").Value Then
　　With Cells(rng.Row, "d")
　　　If obj.exists(.Value) = False Then obj.Add .Value, 0
　　End With
　End If
Next
Range("e1").Resize(obj.Count).Value = WorksheetFunction.Transpose(obj.keys)
Set obj = Nothing

**865～867** (ﾜｯﾁｮｲ 4290-p5K4) · 2020/08/14(金) 12:40:48.74

>>868
おお！流石です。

2020/08/16(日) 00:23:38.07

>>864
構造体使えば簡単に作れそうだけどね。

2020/08/16(日) 13:39:49.06

質問しないで自力でできるようになるのがまず先だろう

2020/08/19(水) 20:44:10.60

スレ全否定で草

2020/08/20(木) 01:09:34.68

シート1のプルダウンから選択した結果(テキスト)を
シート2の任意のセルに表示させるにはどうすればいいですか？

2020/08/20(木) 07:05:04.54

リンクするセルに指定しておけば

2020/08/24(月) 18:59:52.50

A列には数字を手入力
B列には●があったりなかったり

B列に●がある場合A列に数字を（例えば100）入力するとメッセージボックス？がでて
「●があるから入力しちゃ駄目よ！どうしても"100"を入力したいの！？」
「OK」「キャンセル」
みたいにしたいんですがどーすればいいですか？

2020/08/24(月) 19:07:40.74

Worksheet_Changeイベントを使えばいいです

2020/08/24(月) 19:26:54.83

>>875
データの入力規則のエラーメッセージ

2020/08/24(月) 20:16:24.02

1を入力した時点でメッセージボックスが出ると思うんだが10まではセーフで100だと確認のほうが良いのか