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/09/30(月) 09:44:23.17

その通りにコードを組めばいいです
よかったですね

2019/09/30(月) 14:06:19.14

そこまでくると質問というより代わりに作ってって感じな気がする

**デフォルトの名無しさん** (ｽﾌｯ Sd7f-C0+4) · 2019/09/30(月) 14:27:24.46

15万ぐらい払えば作ってくれるよ

2019/09/30(月) 18:03:03.23

任意のフォルダを開きたいくて、そのファイルのパスが
A1に書いてあるときにどんなコード書いたらできますか？だれか教えてください。お願いします🥺

2019/09/30(月) 18:38:27.74

え？ｗ>>124見たいな糞簡単なの作って15万???
うそやろｗｗｗｗｗ

2019/09/30(月) 19:38:06.65

>>128
ファイル形式は？

2019/09/30(月) 20:42:31.76

>>128
次から次へと現れる馬鹿

2019/09/30(月) 21:02:52.82

>>128
フォルダのフルパスがA1セルに入っているなら、

Shell Range("A1").Value, vbNormalFocus

と書けばとりあえずフォルダを開くことはできる
開いたフォルダの中のファイルを選んで何らかの操作をしたいならファイルそのものを掴む別の工夫が必要

2019/09/30(月) 21:15:26.21

.Findで検索するマクロ作ってるけど死にそう
全シート検索で結合セル気にしないでいい方法ないですか
一致したの全部もれなくとるだけなのに
なんでこんな大変なんですか

2019/09/30(月) 22:05:40.31

>>133
何がそんなに大変なの？

2019/09/30(月) 22:43:31.23

初回検索と次項目の検索が別メソッド
検索したあとに問答無用で一周して戻ってくる謎仕様
結合セル最初に見つけたら２週目で見つけられなくて死亡
最初から検索しようとしたら最初の次のセルから開始という超絶理不尽仕様
最終セル開始点にしようとしたら最終セルが結合セルで死亡

2019/09/30(月) 22:55:52.56

http://dsp74118.blogspot.com/2014/08/excel-vbarangefind.html

ってこれなに
うそだろ

2019/09/30(月) 23:15:22.35

あるシートから10000行程クリップボードにコピーして別シートに行挿入する事を10回程度繰り返すマクロがメモリ不足で停止してしまいます。
エクセルは32bitですがメモリ使用量は2.5GB程度、使用率は80%程度なので余裕はあります。
コピー&挿入だけでこんなにメモリ食うのは想定外だったのですが何か良い対策ありませんか？
クリップボードを使ってる理由は書式もコピーしたいからです。

2019/10/01(火) 00:40:37.46

>>137
結合セルがない場合で、書式が行列で統一的に構造化されている(=手動でランダムに設定された書式がない)場合の話だけど、セルの値のコピーはクエリテーブルを使い、セル書式はスタイルとして登録しておいて値をコピーしたあとにテーブル単位で適用するようにする

それができないなら常套手段的な対策として、描画停止、セルの自動計算の無効化、一度のコピー行数の抑制、コピーする情報の量の削減(列を減らす、セルに入れる値の文字列長に上限をつける等)をやるしかない

2019/10/01(火) 00:48:34.75

>>135
検索対象のセル範囲でループを回して、MergeAreaプロパティから取得できる結合範囲の第一セルの値に対してInStr関数でチェックをかけるほうが簡単に実装できる
一度触った第一セルをDictionaryに突っ込んでおけば重複回避の処理も簡単に書けるし

2019/10/01(火) 02:44:48.32

1000行を100回にすれば解決する気がする

2019/10/01(火) 20:19:29.97

>>137
想定して最初から設計しなおした方がいいんじゃね

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をなぜ使うかの方がわからん