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/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

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

2020/08/25(火) 14:23:43.06

タブとか改行（CR+LF）とかバイナリデータが混ざっているとか機種依存文字とか
文字コードの違いとか色々あるからな

2020/08/25(火) 14:28:30.69

まあ一番怪しいのは、文字コード関係でテキストがちゃんと読めてない
ちゃんと読みこんだデータのバイナリ確認してみろ

2020/08/25(火) 14:43:48.86

line inputはSJISで読み込む
バイナリ確認しなくても元ファイルがSJISであればそれでいい

2020/08/25(火) 15:50:41.61

すみません892です
デバッガで見たら
全部の行で1を返してました
つまり全行、先頭は\マークみたいです
1文字目は無視するようにしたら
意図通りの動きになりました

2020/08/25(火) 16:00:19.40

>>909
それ完全にその場しのぎでしかないから
他の担当者が後々困る絶対

2020/08/25(火) 16:23:37.10

先頭が\かどうかすらも確認しないのか…

2020/08/25(火) 17:36:53.32

VBAからPowerShellで外部コマンドを実行して出力内容を加工したいのですが、うまくいきません
現在は、Execの引数に下記のように書いてます
powershell -Command "& Invoke-Expression 'コマンドのパス\コマンド.exe' -option1 -option2 "foo bar" >>'一時ファイル.txt'"

どうしたらよいでしょうか

2020/08/25(火) 18:00:08.63

>>909
1件目でも入っているのかな？

2020/08/25(火) 18:12:08.06

テキスト全行読み込みしてsplitしてる説
んで\rでsplitして\nが先頭に来てるんじゃね

2020/08/25(火) 20:46:10.83

>>912
Exec("powershell -Command Invoke-Expression 'コマンドのパス\コマンド.exe' -option1 -option2 'foo bar' >> '一時ファイル.txt'")

2020/08/25(火) 20:53:13.85

shell でいいのにわざわざ外部オブジェクト使う人っているよね

2020/08/25(火) 21:28:28.78

>>914
改行コードは別に文字列としての\が入ってる訳じゃないだろ

2020/08/25(火) 22:12:02.97

>>915
ありがとうございます
もう一点よろしいでしょうか
標準出力にUnicodeで吐き出すコンソールアプリの出力を、Wscript.Shell.ExecのStdOutを使用して読み込みたいのですが、文字化けします
個人情報を扱うのでできれば一時ファイルを作らずに作業したいのですが、Unicodeの標準出力を直接読み込む方法は無いでしょうか

2020/08/25(火) 22:59:12.42

>>899
>>903なるほど特殊文字が16進数表記で￥マークってことか>>909
〒〒 \03012 郵便マーク

2020/08/26(水) 01:37:45.74

>>918
スレチだろ、厚かましい

2020/08/26(水) 02:00:19.04

>>918
オプションなどで、文字コードを指定できないの？

2020/08/26(水) 02:56:47.46

これから新規に作る形式の文字コードは、UTF-8とGB18030の二択じゃないでしょうかね。

2020/08/26(水) 11:13:08.29

>>921
調べたところわかりませんでした
Unicodeの文字が欠落するのでSJISに変換して流すのも避けたいです
一晩考えてPowerShell側でBase64エンコードしてからVBA側でデコードしてみようと考え直しました
失礼しました

2020/08/26(水) 11:25:02.41

ちなみに、Wscript.Shell.Execに渡せるコマンドラインって255文字までのような制限があるのでしょうか
コンソール側で動いたコマンドラインがExecからだと動かなかったりするので

2020/08/26(水) 19:01:16.17

>>924
状況がわからないので超エスパー回答すると、
32bitじゃないと動かないものを64bitで実行しようとしている。

2020/08/26(水) 19:03:04.71

powershellか。
ごめん、関係ないわ。

2020/08/26(水) 19:09:15.11

>>926
ありがとうございます。
PowerShell上で動作を確認したワンライナースクリプトをExecで実行したら動かなかったもので、特に根拠があって疑ってる訳じゃないです。
こちらはPS1ファイルを用意して実行することにしました。

2020/08/26(水) 19:15:01.39

ところで、ADODB.Streamに書き込んだテキストをCSVとしてパースしてRecordSetに読み込む方法をどなたかご存知ないでしょうか
一時ファイルを保存すればいいのですがオンメモリでやれたらそちらの方がありがたいです
同じADODBクラスなので方法がありそうだなと思い調べてみたのですが…

2020/08/26(水) 20:35:45.89

>>928
厚かましいスレチはいい加減にしろ

2020/08/26(水) 20:40:23.56

ノイズが発生してるけど無視していいよ
知ってて答えたい人が答えると思うし

2020/08/26(水) 22:47:53.03

>>928
ちょっと意味が分からない。
そこまで知っていて出来ないというのは、どこで躓いているのか。

2020/08/26(水) 22:50:24.69

できないわけじゃない
1文字づつ解析とか馬鹿らしくてやってられないから一発でできる方法を聞いておる

2020/08/26(水) 22:52:29.85

一年他部署に応援行ってて戻ってきたが
その間に転属されてた人に

これがあれば一覧のデータを一気に出力できるからマクロの使い方だけは覚えてね
マクロとかわかる？とにかく使い方おぼえて
と言われた
俺が応援行く前に作って課に展開したマクロやんけ…

2020/08/27(木) 10:45:53.98

strconvで文字列"試験"をUnicodeに変換すると、変な文字に変換されるんだけど解決策ありますか？

StrConv(StrConv("試験",vbUnicode),vbFromUnicode)
の結果が、"試験"であってほしいんだけど、別のよくわからない文字に変換される

2020/08/27(木) 10:56:45.12

>>931
情報小出しですみません。

UTF8で標準出力に個人情報含むCSVを吐き出すコンソールアプリがありまして、それを一時ファイルを使用せずにVBAに読み込むのが目的です。

Wscript.Exec.StdOutを使用したところUTF8が文字化けしてしまい、PowerShell上でByte配列化→Base64エンコードして吐き出すことで、VBAでCSVのStringを得るところまではできました。

2020/08/27(木) 10:58:22.12

>>934
shiftjisで表示してんじゃないの

2020/08/27(木) 11:04:53.94

VBAでBase64デコードしたByte配列をADODB.Streamにバイナリ書き込み・UTF8読み込みすることでString変数に収めるところまでは行けました。
ここから、ADODB.Connectionを使用してADODB.Streamの内容をCSVとしてパースしたいのですが、ネットで見られるサンプルコードはファイルを読み込むものばかりです。
試しにADODB.Connection.Open ADODB.Streamとしてみましたが、うまく行きません。
現在ADODBのリファレンスに目を通していて、関係の有りそうなプロパティを見て回っているのですが、方法をご存知の方いらっしゃればご教示いただけるとありがたいです。

2020/08/27(木) 11:29:45.37

>>934
試験のバイナリと
よくわからない文字のバイナリ比較してみたら

2020/08/27(木) 11:35:18.54

WSH は、CP932 だけじゃないの？
もうWSHは、Microsoft もサポートしていないのでは？

WSH, VBScript のスレのスレ主、ピッコロ大魔王に聞けば？

漏れは、Ruby スクリプトをダブルクリックで起動したい場合に、WSHを使っているけど、
その際、Ruby側で、CP932・UTF-8 を変換している。
または、NKF というモジュールもある

2020/08/27(木) 11:58:06.46

>>934
そりゃそうだろ…
Unicode文字列をShift-JISだと思ってUnicodeに変換して、それを更にUnicodeだと思ってShift-JISに変換して何をしたいんだ？
やりたいのは
StrConv(StrConv("試験", vbFromUnicode), vbUnicode)
かな？

2020/08/27(木) 12:21:37.52

AddDllDirectory関数の引数にパスを渡したい
この関数の引数はUnicode文字列をである必要があるから、StrConvでUnicode変換してから渡す
変換がうまく行く文字だけのパスなら、AddDllDirectory関数は成功するが、変換がうまくいかない文字が含まれるパスだと失敗する

2020/08/27(木) 12:24:57.51

>>941
vbUnicodeのstrconvだけじゃいかんの

2020/08/27(木) 12:31:49.04

>>942
それが失敗するから、vbFromUnicodeでもう戻したら案の定変になってた

2020/08/27(木) 12:49:42.68

変換がうまくいく文字列
変換がうまくいかない文字列
それぞれ教えてよ

似たような流れ多い？

2020/08/27(木) 13:18:08.14

なぜうまくいかないというコードを出さないのか

とりあえずEXCEL関係ないしVBA何でもスレ行けや

2020/08/27(木) 13:21:23.95

・成功
　日本、解析、実
　あ~む、ア~ミ、濁音、半濁音

・失敗
　試験、行、め~ん、ム~ン、ゃゅょ、ャュョ

2020/08/27(木) 13:28:24.20

strconvが失敗するって時点で、何かの勘違いがあるとしか思えない