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/01(火) 20:26:17.87

>>137
一度に全部やらず少しずつやるとか、挿入やめて別シートに貼り付けていくとか

2019/10/01(火) 23:46:40.82

vbaできる人は何歳くらいの方が多いのですか？

2019/10/01(火) 23:51:07.99

100才。
きんは100才100才。ぎんも100才100才。
もう死んじゃいましたけど。

2019/10/01(火) 23:51:51.43

いまは中曽根元首相が100才だよ。

**デフォルトの名無しさん** (ｽﾌｯ Sd7f-S/NQ) · 2019/10/02(水) 10:56:45.47

>>145
もう101歳になった

2019/10/02(水) 16:36:11.08

100才くらいなら健康でも1年後にもまだ生きてる生存率は50%くらいだよな。
そろそろ死神のお迎えがくるかな。

2019/10/02(水) 19:09:20.31

そんな事言ってる人に来るからな

2019/10/02(水) 23:17:24.03

直せるものもろくに直さず
超絶くそのまま世界で最も使われる言語の一つになってしまったＶＢＡに
いろいろ思うところがある

俺らがああしろこうしろと習ってることってどこまで本当なのか

2019/10/03(木) 04:52:33.28

人の言うことなんか信用するな
学校で教えることなんて、諸説あるうちの一つを紹介してるだけ
自分で直接見聞きしたことが真実に一番近い

**137** (ﾜｯﾁｮｲ 3d2d-PFwD) · 2019/10/03(木) 05:35:40.04

>>138
>>141-142
細かくコピペするよう変えたのですが、今度はメモリを3.5GBくらい使い始めました。
さすがにおかしいので調べた結果、コピー元のシートの1つがバグってたようです。
現在はメモリ使用量が250MB程度に収まりました。
ありがとうございました。

2019/10/04(金) 08:52:18.52

質問させてください。
シート上に簡単なHTMLを表示させたいのですが、そのようなコントロールはありますでしょうか？

必要なタグは、br font(color属性のみ) bold div(改行の代わり)くらいです。

目的としては、WEBデータベースに登録されているリッチテキストフィールドをダウンロードして表示することになります。
危ないタグはWEBサービス側で排除されていると思います。

2019/10/04(金) 10:46:00.35

>>152
Microsoft Web Browserコントロールというのがあります
ただしHTMLの設定や変更にはVBAが必要です

2019/10/04(金) 20:22:42.36

>>152
簡単なHTMLと簡単でないHTMLの違いがわからん

2019/10/05(土) 10:01:54.14

MSはC#とかTSとか素性のよい言語開発してるのにどうしてマクロは頑なにVBAのみなのか…

2019/10/05(土) 10:16:11.22

企業ユーザーは既存資産が動く事が大事
新しい事は求めていない

2019/10/05(土) 10:21:54.58

c#でexcelの読み書きすればいい

2019/10/05(土) 11:06:04.60

>>157
それならそれでいいんだけどMarshall.ComReleaseが面倒すぎる

2019/10/05(土) 14:26:52.59

""とclearcontentsって別物だったんだな。
一部書籍で扱いが同じだったが新刊書で違いが明らかになった。

2019/10/05(土) 15:04:12.76

>>159
どう違うんだ？

2019/10/05(土) 15:20:21.70

長さ0の文字列と値・数式のクリア

2019/10/05(土) 15:24:12.24

>>161
何を入れてクリアしてるかを聞いてるのでは

2019/10/05(土) 15:52:41.48

ClearContentsでクリアするとValueプロパティの中身が型未定義のEmpty値になるってことか？

2019/10/05(土) 15:53:36.09

>>161
この2つで異なる結果を返す関数、演算は何ですか？

2019/10/05(土) 18:14:16.86

並べ替え、重複削除、ピボットテーブル、COUNTA関数

2019/10/05(土) 18:21:34.50

違うかなぁ？
typenameは、どちらもEmptyだし。並び替えにも影響しないみたいだけど、、

2019/10/05(土) 19:16:42.87

""のTypeNameはvbStringじゃないの？

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

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