Excel VBA 質問スレ Part75

**デフォルトの名無しさん** (ｽﾌｯ Sd7f-wadS) · 2022/02/09(水) 14:24:32.62

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑2行に減ってるけど、同じ内容を3行に増やして貼り付けるナリ

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

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

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

2022/03/03(木) 14:13:34.66

>>284
For Each Ws In Worksheets
Ws.Activate
～色々な動作～
Next Ws
↓
sheets("ワークシート名").Activate

WSをWorksheet("ワークシート名")
に変える
あと、Ws.Activateはなるべくやめたほうがいい
PCが重くてアクティブなシートやブックが変わると、大惨事になる可能性がある

本当にやるならopenなどで対象ファイルを開き、戻り値をsetするのが理想

>>285
？？

2022/03/03(木) 14:19:57.38

>>286
ありがとうございます。
この”ワークシート名”が分からなくても動かしたいのです。
つまり、hogehoge.xlsx だったり zukozuko.xlsxだったりするので
最初にアクティブだったファイル（シート）に戻ってくる汎用性がほしい。

2022/03/03(木) 14:22:48.58

>>287
あぁ、じゃあ
ActiveSheet
でいいよ
ActiveSheet.Cells(1, 1) = 1
みたいな感じで使える

2022/03/03(木) 14:24:24.97

そういやブックを指定する時はopenでいいけど、
シート名が不定な時、シートを指定する方法ってないのか
一旦開いてinputboxにインデックスかシート名入れるぐらいしかないのだろうか

2022/03/03(木) 14:32:22.98

まずワークシートもワークブックもわかってない事から勉強したほうがよいのではないか
それとよくわからず何となく動いたものを他人に渡して大丈夫なのかと不安になるわ

2022/03/03(木) 14:43:43.44

あんまり大丈夫じゃないけど、そこまで言うのもねぇ

2022/03/03(木) 15:56:08.71

心配かけます
とりあえず、データは何回でもダウンロードできるのでいくら壊れても大丈夫です。
内輪の事務ですし
やはり、どんなファイル名（シート名）でもＯＫとなると難しいですかね
当然フルパスみたいに書くのが良いのはわかります。

2022/03/03(木) 16:16:41.04

加工されるワークブックにはワークシートは複数なの？それとも1つなの？
仮に先頭にあるワークシートであれば
Set Ws = Worksheets(1)
のような指定をすればいいだけだぞ
ワークシート名が決まった名前であれば
Set Ws = Worksheets("○○")
のような指定でもOK
どのシートが加工していいのかを判断できるなら当初のFor文で回しながらシートを特定してもいいかと思うが
ある程度のルールが決まった質問じゃないと漠然としすぎて回答する側もとまどうと思うぞ

2022/03/03(木) 17:11:57.48

>>284
>てっきりWSという関数に加工BOOKの場所データが格納されていて
Wsは関数じゃなくて変数
その変数に値をセットしていたのがFor文
>最初にアクティブになっていたブックシート情報を関数に入れておけばよいと想像しているのですが、どうすればいいかとかわかっていません。
Set Ws=ActiveSheet
かな。試してないけど
とりあえずForの代わりにこれで動くんじゃね

あまりにも基本がわかってない感があるなぁ

2022/03/03(木) 17:21:03.63

コピペしかしない人なんでしょ

2022/03/03(木) 18:46:16.06

>>294
はいすいません
関数は書き間違いです。変数ですよねｗ
ちょっと、脳が
Set Ws=ActiveSheet
で辻褄があいました。
最初の　For　Nextで全シートした場合も１シートだけやった場合もエラーが出ずに自分の脳みそも合致がいきました。
これを書いていないのに　Forで動かしたときに問題なかったので？？？状態になっておりました。
ありがとうございます。

**デフォルトの名無しさん** (ｽﾌｯ Sdea-SZxA) · 2022/03/03(木) 19:43:39.19

何ていうかVBAの言語より先にちゃんとした構文の日本語を学ぶ事をおすすめします

2022/03/03(木) 21:25:56.68

いやでも回答者さんの言うとおりだと思う。理屈や仕組み分からないまま使ったりしていたらいつかは大事故に繋がる。
過去にそういう事が身近であった。

2022/03/03(木) 21:46:06.24

何がきっかけで作ることになったか知らないけど
どうして動いてるかもわからずに作って少ししておかしいことに気づいてもすぐ直せなかったら評価悪くなると思うんだよね
自分の周りの出来事でなければ知ったことではないんだけどその辺は踏まえて作った方がいいよ

2022/03/03(木) 22:33:01.31

ID:KJVc8U50M の言い回しが技術サイトでありがちな英語かロシア語の機械翻訳文に似てて草

2022/03/05(土) 11:35:29.63

！！Private Sub Worksheet_Change（ByVal Target As Range）

Dim i As Long
For i ＝1 to 5
cells（i，4）＝DateValue（Cells（i，1）&" 年"&" 月"&Cells（i，2）&Cells（i，3）&"日"）
Next i
End Sub

上記記述して実際にｼーﾄに数字を入力すると型が一致しませんとｴﾗーになってしまいます
どうしていいのかわからずご教授していただければと
素人みたいな質問すいません

2022/03/05(土) 12:01:15.53

月の位置がおかしいから？w

2022/03/05(土) 12:21:43.22

こういうひとってデバッグの仕方とか知らないのかね？
Debug.Print(Cells（i，1）&" 年"&" 月"&Cells（i，2）&Cells（i，3）&"日")
のようなことをすればどんな値が指定されているかわかるんだからみればいいのに

＞素人みたいな質問すいません
素人でもこんな質問するかなという内容

2022/03/05(土) 12:21:43.61

>>301
とりあえず止まったときにイミディエイトウィンドウで
Debug.Print Cells（i，1）&" 年"&" 月"&Cells（i，2）&Cells（i，3）&"日"
ってやってみな

2022/03/05(土) 12:22:36.15

被ったわ…

2022/03/05(土) 12:39:47.23

そもそもVBAにする必要あるのかこれ？

2022/03/05(土) 12:51:19.12

>>301

Private Sub Worksheet_Change（ByVal Target As Range）
Dim i As Long
For i ＝1 to 5
cells（i，4）＝DateValue（Cells（i，1）&" 年"&　Cells（i，2）&"月"&Cells（i，3）&"日"）
Next i
End Sub

すいません!
月の位置が違いました
上記の記述でも型が一致しませんとエラーになってしまいます

2022/03/05(土) 13:08:57.00

=Date(A2, B2, ... え？それ以上はやめとけって？　はーい

2022/03/05(土) 13:15:31.27

その書き方だと少なくとも変更された際のイベントでDateValueを正しく動作させるには
1から5行目までの1から3列目までにそれぞれ年月日の値が保証されないと型が一致しないとでるけど
そういう状態が担保できてるの？
1行目の入力中でもその書き方だと2行目以降が空欄だと単なる「年月日」という値でDateValue実行してしまいますよ

**デフォルトの名無しさん** (ｽﾌｯ Sdbf-OYGG) · 2022/03/05(土) 13:43:03.37

セルの中身が実はシリアル値でしたに一票

2022/03/05(土) 14:24:04.55

>>309
おっしゃるとおりだと
なんとなくわかっていたのですが
回避方法ありますか？

2022/03/05(土) 14:46:06.75

>>311
いずれかのセルが空欄や数字以外だったら4列目に空を設定
それ以外の場合のみDateValueとかでいいんじゃないの
ただ結果的に月日が不正な値(99月など)ならどうするとかそういうのも含めて自分で考えないとこの先やっていけないのでは

2022/03/05(土) 14:57:28.77

>>313
なるほど
参考になります
ググって見ます！
ありがとうございます！

2022/03/08(火) 11:28:41.82

すみません、質問です！
例えばA1セルに関数が入っていたとします
A1セルが空白かどうか(関数は入っています)をVBAのIF文で分岐したいですがうまく行きません
VBAでは空白を取得できているはずなんですが何故なんでしょう…
どなたか対応方法を教えてください

2022/03/08(火) 11:33:03.86

If Range("A1").Fomula = "" Then

2022/03/08(火) 11:33:45.29

あ、嘘。ごめん。間違えた。忘れて。

2022/03/08(火) 11:39:20.91

>>315
ありがとうございます
Formulaを設定すると関数をそのまま取得してしまいました
なので空白の時の分岐ができませんでした

2022/03/08(火) 11:41:01.62

大丈夫です！
ありがとうございますm(_ _)m

ご存じの方いらっしゃいましたらよろしくお願いします

2022/03/08(火) 12:20:48.22

普通にセルのValueなりTextなりを見ればいいんじゃないの？

2022/03/08(火) 12:37:16.79

>>314>>318
If Range("A1").value = "" Then

>>319
その普通を学習前の段階ぽい

2022/03/08(火) 13:18:52.50

空なのか空白なのか

**デフォルトの名無しさん** (ｽﾌｯ Sdbf-6iqn) · 2022/03/08(火) 13:28:01.68

Trim(.Text)じゃないか？

>>321
数式が入ってたらEmptyにはならん

2022/03/08(火) 14:12:30.12

Empty と空文字列と空白は違うって話かと

2022/03/08(火) 19:12:34.37

前任者のシートで0を書式で空白にしてるのには参ったね
textプロパティを覚えるきっかけにはなったけど

2022/03/08(火) 19:25:23.65

文字列枠だけど書式は #
よくやるw

2022/03/11(金) 06:05:40.07

すみません、質問させてください。
VBAで自作したクラスのメソッドやプロパティの一覧を列挙するにはどうすれば宜しいでしょうか？
どのような自作したクラスでも使える汎用的なものを作成したいです。

2022/03/11(金) 07:32:33.79

すみません。
特定のセル内に有るリンクを抽出する方法は有りますか？

**デフォルトの名無しさん** (ｽﾌｯ Sdbf-6iqn) · 2022/03/11(金) 08:32:32.67

>>326
VBAでは無理
ソースをエクスポートしてテキストファイルを処理するぐらいしかないと思う
VB.netならGetMembersってのがあるんだけど

**デフォルトの名無しさん** (ｽﾌｯ Sdbf-6iqn) · 2022/03/11(金) 08:33:31.79

>>327
Hyperlink

2022/03/12(土) 01:24:13.27

>>328
なるほど、VBAでは無理なんですね。
以前、VB.Netの命令をVBAで走らせることが出来る場合があることをこのスレで聞いたことがあるような気がしましたので、
そちらとAPIで実装出来ないかを模索してみます。ありがとうございました。

2022/03/12(土) 03:38:02.55

オブジェクトブラウザがやってるけど、どうやってるんだろうな
>>328
エクスポートとかしなくても、VBEオブジェクト使えばコードのスキャンはできる
プロシジャ名の列挙だけでよければここの実行結果３がそうじゃね
https://excelwork.info/excel/codemoduleproperty/

2022/03/12(土) 08:17:43.49

>>331
後出しになってしまい申し訳ありません。
実は、実際にやりたかったことは
自作したEntityクラスのプロパティを
片っ端からGetして配列に入れ込み、
それをシートに展開すると言うことでした。

名前の列挙さえ取得できれば
後はリフレクションで呼び出せば可能と
考えていました。

おかげで実装の目処がつきました。
ありがとうございます。

2022/03/13(日) 02:21:31.96

どういう状況で使うんだろう
自分で作ったのに数が多すぎて覚えてないとか?

2022/03/13(日) 02:43:05.91

素人ですが質問させて下さい。

画像のような、1つのシートに20行単位の表で、連番と日付を上の表から取得するようなブックを作成しています。
表の順番を変えても、常に1つ上の表の番号+1、同じ日付としたいため、直接セルを参照せずこのような関数を使用しています。

式は、↓としています。
=INDIRECT(ADDRESS(ROW()-20,COLUMN()))

起動中は問題なく動作するのですが、ブックを閉じて再起動すると、起動時に必ず落ちてしまいます。(手動計算にすると開ける)

コピーを作成して、上記の式の部分だけ消すと問題なく起動します。

この場合、何が悪いのでしょうか。或いは代替手段があれば教えて頂けると助かります。

https://i.imgur.com/cVZnwnZ.jpg

2022/03/13(日) 02:49:45.62

普段VBAを触っているためVBAスレで質問してしまいました。
スレ違いであればスルーして下さい。

2022/03/13(日) 03:12:17.58

>>335
多分Excel板なら親切な人が回答してくれすよ

2022/03/13(日) 05:18:44.42

>>334
offsetで良いと思う。indirectは挙動が微妙におかしい
t46=offset(t26,-20,0)

>>335>>336
回答者は被ってるからどっちでもいいよ

2022/03/13(日) 11:02:15.36

レスありがとうございます。

こいつ自体難のあるものなんですね。
offsetで狙った動きができました。ありがとうございます。

2022/03/13(日) 14:32:38.85

普通に「＝R[-20]C+1」じゃダメなのかね（R1C1形式

2022/03/14(月) 05:49:07.49

>>339
これだと移動させたとき式の値も変動してしまいました。

2022/03/14(月) 07:57:10.59

>>340
失礼、移動後も保証しないとだめだからこれではだめですね

2022/03/14(月) 17:48:06.77

>>334ですが、当初安定していたもののまた同じ現象になりました。

端末を変えるなり新しいブックにして作り直したり色々試すも改善せず、T列の参照だけがこの現象を引き起こしている(参照先が文字列だと発生しないが数値は発生)ことが分かったため他の列を経由して参照する形で概ね落ち着きました。

エクセル自体のバグなのか分かりませんが同種の状況の方の参考まで。

2022/03/15(火) 13:19:58.65

>>342
多分ブックが壊れている
新しいブックを作り、1シートずつコピーすれば直る可能性がある
その状態で使い続けると完全に壊れて、開けなくなる可能性があると思う

2022/03/16(水) 04:32:35.42

ブックがブックわれている

**デフォルトの名無しさん** (ｽﾌｯ Sda2-eKgF) · 2022/03/16(水) 07:48:40.55

セル「ちくしょーちくしょー！完全体に…… 完全体にさえなればー！」

2022/03/16(水) 07:55:37.71

エクセル「私の行数は　1048576です」

2022/03/16(水) 10:32:53.21

質問させて下さい。
EXCELの自ブックのシートにVBAにてADO接続でSQLを投げてRecordSetに値を取得していますが、
あまりに遅いのでINDEXを対象のシートに設定したいのですが、可能でしょうか？
可能であれば実装方法を教えて頂けるとありがたいです。

CREATE INDEXのSQLを投げると
「この操作は、このタイプのオブジェクトには実行できません。」というエラーが返って来ます。

EXCEL VBA上でのADO接続だったのでこちらのスレで質問させて頂きました。
もしスレ違いで有れば誘導して頂けるとありがたいです。

2022/03/16(水) 20:05:55.54

エクセルの開発、挿入、フォームコントロールでリストボックスを作って操作するのですが
sheet.ListBoxes("List").AddItem "A" だったかな？で追加できるんですが
sheet.ListBoxes("List").Clear で全て消去したくてもエラーになります。
sheet.ListBoxes("List").RemoveItem 1 で消せるので .ListCountが0になるまで
消していくとやりたいことはできるのですがClearのような1行で消す方法が知りたいです。
あと選択している内容を取得したいのですが.Valueだと内容ではなくインデックス番号？が返ってきます
選択している内容を文字列で取得できないのでしょうか。検索してもユーザーフォームのページばかり
出てきてわかりません。どなたか知っていたら教えてください。

2022/03/17(木) 04:53:04.06

>>348
全消しはRemoveAllItemsかな？
内容の取得はItem(インデックス番号)かな？
マニュアル見てそれっぽい単語を見つけただけだから違ってるかも

2022/03/17(木) 05:57:03.26

最近のExcelってヘルプもついていないの？

2022/03/17(木) 06:07:57.47

ネットがあるからねぇ

2022/03/17(木) 11:46:40.93

それでなくなったのか、知らんかった

2022/03/17(木) 12:31:38.84

F1押すだけでヘルプ出てくるの便利だったのにね

2022/03/17(木) 12:35:28.43

F1ヘルプ起動大嫌い
起動遅いから誤爆した時ストレス

2022/03/17(木) 12:48:39.33

まあ市販ソフトとかゲームとかおしゃんな小物とかもネット参照多くなったしね

2022/03/17(木) 15:59:04.45

2010のあたりでヘルプがオンラインになって、旧バージョンのヘルプファイルを無理矢理インストールして使ってた記憶がある
あれってどのバージョンの話だっけ

2022/03/17(木) 16:00:36.21

>>348
VBAから操作するんなら、たぶんフォームコントロールよりActiveXコントロールのが簡単だと思うよ
MSは推奨してないみたいだけど

2022/03/17(木) 18:51:23.33

やたら重かった無能なイルカよりはマシだろう

2022/03/17(木) 19:51:28.31

お前を消す方法なつい

2022/03/17(木) 20:00:50.39

>>348
https://www.sejuku.net/blog/37803　ここ読んでがんがれ

2022/03/17(木) 22:15:36.31

>>349
全消しはRemoveAllItemsでできました。Itmeで内容はとれずエラーになりました。
マニュアルはマイクロソフトのExcel VBA リファレンスかな？そこで検索かけてもよくわからないのであきらめます。
Valueでもやりたいことはできそうなので

>>357
ActiveXでのやり方はわかるんですけどできればActiveXより新しいの使いたいんですよね。
別件でActiveXが大量に使われているブックが物凄く重くなっているのを見たことあってあまり使いたくない。てのもあります。

>>360
ユーザーフォームのことじゃないです。

2022/03/18(金) 09:26:17.20

じゃあこれ
http://officetanaka.net/excel/function/tips/tips56.htm

2022/03/18(金) 10:46:32.32

>>361
List(参照したい位置)でいけるんじゃないか
参照したい位置はListIndexでとれるかと
複数選択ならSelected(位置)でTrueになるところかな

2022/03/18(金) 19:57:18.31

シート上で任意に選択した範囲を set myRange = 選択範囲のように代入した後、
For Each buf In myRange.Value
処理A
Next
という処理をしようとすると、
・選択範囲が複数ある場合：正常に動作する
・選択範囲が1つの場合：「型が一致しません」のエラーになる

「For each ○ IN ●」の●には複数セルが指定されていないといけないということでしょうか？

2022/03/18(金) 23:38:51.84

>>364
そうです　

set buf as range
For Each buf In myRange
debug.print buf.value
Next
こんな感じじゃだめですか？

2022/03/18(金) 23:53:01.28

おっと間違えた
dim buf as range
For Each buf In myRange
debug.print buf.value
Next

2022/03/19(土) 00:50:36.99

myRange.Value の Value がおかしかったのか。
ありがとうございます。

2022/03/19(土) 02:03:56.48

>>364
myRangeの何をbufとしてとりたいの？各セル、連続した複数のセル、myRange全体によつて正しいこーどは異なる

**364** (ﾜｯﾁｮｲ 13ad-bSSa) · 2022/03/19(土) 10:45:02.08

>368
任意のドラッグ操作で選択したセルと同じ行のL列(左から12番目)に入っている文字列を結合して表示させようとしました。

Dim cnt As Integer
Dim myRange As Range
Dim buf As Range
Dim target As String

cnt = Selection.Rows.Count
Set myRange = Range(Cells(Selection.Rows(1).Row, 12), Cells(Selection.Rows(1).Row + cnt - 1, 12))

For Each buf In myRange
target = target & buf & vbCrLf
Next

Debug.Print target

2022/03/19(土) 10:48:26.22

>>347で質問させて頂いたものです。
色々調べましたがシートにINDEXを設定するのは実現可能か分からなかったのと、
出来ない場合、代替案を考えたいのですが、
ひとつのシートに20万行、100列の一覧が有ったとして
そのとき、ひとつの項目(項目1とします)はある値とイコール、更に別の項目(項目2とします)は
項目1で抽出したレコードの内の最大値を求めたいです。

どのような方法が一番速く処理出来ると思いますか？

**364** (ﾜｯﾁｮｲ 13ad-bSSa) · 2022/03/19(土) 10:48:43.76

>>369は正常に動作したコードです。

最初は下記のようなコードでエラーとなっていました。

Dim buf As Variant ←Variant型にしていた。

For Each buf In myRange.Value ←.Valueを付けていた。
target = target & buf & vbCrLf
Next

Debug.Print target

2022/03/19(土) 10:51:21.24

範囲検索するのにfindとworksheetfunction.matchとではどちらが速いですか？

2022/03/19(土) 10:52:04.86

ちなみに完全一致の場合です

2022/03/19(土) 11:51:46.09

自分で試せばいいだろw

2022/03/19(土) 11:51:57.89

試したほうが早いよ

2022/03/19(土) 12:52:21.15

は？試してたらこんな所で聞きませんけど？

2022/03/19(土) 12:52:58.71

>>369
＞cnt = Selection.Rows.Count
＞Set myRange = Range(Cells(Selection.Rows(1).Row, 12), Cells(Selection.Rows(1).Row + cnt - 1, 12))

Set myRange = Cells(Selection.Row, "L").Resize(Selection.Rows.Count)
もっと簡単に書けそうな気もするがこんなもんかな

2022/03/19(土) 13:47:10.61

>>376
試してなくても、こんなところで聞かないでくださいw

2022/03/19(土) 14:44:42.82

>>377
ちゅんちゅんさん推奨の書き方か
resize真似して使ってたんだけど間違えやすかった

2022/03/19(土) 15:12:32.83

>>377
ありがとうございます。.Resizeを使うとスマートに書けるんですね。使ってみます。

2022/03/19(土) 15:57:19.25

autofilterで表示されたものだけで、列をautofitすることは出来ますか？

2022/03/19(土) 16:36:01.13

SpecialCells(xlCellTypeVisible)でできんじゃねーかな

2022/03/19(土) 16:40:58.85

コードはスマートなのに…って言われろ

2022/03/19(土) 18:04:38.19

Range("テーブル1[項目A]").SpecialCells(xlCellTypeVisible).Columns.AutoFit
出来ないのかと思ったらできるじゃん

2022/03/19(土) 18:22:46.82

VBA素人にご教示ください

PowerQueryで整形したテーブルのデータを
別ブックのVBAに引っ張ってきたい場合
・列データをユーザー定義型にまとめる
・ListObjectを使う
どちらがベターでしょうか？