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/28(土) 08:34:58.34

>>106
マイクロソフトが仕様書公開してる

2019/09/28(土) 09:58:25.05

>>106
MSが仕様公開して言語レファレンスまで作ってる
開発&実行環境はExcelのPowerQueryに付いてるので特に余分なものは要らない

2019/09/28(土) 14:02:31.05

>>99
一番目の方法でまず一意のIDを持つ部材のマスタを作った方が良い
その上で、派生関係や使用工程は列を分けて部材マスタの属性値として管理する
そうすれば各使用工程で使う部材を抽出するビューが漏れなく簡単に作れるし、部材データの追加も容易

何故他のやり方が良くないかというと、使用工程や派生関係を管理番号で構造的に表そうとしても、一対一の親子関係から外れる複合的な派生関係の表現が難しいし、複数の工程にまたがって使用する部材が登場した場合の付番がとても困難だから

2019/09/28(土) 16:34:58.82

↑仕様もわからないのに勝手に決めつける馬鹿

2019/09/28(土) 17:19:26.34

>>110
データ活用のシナリオの説明もある程度書いてくれてるし、3パターンの中からオススメを選んで教えてくれと言われてるんだから根拠を示しながら回答するのはOKだろ

2019/09/29(日) 08:21:00.73

馬鹿と言いたいだけの馬鹿は相手にしなくていいよ

2019/09/29(日) 09:41:07.61

> 馬鹿と言いたいだけの馬鹿
>>112の悪口はやめなよ

2019/09/29(日) 10:19:36.85

今、困っていることがあり教えてください。

Excelをオープンした時に、入力規則のリストが設定されているセルに
VBAでリストにある文字列を張り付けるとリストが解除されてしまい困っています。
ただ、Excelを既にオープンしている状態であれば、VBAで文字列を張り付けてもリストは解除されません。
Excelをオープンした際の文字列貼り付けでもリストを解除しない方法、何かないでしょうか。

2019/09/29(日) 11:49:35.55

>>114
試してないから自信はないけど配列に文字列の値を格納してからコピー先セルに一括代入してみたらどうかね

2019/09/29(日) 12:33:57.48

計算方法の自動手動を切り替えるとか
doeventsを挟むとか
入力規則を消去して再度設定してみるとか

2019/09/29(日) 13:40:07.40

>>115
適当な配列を用意して、1要素に代入したい文字列を格納した後、該当セルにコピーしたが駄目でした。
もしかして、一括代入の意味の捉え方誤っていますでしょうか？

>>116
計算方法を手動にしてもdoevent挟んでも駄目でした。
入力規則の再設定は大丈夫でしたが、再設定しか駄目なんですかね。

2019/09/29(日) 16:27:33.03

再設定で目的が果たせるならそれでいいんじゃないの
気に入らないかもしれないけど

2019/09/29(日) 19:28:31.60

>>118
目的は達成できるのですが、どうしてそんなことが
起きているのか気持ち悪さが残ってしまって・・・

ひとまずリスト再設定で回避してみようと思います。
皆さん、ありがとうございました。

2019/09/29(日) 20:52:21.32

１ステップずつ何が起こってるか確認してみた？

2019/09/29(日) 21:37:57.26

ウォッチ式で入力規則が無効になった瞬間を捕まえたらどのステップで問題が起きてるか分かるよ
あとApplication.EnableEvents=False の状態で入力規則が無効化する症状が同じように出るかどうかを確かめると良いかも
副作用あるから気を付けた方がいいけどね

2019/09/29(日) 22:03:13.06

デバッグは大事

2019/09/29(日) 23:10:51.27

Worksheetの表示を縮小したときに名前をつけた範囲名が青文字で表示されますが
あれを縮小表示なしに表示するメソッドとかないですかね？
なければ諦めて作ろうと思って、、

2019/09/30(月) 09:40:08.63

以前の勤務表の作成についての質問をしたものです。

指定範囲に対して条件を参照して自動で勤務を組むにはどのようなコードが使えますか。

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行目を指定するのは難しそう