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/25(水) 06:45:55.03

まじ？
エクセルやってる一般社員様がpython始めたらどうなるんだろ

**デフォルトの名無しさん** (ｽﾌｯ Sd22-k+x8) · 2019/09/25(水) 08:47:00.45

おっぱいでexcelを自動化する

2019/09/25(水) 09:19:56.07

Pythonエンジン搭載は色んな理由で見送られたはず

2019/09/25(水) 13:51:45.80

>>85
未だにこういう馬鹿がいるのか

**デフォルトの名無しさん** (JP 0H4a-tMNN) · 2019/09/25(水) 17:46:53.71

Python搭載は見送られて代わりにJavaScriptが搭載されるという記事をどこかで見かけましたが。

どなたか>>46わかるかたお願いします。

2019/09/25(水) 19:10:29.50

>>90

>>89
>>未だにこういう馬鹿がいるのか

2019/09/26(木) 00:31:10.28

jsだったらgasと同じような感じになるのかな
gasはそもそもスプレッドシート自体の機能が物足りないからイマイチだったんだけど

2019/09/26(木) 00:57:48.08

これからJSエンジンが実装されるならTypeScriptベースで型付け強制になるだろうな

2019/09/26(木) 20:07:07.87

.ClearContents と .Value="" では実行後のセルの状態は異なりますか？

2019/09/26(木) 20:28:04.65

何も載らないよ
Excel自体クラウド上で使うようになってきてるから
マクロみたい使うならJavaScriptなのかもね

2019/09/27(金) 06:06:27.72

きーよしー
こーのよーるー

2019/09/27(金) 07:55:16.60

ハーゲーはー　ひーかーり

2019/09/27(金) 08:22:15.68

最近はExcel操作のためにVBAを使うのはもうなんか最後の手段って感じがする
PowerQuery(M言語)、PowerPivot(DAX)、JavaScript、Python、PowerShellのどれかで書く方が楽だしメンテも簡単

2019/09/27(金) 08:43:34.46

趣味が大がかりになってきて使う材料の管理が煩雑になってきたのでExcelを使って管理しようかなと考えています

工程が1,2,3…とあって、それぞれの工程で使う部材は今のところ重複はなく(今後どうなるかわからない)部材毎に色違いやグレード違い等の派生品があり、
用途としては工程毎に使う部材を別シートにリストアップしたり、規定量を割ったものをピックアップ
部材の管理番号を振るとき
番号は工程や派生に関わらず一列で一意の連番
3列で工程ｰ工程内の部材を連番ｰ派生を枝番
工程に関わらず部材毎に連番ｰ派生品を枝番
マクロを組むときこれらのうちどうするのがよい構造になるでしょうか？

**デフォルトの名無しさん** (ｽﾌｯ Sd7f-mDN4) · 2019/09/27(金) 09:04:03.76

まず3行にまとめます。

2019/09/27(金) 12:08:52.99

RDBSを使って合成テーブルを作ります

2019/09/27(金) 21:25:04.26

>>98
Excelデータ操作するのにか！？

2019/09/27(金) 21:29:52.64

確かにvlookup関数もCで作ったほうが処理速度速いですし

2019/09/28(土) 01:10:35.83

>>102
情報取得とかテーブル加工とかならVBAよりQueryTable）PowerPivotの方がよっぽど速いし型安全にデータ処理できる
数万行以上のデータのリレーション構築とテーブル加工をVBAやWorkshhet関数で実現するとか正気の沙汰じゃないぞ

2019/09/28(土) 07:02:49.99

なんか齢50近くになると新しい言語？を知らない…power query？power pivot？
…後で調べてみないと…

2019/09/28(土) 07:36:24.34

Ｍ言語ってどうやって勉強するのさ
英語の本け？

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をおしたら対象となるセルに反映されました。
これもどうしたら普通通りになりますか？