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

**デフォルトの名無しさん** (ｱﾒ 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だと確認のほうが良いのか

2020/08/24(月) 20:27:42.41

>>875だけどいい忘れてました
0を入力した場合はメッセージ出さずにそのまま0と入力
0以上（例えば200）を入力した場合は「●なんだから入力すんなボケ！どうしても"200"って入力したいの？」
「OK」「キャンセル」
みたいなね

2020/08/24(月) 20:34:28.35

OK押したらそのまま200がセルに入力される
キャンセル押したら""ね

2020/08/24(月) 20:35:32.06

馬鹿は条件を後で付け加える

2020/08/24(月) 20:36:54.05

>>881
誹謗中傷？

2020/08/24(月) 20:42:38.20

メッセージボックスはうっとおしいからセルの色を変えるぐらいのほうが喜ばれるよ

2020/08/24(月) 20:46:47.07

>>883
それでも入力する奴がいるんだもん
IF(B2:B10000=●,"入力すんな","")
これでも駄目

2020/08/24(月) 20:56:49.89

数字以外を入力したら？

2020/08/24(月) 20:59:38.82

>>885
数字以外入力しません

2020/08/24(月) 21:02:06.67

自主的にしないのなら入力規則をユーザ設定にしてエラースタイルを注意でいいじゃんかと思うんだが

2020/08/24(月) 21:19:01.81

>>887
そうか
やってみようかな
ちなみにVBAだとどう書くの？

2020/08/24(月) 21:58:37.53

●があるあったらダイアログ表示するだけ

2020/08/24(月) 21:58:57.09

●があったら

2020/08/24(月) 22:34:30.60

個性的な物を作りたいのなら図形を使えば色も形も自由自在に作れる

2020/08/25(火) 07:27:56.68

Win10で、VBAを作っています。
テキストファイルをLine関数で1行ずつ読み込み
読んだ行にてInStr関数で円マーク（\）を検索
しています。
InStr(1, 検索対象, "\")
と書いています。
しかし\が無い行で「ある」と判定されているようです。
\にはエスケープ文字が必要だからではないかと思いますが
どう書いたら良いかわからず途方に暮れています。
どなたかご教示ください。
（文字コードで指定すれば良いようですが
他の担当者が理解できない可能性もあるので
別の方法も把握したく存じます）

2020/08/25(火) 09:38:34.28

無いのにあると判定される行の文字列を教えて下さい

2020/08/25(火) 09:40:05.96

instr特に問題なさそうですが。
ウォッチ式の想定外時検索対象文字列は晒せないのですか？
instr使わずに1文字ずつ取るとか？

2020/08/25(火) 10:33:08.00

>>892
されてるようですじゃなくてちゃんと確認して

2020/08/25(火) 10:41:04.86

>>893
海砂利水魚の水行末\ｎ

2020/08/25(火) 11:01:09.56

>>896
次の人どうぞ

2020/08/25(火) 11:14:02.60

>>893
[壁]_･)_ｼﾝ・シンギュラリティ￥ｎ

2020/08/25(火) 12:47:56.13

>>893
会社の住所です
一文字目が全角郵便マーク
2文字目が全角スペース
三文字目から半角数字で郵便番号です。
\と半角ハイフンの間を取り出す処理を
書いたところ
郵便番号最初の3桁、
ハイフンの手前まで
が取り出されますので
全角スペースが\マークとして
扱われてると思ってます

2020/08/25(火) 12:58:49.60

>>893
「\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-
\と半角ハイフンの間を取り出す処理
を全角スペースが\マークとして扱われてると思ってます
\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-」

2020/08/25(火) 13:03:59.81

>>899
そもそもVBAにエスケープ文字とかないから
お前のコードがバグってるか、テキストがちゃんと読めてないかどっちかだろ

とりあえずコードと読み込んだデータ晒せ

2020/08/25(火) 13:39:05.50

>>899
その説明だとコード晒してもらわないとわからん
住所そのまま晒してって言ってるんじゃなくて
別の文字に置き換えていいから同じフォーマットで
処理前後でどうなってほしい所がどうなってしまうのか書いてもらわないとわからない

2020/08/25(火) 13:53:58.00

>>900
なんで想像するだけで実際の中身を確認ようとしないの

2020/08/25(火) 13:54:35.07

>>903は>>899

**デフォルトの名無しさん** (JP 0Hc3-n+O8) · 2020/08/25(火) 14:14:41.86

プログラムってのはなあ、思った通りには動かないもんなんだよ
いつでも書いた通りに動くだけ