Excel VBA 質問スレ Part63

2019/09/16(月) 19:34:59.04

!extend:checked:vvvvv:1000:512

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

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

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

2019/10/05(土) 22:49:37.62

特定フォルダの中のファイル名を取得したくて、A1にファイルパスが書いてあってあったとき
Dim　fname As String fname = "DirSample.xlsx"　
の"DirSample.xlsx"の部分をA1のパスにしたいのですが、どうやった良いですか？

2019/10/05(土) 23:05:00.52

fname = range("a1").value & "¥DirSample.xlsx"　

2019/10/06(日) 00:21:58.24

>>164
=type(a1)
書式を文字列にしてからやってみ

2019/10/06(日) 01:50:18.38

>>5
このQueryTablesなんですけど、セル内改行を含むCSVを読み込むにはどうしたらいいですかね？
改行コードをいじってみたりしたけど駄目でした

2019/10/06(日) 08:08:24.51

>>171
CSVで「セル内改行」？
意味が分からん

2019/10/06(日) 09:32:50.86

>>172
Excel セル内改行 csv
でググっておいで

2019/10/06(日) 12:19:22.67

>>171
Excelのセル内改行コードとテキストファイルの改行コードは違う

2019/10/06(日) 13:05:06.23

>>171
CSVのパーサーを自分で書けばいいです

2019/10/06(日) 13:10:14.83

>>171
QueryTableをコード内でAddするんじゃなくて、予めPowerQueryでセル内改行の処理を埋め込んだ接続を作っておいて、コード実行時にデータの読み込みとテーブル出力だけすればいいんじゃないの

2019/10/06(日) 21:50:03.52

>>174
セル内改行をLFにしたり色々試したんですけど駄目でした
結局一時ファイルを作って開いてからコピペという泥臭い処理に…

2019/10/06(日) 21:50:35.79

>>176
PowerQueryですか
調べてみます

2019/10/06(日) 22:04:38.99

VBAある表のデータで行と列が一致する列の値を返すコードを書きたいのですがどうしたらいいでしょうか？
行だけならVLOOKUPで行けると思うのですが列になるとどうしてラいいのかわかりません。
該当するセルの値を返したいです。

2019/10/06(日) 22:26:25.54

rangeをfor eachで巡回すればいいんじゃないの

2019/10/06(日) 22:28:18.73

>>179
行と列が一致するってどういうこと？

行インデックスと列インデックスが一緒ってこと？

それとも、行と列を特定するための条件が個別にあって、それらの条件を満たすってこと？

2019/10/06(日) 22:57:39.08

>>177
コピペするならファイル作らなくてもクリップボードに送ってテキスト取り出し貼ればセル内改行も反映される

2019/10/06(日) 22:59:41.24

>>179
何がしたいのか知らないけどVLOOKUPの横版ならHLOOKUP

2019/10/06(日) 23:02:28.07

>>181
後者です。
シートに参照する表がありユーザーフォームで作った入力表がコンボボックスとなり
一つ目のコンボボックスが行とリンクしていて、二つめのコンボボックスが列と連動して言いて
行と列の一致した値をテキストボックスに表示させたい。
一つだけならVLOOOKUPでできるのですが表となると私の力だと全く思い浮かびません。
力といってもちょっと基本がわかって参考書とインターネットの中から使えそうなコード
を引っ張ってきて使えるように修正するくらいで一から工程を考え出す事は難しいです。

2019/10/06(日) 23:09:13.37

ワークシートのchangeプロシージャで
A1:F5　 A6:E6　A7:I105　　に変更が加わったとき　Range("F6") = Date　をさせたいのだけど

If Intersect(Target, Union(Range("A1:F5"), Range("A6:E6"), Range("A7:I105"))) Is Nothing Then

ではうまくいきません。どなたか教えてください。

あと、セルに文字を入力するとき、対象となるセルを選択し、文字を入力したら、別のセルに入力途中の文字が表示され、Enterをおしたら対象となるセルに反映されました。
これもどうしたら普通通りになりますか？

2019/10/06(日) 23:14:57.48

>>１83
しいて言うならVLOOKUPとHLOOKUPの合わせる感じ？

2019/10/06(日) 23:34:28.13

>>185
うまくいきませんとは？
一部だけ反応？反応の範囲が逆？

2019/10/06(日) 23:45:45.30

>>186
関数でできないからVBAでやろうとしてるだけなら関数でできる
表がA1:D5だった場合、
=VLOOKUP(列の条件,A1:D5,MATCH(行の条件,A1:D1))

2019/10/06(日) 23:45:57.04

Ruby では、2次元配列[ 行 ][ 列 ]で、そのセルを指せる。
ただし、行列のインデックスは、0 から始まる

require 'csv'

str = <<"EOT"
1,a,x
2,b,y
EOT

p table = CSV.parse( str )
#=> [["1", "a", "x"], ["2", "b", "y"]]

p table[ 1 ][ 2 ] #=> "y"

2019/10/07(月) 07:20:54.28

>>184
重複無しなら、コンボボックスの値が行、列の並びと
同じになるように設定して
listindexを取得
offsetで値を取るとか

2019/10/07(月) 08:45:07.25

見出し行の値をkeyにしてrowを取り出すdictionaryを作る
列も同じ様に配列を作る
で、コンボボックスで選択した値で配列から行列番号を取り出す

49 (ﾜｯﾁｮｲ 7991-rYlP) · 2019/10/07(月) 13:22:02.42

>>177
まず考えるべきはバイナリエディタで該当ファイルを開いて、どういう構造になってるのかを確認することだよ。

2019/10/07(月) 19:22:21.76

>>185
Not抜けてる

2019/10/07(月) 21:18:32.84

>186 change自体が動きません
>193 マジッスカ！どこに入れたらいいですか？

2019/10/07(月) 21:59:12.85

>>194
ifのあと

2019/10/08(火) 00:24:14.75

〉〉184です。教えてもらった通りVLOOKUPとマッチ関数で作ってみたのですが
うまく言いません。通常のエクセルの状態ではやりたいように動作したのですができればVBAで動作できるようにしたいです、

Sub 行と列()
Dim tateya As String
Dim gouki As String
Dim gouki2 As String
Dim siriaru As String
Dim test As Worksheet
Set test = Worksheets("テスト")
tateya = test.Range("a1")
gouki = test.Range("a2")
haihhun = "-"
gouki2 = tateya & haihhun & gouki '号機
Dim siri As Worksheet
Set siri = Worksheets("シリアル管理")
Dim hanni_seru As Variant '範囲指定はバリアント型を使用する事
hanni_seru = siri.Range("D1:L39")
'VLOOKUP(列の条件,A1:D5,MATCH(行の条件,A1:D1))
siriaru = WorksheetFunction.VLookup(gouki2, hanni_seru, WorksheetFunction.Match("H1", "D1:L1"))
MsgBox siriaru
End Sub

2019/10/08(火) 02:02:42.66

>>196
たぶんMatchの行条件がおかしいけど、どんな問題が起きてるのか分からないから何とも言えない

それから、haihhunとかsiriaruとかhanni_seruとか変数の名前がちょっと酷い
HyphenとかSerialとかとかCellとか英語をきちんと使う意識を持つのが上達の近道だと思う

2019/10/08(火) 07:19:14.43

〉〉196
その前にまず安価というのはだな・・・

2019/10/08(火) 07:24:09.57

別モジュールに書いたprivate subをcallする方法ってありませんか？
publicにするしか無いでしょうか

**デフォルトの名無しさん** (ｱﾒ MM11-pTrw) · 2019/10/08(火) 07:37:46.07

〉〉196です。
変数名もそんなルールが？
なんとなく日本語はよくないなーと思って自分で分かるローマ字で表記しようと思ったんですが、後ダメな理由がマッチ関数にワークシート指定してないからかと推察。後で試します。

2019/10/08(火) 07:52:03.60

無理に英語やローマ字にするくらいなら日本語でいいと思うけど
あとrangeだけじゃなくてcellsも覚えれば目的達成するんじゃないか
cellsのrowとcolumnで行と列を取得出来るからね

2019/10/08(火) 08:16:32.23

>>199
ただ単にマクロ一覧に表示されないようにしたいだけなら、Private SubをPublic Subに変えた上で、そのモジュールの先頭にOption Private Moduleと書き加えれば良い

Publicにするのが嫌なら、呼び出したいPrivate Subが定義されたモジュールにコールバック用の引数付きでPublic Subを定義して、呼び出したい任意のPrivate Subを引数で指定して呼び出せるようにする
それも嫌ならクラスを作ってPrivate Subをメソッド化する

どちらもコーディングや保守の工数は大して変わらない

2019/10/08(火) 20:03:58.77

>>202
＞ただ単にマクロ一覧に表示されないようにしたいだけなら、Private SubをPublic Subに変えた上で、そのモジュールの先頭にOption Private Moduleと書き加えれば良い
まさにこれです！privateにしなくても出来たんですね、勉強になりました。ありがとうございました

他の方法も勉強になりました

2019/10/08(火) 20:07:56.35

>>200
決まった名前ルールは無い
人それぞれに拘りがあるだけ
たぶんみんなに共通する拘りは分かり易さ
他人が見て混乱しないか、半年後に自分が見てすぐに理解できるか

2019/10/08(火) 21:30:26.55

>>200
ありがとうございます。
コメントつけまくって分かるようにしてます。
まぁ詳しい人が見たらめっちゃくちゃで分かりにくいと思うのですが、
自分用で自分が分かればいいので動くようにして少しづつ改良していきます。
多分、めっちゃ不具合あります。

2019/10/08(火) 22:01:56.51

データベースから原本のシートに転記する場合表内でのループは可能ですか？
但し、実際に転記する表の上下に題名や表題があります。
例えば1行から20行での表があり繰り返すデータが21行目なったら2ページめの1行目
にするという事は可能でしょうか？
出来なさそうなのでリストボックスで見やすく作ろうかと思ったのですが、印刷用でも
作成したいのでできるのであればご教授お願いします。

上だけなら、forの頭を指定して10番目ならfor i = 10TO 最終行
で行けると思うのですが表の最後の指定がわかりません。
ｆｏｒをネストすればできそうですが、それでも20番目でおわる形にする感じ？
21行目を2ページ目の1行目を指定するのは難しそう

2019/10/08(火) 22:10:00.62

伝え方って大事だよね

2019/10/08(火) 22:11:56.81

>>206
21行目になったら次の改ページ位置を貼り付け先にする

2019/10/08(火) 22:13:49.93

または上下の題名をヘッダフッタに指定して改ページを気にせず貼り付ける

2019/10/08(火) 22:46:38.31

>>194
出来ました。大変ありがとう😊

2019/10/10(木) 21:20:24.18

たまにpowershellの話題出るから聞きたいんだけどpowershellってUWSC見たいなことできるの?

2019/10/10(木) 21:36:10.47

特化してるUWSCみたいに簡単ではないけど
がんばればできる

2019/10/10(木) 21:48:01.84

頑張れば何でもできるだろうな。

VBSではWindowsAPIが使えない!　→　裏でこっそりエクセル起動すれば使えるじゃん。
VBAではjavascriptが使えない!　→　ExecuteThisJavaScript使えば出来るじゃん。

殆ど屁理屈だけど。

2019/10/10(木) 22:44:29.41

要は徒歩でも世界一周できますよねってこと

2019/10/10(木) 23:00:49.96

ジョジョ、逆に考えるんだ、『vbaの下に便利APIのExcelやAccessが付いているのさ』と考えるんだ

49 (ﾜｯﾁｮｲ f791-mVPP) · 2019/10/11(金) 15:44:34.63

>>211
>>212
>>213
出来るぜ。
－応ここVBAのスレだから言うけどVBAでも全部できる。
今流行りのRPAがやってることは全部出来る。

49 (ﾜｯﾁｮｲ f791-mVPP) · 2019/10/11(金) 15:50:38.03

ただ、VBSでWindowsAPIは苦肉の策というか、敬遠したい気持ちもある。
まっ、VBSが最適な話なら結局使うけどね。

VBAでJavaScriptも同様だね。
こっちの方が敬遠したい。
まずはVBAだけで何とかならないかを考える。

2019/10/11(金) 17:59:50.09

>>216
まじかー
vbaでやれるっていうことはwinapiを使うって事だよね？
スクリーン上の指定座標の色の識別とかもできたりするの?

2019/10/11(金) 18:32:21.51

特定のアプリのウィンドウに対してキー入力するとかVBAでやってるよ

2019/10/11(金) 20:26:26.20

Cells(1,"A")と書かず、判りにくいCells(1,1)と書くのはなぜ？
ネットの記述とか見ててもCells(1,"A")と書いてるの見たこと無い

2019/10/11(金) 20:52:13.81

マルチかよｗ

2019/10/11(金) 22:18:32.49

はわわ～

2019/10/11(金) 23:28:47.56

RPAは所詮VBAに勝てないか
っしゃああ

2019/10/12(土) 02:44:28.24

forでまわせんだろうが
個人的にrangeをなぜ使うかの方がわからん

2019/10/12(土) 02:45:37.39

ああ、cellsで表現できるのをrangeで表現するのが解らんってことね

2019/10/12(土) 06:05:16.65

Range("B2:E6").Interior.Color = 65535
こんな場合は使うかな
見た目というかデータに影響を与えない部分はrangeの方が読みやすい、気がする

2019/10/12(土) 06:55:20.10

朕も同じくだわ

2019/10/12(土) 11:15:14.28

Cells(1, "a")と書くならRangeを使えばいい

**デフォルトの名無しさん** (ｽﾌｯ Sdbf-hY6M) · 2019/10/12(土) 11:19:09.64

マルチする迷惑な質問者多いな…

2019/10/12(土) 11:58:45.65

繰り返すときCells(i, 1)よりCells(i, "A")の方が判りやすいと思うけど

2019/10/12(土) 12:34:57.54

列方向に繰り返す時はどうするの

2019/10/12(土) 12:37:13.83

むしろexcel側でR1C1形式がデフォルトになれば良いのに

2019/10/12(土) 12:44:34.65

列は作りながら足したり順番変えたりする事が多いので最初からenum作って管理する
cells(i, cols.hoge)みたいにしてる

2019/10/12(土) 12:46:15.93

>>232
三四郎に対抗するためにこうなった
結果、シェアを勝ち取ったけど負の遺産として残ってしまったね

2019/10/12(土) 19:24:53.26

アホか

2019/10/12(土) 20:28:16.78

経験則から言うと、エクセルのシートを入力フォームや書類のフォーマットのように捉えてる人はRangeを使いがちで、配列やテーブルとして扱えるオブジェクトの塊だと捉えてる人はCellsを使う傾向がある

2019/10/13(日) 00:25:27.70

あるリストをユーザーフォームで絞り込んでそのユーザーフォームのリストボックスに
リスト内容を表示させたいのですがオートフィルタの表示はうまくいくってるのですがリストの表示
先がうまく表示されません。どうしたらいいでしょうか？
以下がコードです
private Sub kensaku_bo_Click()
'オートフィルタによる検索
Dim ws_kiki As Worksheet
Set ws_kiki = Worksheets("機器履歴")
Dim tateya As String '建屋番号
Dim gouki As String '号機番号
Dim gouki2 As String '建屋と号機の合計のセル
Dim haihhun As String
Dim myData As Variant
tateya = ken1.Value
gouki = ken2.Value
haihhun = "-"
gouki2 = tateya & haihhun & gouki '号機
ws_kiki.Range("A1").AutoFilter 3, gouki2
Dim lastRow As Long
With ws_kiki
lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
myData = .Range(.Cells(1, 1), .Cells(Rows.Count, 12).End(xlUp)).Value
End With
With list_ken
.ColumnCount = 12
.ColumnWidths = "50;80;50;50;50;50;50;50;50;50;50;50"
.List = myData
End With End Sub

2019/10/13(日) 04:36:36.74

変数で回す時はCells
セル位置が固定の時、範囲指定する時はRange
1行ごとにコード入力が楽な方を選んでる

2019/10/13(日) 09:40:37.82

>>237
myData(Range型)にはフィルター設定情報は入ってない
一行一行をRow.Hidden判定しながら範囲内ループさせて取り込むしかないね

2019/10/13(日) 09:49:37.23

間違えたmyDataは配列か
それでもフィルター情報は乗らないね
リストボックスの.RowSourceに範囲アドレス入れて連動させる方法あるけど
これフィルターも連動したっけ？

2019/10/13(日) 15:30:11.40

セルC5:C17に数値が入ってます（A）
セルF5:F21に数値が入ってます (B)
セルY5:Y25に数値が入ってます (C)

AとBの数値のうちCにない数値をセルO5から順番に下に抜き出したいんです

cを配列で格納するまでは出来たのですがそれ以降がわかりません
よろしくお願いします

初心者です

2019/10/13(日) 15:41:29.73

配列を検索すればいいだけでは

2019/10/13(日) 15:45:58.47

>>242
すみません
その配列の検索が上手くいかなくて…

**デフォルトの名無しさん** (ｽﾌｯ Sdbf-Sohv) · 2019/10/13(日) 16:03:13.56

配列よりコレクションの方が楽では

2019/10/13(日) 16:05:31.06

for i = 0 to ubound(配列)
if 配列(i) <> 検索したい値 then
どこかに出力
end if
next

こういう感じでいいんじゃないの

2019/10/13(日) 16:45:22.08

>>244
コレクションっていうのがあるんですね
調べてみます
ありがとうございます

2019/10/13(日) 18:24:09.39

普通のCollectionや配列よりも、Cの値をKeyにしてDictionaryオブジェクトを作る発想の方がいいと思う
Dictionary.ExistsメソッドでKeyの存在確認ができるからコードが簡単になる

2019/10/13(日) 20:43:23.01

Dictionaryは平均的なVBAerには理解できないから却下
他人に理解できないもん作るんならVBAを選ぶ意味がない

2019/10/13(日) 21:17:01.40

dictionary楽なのに

2019/10/13(日) 21:32:01.32

連想配列の事をdictionaryって呼ぶんだっけか
すごい便利だけど、シートあるんだからシート使った方がいいんでない？と思う
二次配列が目に見えて編集し放題とか楽ちんすぎる

2019/10/13(日) 21:38:36.38

みんなどうやってVBAそんなに詳しくなったの？

2019/10/13(日) 21:44:04.10

必要に迫られて作ってるうちに

2019/10/13(日) 21:48:31.98

休日まで費やして2000行くらいコード書いてるけどそろそろ嫌になってきた

2019/10/13(日) 21:50:43.69

そのうち、休日に働いて会社でのんびりするようになるよ
あんまり良くないんだけど、プログラムは家じゃないと集中して掛けないんだよな

2019/10/13(日) 21:54:03.90

>>254
あー、すごいよくわかる

2019/10/13(日) 22:03:41.31

>>248
平均的なVBAerってのがどんな層を指してるかちょっと分からないが、Dictionaryが理解できない人ってそもそも配列やCollectionも理解できないんじゃないの？

2019/10/13(日) 22:05:54.29

dictionaryの替わりにシートを使うという発想は無かったな

2019/10/13(日) 22:13:33.97

シートが可視化された配列だってのはその通りなんだけど誰でもGUIで簡単に編集できてしまう点が逆に弱点でもある
値の読み書きだけでイベントハンドラに制御が渡るからそもそも重いし

2019/10/13(日) 22:23:41.04

>>258
まぁねぇ。その辺は考えながらうまく付き合って行きたい所

2019/10/13(日) 22:44:44.65

連想配列ってどういうとき便利なの?
いまいち基本書の記述じゃメリットとか使い道とかがイメージできないんだけど

2019/10/13(日) 23:07:03.97

>>260
配列で数字の添え字じゃなくて好きなワードを使いたい時に
あとDictionaryの場合は検索がめちゃくちゃ速いから大量データの検索に

2019/10/13(日) 23:09:26.19

>>241
こういうこと？
Sub 検索()
Const COL_C As Long = 3
Const COL_F As Long = 6
Const COL_O As Long = 15
Dim nSetRow As Long
nSetRow = 5
Dim nRow As Long
For nRow = 5 To 17
If Not IsExistValue(Cells(nRow, COL_C).Value) Then
Cells(nSetRow, COL_O).Value = Cells(nRow, COL_C).Value
nSetRow = nSetRow + 1
End If
Next
For nRow = 5 To 21
If Not IsExistValue(Cells(nRow, COL_F).Value) Then
Cells(nSetRow, COL_O).Value = Cells(nRow, COL_F).Value
nSetRow = nSetRow + 1
End If
Next
End Sub

2019/10/13(日) 23:09:42.80

Function IsExistValue(ByVal pValue As Variant) As Boolean
IsExistValue = True
Const COL_Y As Long = 25
Dim nRow As Long
For nRow = 5 To 25
If Cells(nRow, COL_Y).Value = pValue Then
Exit Function
End If
Next
IsExistValue = False
End Function

2019/10/13(日) 23:20:25.51

>>260
Keywordが重複できないから必ず一意なデータセットができる。

2019/10/14(月) 00:48:24.78

2シートを連結する際、それぞれの各行をクラスインスタンスに代入＆コレクション化してるのですが連結方法に悩んでいます
例
Personクラス(pID, 氏名, 名字プロパティ, 生年月日,...)→Persons.Add Personインスタンス, str(pID)
Diaryクラス(dID, pID, 作成日, 本文,...)→Diaries.Add Diaryインスタンス, str(dID)
'出力
Dim d as Diary
For each d in Diaries
if(存在チェック) then xxx = Persons.Item(d.pID).名字
Next d

現在上記のように回してますが、より楽に書けるor固いor保守しやすい...実装があれば教えて頂きたいです
現状はプロパティの予測表示がされないのが微妙に使いにくいのと、そもそもコレクションにおけるkeyの動作をよく分かっていません

2019/10/14(月) 01:24:56.78

>>260
existsが便利
数数えたり、行番号取り出したり、キャッシュの管理に使ったり
for eachで中身を全部取り出そうとしたら投入順が保証されないのが少し残念

2019/10/14(月) 09:29:31.58

VBAでOPENしたファイルを引数で渡す事は出来ますか？
こんな感じで処理したいのですが、「#1」って変数では無いのでしょうか

Sub foo()
Open "TESTFILE" For Output As #1 ' Open file for output.
Call write_(#1,"content")
Close #1
End Sub

Function write_(filenumber, content)
Write filenumber, content
End Function