Excel VBA 質問スレ Part70

2021/03/18(木) 22:08:43.71

!extend:checked:vvvvv:1000:512
↑同じ内容を2行貼り付けるナリ

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

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

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

2021/03/18(木) 22:33:41.83

教えて下さい。
あるシートに買物記録があり、日付や購入店、商品名や単価の他に、商品の分類という列があります。
その列には「文具」「食品」「書籍」等が入力されています。

VBAで以下の動作をさせたいです。
　↓
別シートにあるダウンリストに、選択肢として、この商品分類に入力されている値を設定する。
重複して入力されている値は無視。入力されているすべての「分類」をひとつずつ選択肢にする。
　↑
これをどう書けばいいのか、教えて下さい。
よろしくお願いいたします。

2021/03/19(金) 02:09:13.85

関数でも分類列をMATCHで検索してRow()と不一致なら空白として、それを入力規則にすれば出来そう

VBAでやるなら連想配列使って重複弾いて、かな

2021/03/19(金) 12:08:42.44

>>2
もう少し個別にどの部分で詰まっているか書いた方がいいんじゃね

2021/03/19(金) 12:19:16.87

>>2
ワークシート関数でいいなら

適当なセルに
=UNIQUE(商品の分類が入力された列)
と入力すると重複整理された配列が入力される

プルダウンリストを作りたいセルを選択して「データの入力規則」を選択、
規則をリスト、ソースをさっき作った配列がある列にする

2021/03/19(金) 13:13:27.10

ピボットグラフのRefresh処理が重くて困ってます
汎用的な処理中に停止すべき項目(画面描画、自動計算、イベント、アラート等)は停止させています
元テーブルの行は100程度です

処理を軽くする方法があればご教示願います

2021/03/19(金) 20:19:38.16

>>6
ピボットテーブルの設定で「更新時に列幅を自動調整する」のチェックを外す

2021/03/19(金) 20:23:00.26

ググるとそれ出て来るけど、あんまり意味ないよ
って言いつつ効果あったら恥ずかしいけど

2021/03/19(金) 22:16:15.80

初心者にはそのくらいしか教えられん

2021/03/19(金) 22:28:12.63

>>7
ありがとうございます

ですが処理が重くて困っているのはテーブル(ピボットテーブルではない)から作っているピボットグラフなのです

これが重かったりするんですかね…？

2021/03/19(金) 23:06:18.73

というか元々のグラフ生成が重いのかマクロが重いのか分からん
普通に更新ボタン押しても重いの？

2021/03/19(金) 23:55:25.74

>>11
普通に手動でも遅いです…
何かピボットグラフの設定によるものならとも思いますが…

2021/03/20(土) 00:13:52.57

グラフ更新よりも関数で固まってる可能性の方が高いから、
一度全部の関数を削除してみて解決するか確認した方がいいと思う
自動計算切っててもピボット更新時に再計算されてる筈だし

2021/03/20(土) 01:01:30.93

>>13
ありがとうございます
ワークシート関数は使わず、計算は全部vbaでやっています
関数と言えるのはピボットグラフのグラフ部分だけです
ピボットグラフのグラフ部分の関数が重くなっている可能性が考えられるかも、と思いました
ありがとうございます

2021/03/20(土) 04:43:36.06

すげえなワークシート関数一切使わんでマクロだけでやるとは

2021/03/20(土) 06:51:00.93

遅いとか速いとか漠然としているので何秒かかっているかもわからんし
データ量やステップが増えればある程度遅くなるのは当然じゃね

2021/03/20(土) 07:28:18.81

2～3秒でも遅いと思う人もいるしね

2021/03/20(土) 10:08:34.03

VBA使ってんならどの部分でどんだけ時間かかってるか測定すりゃいいだけの話なのになぜしないんだろう

2021/03/20(土) 11:15:44.57

>>18
Refreshのところで3秒かかっています
他処理全部は0.5秒で終わります

2021/03/20(土) 20:35:40.01

PCの性能が低いんじゃね

2021/03/20(土) 20:48:09.36

それだ！
パソコンのせい
ゲーミング用買えば全てにおいてスマホ級の速さだ！

**デフォルトの名無しさん** · 2021/03/21(日) 16:59:06.62

5chのスレで1000まで書き込まれたスレを1から1000までコピペしてsheet1に貼り付け
sheet1を特定のキーワードで検索して、書き込まれたレスと返信があったレスを抽出してsheet2に貼り付けしたいです
返信がない場合は空白と入力したいです
アイデアが思い付きません、宜しくお願いします

2021/03/21(日) 17:54:39.86

いつもの

2021/03/21(日) 21:09:20.26

>>6
イベントやアラートって目的も無く停止しない方がいいと思うが

2021/03/22(月) 05:59:51.89

>>22
5chアフィ乙

2021/03/23(火) 06:37:42.10

エクセルからhtmlで保存するとき
グラフが1000以上でもできますか

2021/03/23(火) 10:17:41.75

自分で作らずに質問してくる意味が解らない

2021/03/23(火) 14:45:46.64

ゆとりだから

2021/03/23(火) 15:16:16.58

選択した範囲をグラフにするマクロを作りたいです
マクロ記録でグラフ化するまでを記録し、それを改造してやっているのですが

' Macro1 Macro
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlRadar
ActiveChart.SetSourceData Source:=Range(左上:右下)
End Sub

「選択してる範囲を取得」してその範囲を代入する方法がよくわかりません

ActiveChart.SetSourceData Source:=Range(左上:右下)

のRangeの左上、右下部分にSelection(1)やSelection(Selection.Count)を入れようとしてるのですがエラーになります

**デフォルトの名無しさん** (JP 0H63-THbH) · 2021/03/23(火) 17:44:03.01

selectionだけでいいんじゃね

2021/03/23(火) 17:59:59.95

チャートをセレクトしてるからセルのセレクトが外れてるんだよ
Addchartの時にセレクトじゃなくてオブジェクト変数にセットして
アクティブチャートの行を【変数名】.(以下略)にしてやってみ
変数にセットするときに文頭のSetを忘れないようにな

2021/03/24(水) 18:47:39.00

エラー処理で　On Error GoTo　を　On Err GoTo　と書いていて
全く気付かずエラーになって初めて、ラベルに飛ばないので気づきました。
デバッグでは判定しないようですが、これらを事前にチェックで見つけることは
出来ないのですかね？

2021/03/24(水) 19:49:11.93

ヤフーファイナンスの仕様が変わった？のか、
当該銘柄のページから
Set FR = rngResult.Find(What:="出来高*", LookAt:=xlWhole)
みたいな感じで出来高取得してたのが
できなくなった

どこを直せばいいのかわからん
もしかしてこのやり方では
取得できなくなった？

2021/03/24(水) 19:50:41.85

>>32
まともなコードならGoto文なんてたいしてないはずだからGoto検索して目視確認で良いんじゃね？

29 (ﾜｯﾁｮｲ 9990-oXDN) · 2021/03/24(水) 20:04:27.24

うまくいきました、ありがとうございました
もう一つ質問させてください

以下は↓の画像のようにデータを範囲選択し実行した場合、３項目ごとに３つのグラフを表示するマクロです
①https://i.imgur.com/qb9quh1.jpg

Sub Macro1()
' Macro1 Macro
Set CTop = Selection(1)
Set CLast = Selection(Selection.Count)

Dim i As Integer
For i = 0 To 2
y = i * 3
With ActiveSheet.Shapes.AddChart.Chart
.ChartType = xlRadar
.SetSourceData Source:=Range(CTop.Offset(0, y), CLast.Offset(0, y))
End With
Next i
End Sub
ただ、データに問題があり、「行9」以降の部分を抜き出してグラフを作りたいんです
そこで↓のように範囲選択をして実行したのですが、系列名が出ないのでどのデータかわかりにくいんですよね
②https://i.imgur.com/u2vrDOl.jpg

実際のデータは膨大なため、いちいち「行○○～行○○まで」と数値で指定するのは難しいです
できれば②画像のように手動で範囲選択した場合、①のようにa,b,cと系列名が出るのが理想です
.SetSourceData Source:=Range(○○)　の部分をうまく変更すればいいのか、それとも
.SeriesCollection(1).Name = ○○　とあとで系列名を変えるのが良いのでしょうか？

2021/03/24(水) 20:32:05.20

個人的には系列名変える方が好き

◆QZaw55cn4c (AU 0Hd3-RQYg) · 2021/03/24(水) 21:05:50.71

>>32
インタプリタは事前に文法チェックをしないのが普通ですから、そういうのはインタプリタには無理でしょう‥‥

2021/03/24(水) 22:00:53.02

いまどき純粋なインタプリタなんてそうそうないけどな

一応VBAは事前にコンパイルチェックできるが、>>32は
On 変数 GoTo xxっていう文法があってそれにそっているからエラーにならない
そして変数宣言を強制しても、Errって組み込みオブジェクトが宣言なしで使えるという罠

なんかコード分析するようなツールでもあれば検出できるかもしれんが、まあ標準の範囲じゃ無理だな

2021/03/24(水) 22:54:43.12

>>35
元データと選択された表示用データを別にすればいいんじゃね

2021/03/25(木) 01:08:37.06

いきなりアホになってコード組むのが劇遅くなりました。
きれいなコードを書こうと思ったんです。

2021/03/25(木) 04:40:02.78

思っただけにしとけ

32 (ﾜｯﾁｮｲ 71fd-ED79) · 2021/03/25(木) 10:21:24.54

ありがとうございました。
自分で注意深くやるしかないんですね

実行中のfunction（やsub)名を取得することは可能でしょうか？
やりたいことはエラー処理時にその名称を表示したいのですが
べた書きするしかないですかね

Public Function test() As String

On Error testErr

testErrr:
MsgBox "testErr" & vbCrLf & "エラー番号：" & Err.Number & vbCrLf & "エラー内容：" & Err.Description, vbExclamation
End Function

2021/03/25(木) 10:44:00.77

あくまで個人的だが

MsgBox "○○で異常が発生しました." & vbCrLf & "Err:" & Err.Number & " " & Err.Description, vbExclamation, "Function test"

2021/03/25(木) 12:19:36.16

個人的には、だけど最終的にはエラー停止が発生せずイレギュラーの場合は自らメッセージを出して処理を終了するってするのが望ましいんじゃないかなぁ
動作確認で何実行してるか知りたいだけなら各プロシージャの頭にDebug.Printでプロシージャ名吐き出しておけばどこで止まったかが分かりやすい
まぁDebug.Printって軽くはないから少し大きめのプログラムだと動作確認中は時間かかる感じになっちゃうだろうけど

2021/03/25(木) 13:26:17.29

確かにそうだ、異常が出ないで済むように入力データのチェックとか
ファイルの有無とかを先に確認すればエラーなんか滅多に起きないからな

2021/03/25(木) 13:41:18.71

>>45
勿論そういうチェックは入れていますよ

**デフォルトの名無しさん** (JP 0H4d-4Ddi) · 2021/03/25(木) 14:16:03.41

ちゃんとチェックしてればOn Errorの出番なんか滅多にないはずだけど
複数の関数でエラーが出るなんて、あきらかに設計がおかしい

2021/03/25(木) 20:31:33.14

>>42
そうだね。VBAと言うかVB6系のは基本ベタ書きするしかない。
例外としてクラシックASPでVBS走らせたときはエラー行を表示させることが出来る。
VB.Netになるとその辺の情報は細やかに取得出来る。

VBAはベースが古い言語だから仕方がない。

2021/03/25(木) 20:56:22.97

>>47
別に設計がおかしいと言うことはないよ。
これはVBAに限らず論理エラーで把握しきれないものは実行時エラーとして捕まえる仕組みは当然のように設定する場合がある。

例えばDBに繋ぎに行って繋がったけどその後で何らかしらの外部的要因で切断された場合など、
実行時エラーが発生するのでその場合は何度か再接続を試みてダメだったら初めてエラーとしてポップアップするとか。

その場合、当然データ復旧させるためにもどこでどんなことやろうとして落ちたのかの情報は重要になってくることが多い。
その他にも概ね実行時エラーは予期せず落ちるものの方が多いので
むしろ状況を把握するためにも細かい情報が必要になることの方が多いよ。

**デフォルトの名無しさん** (JP 0H4d-4Ddi) · 2021/03/25(木) 21:30:34.23

それってVBAでやるようなことか？
例外処理が必要とわかってるなら.netで書いてTry Catchを使うべきだと思うが

2021/03/25(木) 22:07:43.24

>>50
.Netの環境が選択出来る状況ならそれでもいいんじゃないの？
.NetならException継承して好きにエラー設定すればいい。
けどここはあくまでEXCEL VBAのスレだからその話は置いておくよ。

後、VBAでやることかどうかと言うのはあくまで俺はだけど正直あまり選択基準にないな。
第一に要件を満たしているか、次に操作や保守がし易いかかな。まぁ、納期も重要な要素にはなるけど。

だからもし後で保守するのが楽になるなら
場合によってはエラーメッセージ出しておしまいにすることもあるだろうけど
場合によってはエラーハンドリング用のクラス作ったり、
論理エラーもRaiseして実行時エラーのように処理して一元管理したり、
後始末処理をコールバックで移譲して処理したりすることも視野に入れるよ。

2021/03/26(金) 01:12:09.52

Date型はミリ秒を持てないのでしょうか？
エクセルシートでミリ秒まで表示できている時刻があって、そのシリアル値をVBAのDate型変数に代入するとミリ秒以下が丸められてしまいます

2021/03/26(金) 01:52:58.20

>>52
時分秒までです。

2021/03/26(金) 01:56:39.74

>>52
Date型に入れて何がしたかったのか？

2021/03/26(金) 02:03:12.37

日付を扱うならDate型使うけど
時間を扱うならDouble型使うなぁ

2021/03/26(金) 08:07:23.35

>>52
シリアル値で日付までが整数部で時間から小数点になるから小数点扱える型なのにミリ秒が扱えないと言うのが
ちょっと引っ掛かったので調べてみたよ。

方式はセルにミリ秒まで表示出来るフォーマットを設定して(yyyy/mm/dd hh:mm:ss.000)
隣のセルにその値を代入する数式を書いて、数値で小数点16桁くらい表示するようなフォーマットを設定して、
またその隣にそのセルの値を代入してセルにミリ秒まで表示するフォーマットのセルを用意して
元の値が正しく表示されるか確認した。

で、VBAで最初のセルの値をDATE型の変数に入れてDouble型に変換したら、
キチンと2番目のセルと同じ頭になることが確認出来たので、DATE型はミリ秒まで持てることが分かった。

じゃ、何で丸められちゃうの？
と言うことになると思うけど、どうやらこういうことらしい。
https://support.microsoft.com/ja-jp/topic/excel-ワークシートのセルに-vba-の日付形式またはバリアント型の日付形式を割り当てようとすると-ミリ秒が最も近い秒に丸められます-4a0af2c5-78de-762f-6431-8669890f585b

2021/03/26(金) 12:00:33.82

>>53-56
ありがとうございます
勉強中でVBAの動きを理解したいだけで、何がしたいという訳ではないです

調べてみましたが以下の理解で合っていますか？
・シリアル値はDouble型の情報
・日付(Date型)はシリアル値の表示形式を"yyyy/mm/dd h:mm:ss"に変更しているイメージ
　内部ではミリ秒含むシリアル値を持っており、ミリ秒以下の情報が九められた訳ではない
・シリアル値とDate型はCDbl とCDateで相互変換可能。このときもミリ秒以下の情報は丸められない

・ミリ秒が丸められるのは、Date型をセルに＝で代入したとき
　これは、Date型の表示をセルにコピーしているため
・セルにDate型でなくシリアル値を代入することで回避できる

2021/03/26(金) 12:08:12.92

（´-`）.｡oO（「丸める」をどう入力したら「九める」となるのだろう・・・知らんけど）

2021/03/26(金) 12:44:01.98

>>57
本当の意味合いではかなり違う気もするけど
イメージ的にはそんな感じかな

2021/03/26(金) 16:10:18.31

>>58
手書き入力なんじゃないの
知らんけど

2021/03/26(金) 17:55:08.09

>>57
Excelの内部データ形式は突き詰めない方がいいよ。

それにセルの書式の組み合わせもあるから、初心者は気をつけてね。

2021/03/26(金) 18:28:35.59

ありがとうございます
これ以上は突き詰めないようにします
ここまで解説してるサイトはほとんど見当たりませんでしたが、
Application.Waitの引数にVBAの関数を使うと何故精度が低くなるのか？など
ここまで理解して初めて分かることもあったので良かったです

ちなみに丸が九になったのはパソコンで打った文章をスマホで取り込んだからですね

2021/03/26(金) 20:17:02.94

馬鹿は入力機器で漢字コードが決まると思っているのか

2021/03/26(金) 20:24:40.81

漢字コードとは古臭いw

2021/03/26(金) 20:27:23.31

JISコード時代の俗称だぞ。

2021/03/26(金) 20:58:19.29

JISとかEBCDICとか古いこと自慢か？

2021/03/26(金) 22:02:40.63

5chはジジイの巣窟です
昔話に花が咲くのです

2021/03/26(金) 23:45:57.50

漢字ROMの時代からやってきた過去の人

2021/03/27(土) 20:27:21.67

質問させてください
worksheetfunctionでsumifやcountifを使用する際の範囲指定はrange(cells(),cells ())と指定する方がいいのか、cells ().resize()と指定する方がいいのか、どっちでしょう？
皆さんはどっちを使用していますか？

2021/03/27(土) 21:10:41.58

rangeは特殊だからはまりやすい。

2021/03/27(土) 21:23:53.88

範囲をどうやって決めているかによる

2021/03/27(土) 21:28:05.27

一般的な場合として、例えば最大行までの範囲とした場合はどうでしょうか？
初心者なので、最近、resizeを学んだばっかりなのですが、resizeの方がすっきりしてるような気がするけど、rangeの方がパッと見、分かりやすいよなぁと思いまして

2021/03/27(土) 21:49:27.21

たしかえくせるちゅんちゅんでそんな解説記事あったよ
おれは範囲指定だったらrangeかな
resizeは増やす値をちゃんと考慮しないと間違えやすいんだよな

2021/03/27(土) 22:29:55.09

>>72
その程度ならお好みでどうぞ
値貼り付けとかやる場合は、range(cells(),cells ())なんか使ってられないから、Set、Offset、Rresizeの組み合わせを使っているな

2021/03/27(土) 22:36:49.88

テーブルにすればRangeでCellsを囲う必要がなくなるから、まずテーブルにできるかから考えてみれば

2021/03/28(日) 12:09:35.83

現在6秒に1回実行するマクロを組んでいるのですが、
複数のエクセルファイルを開いてそのマクロを実行すると、
マクロを実行しているファイルとは別のエクセルファイルを
マウス等で触ってアクティブにすると、関係のないそのエクセルファイルで
そのマクロが実行されて、肝心のマクロが肝心のファイルで実行されず、
エラーになるという現象で悩まされています。
例えば、ファイル名「A」、ファイル名「B」のエクセルファイルがあり、
ファイル名「A」の中にマクロでセルF1を6秒間隔で選択するコードがあると、
6秒間隔で更新している時にファイル名「B」を触ると(アクティブになる)、
ファイル名「B」のセルF1を選択してしまうのです。本来はファイル名「A」
で組んでいえるのでファイル名Aで実行されなければいけないのに。。。
ファイル名、シート名を指定して他のファイルでは実行できないようにコードを
入れてみたのですが治りません。どうすればよいのでしょうか？
ご教授お願いします。

**デフォルトの名無しさん** (JP 0Hd5-UUyU) · 2021/03/28(日) 12:41:13.75

>>76
コードの中の全部のRangeやCellsにもブック名とシート名を書いてある？

2021/03/28(日) 12:53:44.04

面倒なのは分かるが意図しない動作をする時はF8で1ステップずつ挙動を確認したりデスクに適当な人形をおいてコードを１行１行何をしている行なのかを人形に対して声に出しながら説明すると原因を突き止めやすいぞ

2021/03/28(日) 13:27:37.72

当該ブックのアクティブ化とCallだけでやろうとしている？

これかしら？
Application.Run ‘パス+ブック名’!マクロ名

https://excelwork.info/excel/runmethod/

2021/03/28(日) 14:55:28.65

thisworkbookでいいんじゃね

2021/03/28(日) 15:28:42.70

>>76
これだとアクティブなシートの[A1]に入力されます
Range("A1")=1

これだと指定したファイルの指定したシートの[A1]に入力されます
Workbooks("Book1").Worksheets("Sheet1").Range("A1") = 1

2021/03/28(日) 17:37:33.16

まーおそらく値の設定時に
ちゃんとどのブック、どのシートって
設定されていないんだろうけど
もっと気になるのは6秒というスパンの中で
処理が6秒を間に合わなかったらどうするのか
ちゃんと考えて作っていのかってことかな
大きなお世話かも知れんけど

2021/03/28(日) 18:29:23.67

セルの編集は6秒あれば楽勝です

2021/03/28(日) 20:03:24.72

>>40
自分が書き込んだかと思ったわw

2021/03/28(日) 20:31:26.17

UIAutomationでInvokeをするVBAマクロをタスクスケジューラで
日次・定時に起動しているのですがロック画面ではInvokeが実行されません

解決策をご存知でしたらご教示お願いします

2021/03/28(日) 21:54:46.91

ロック画面にしない

2021/03/29(月) 18:09:13.47

もはやexcelの問題じゃねーし

2021/03/29(月) 19:13:47.90

なんでFINDで探せないんだろ
とおもったら
改行コードがはいっていたでござる

やれやれ

2021/03/30(火) 17:58:10.32

質問です

前はExcel2010の時はレジストリを維持って、VBAファイルだけはインスタンス起動が出来るようにするとか可能でしたが
2019にするとインスタンス起動ができなくなってました。
.xlsmファイルだけインスタンス起動するとか方法はないでしょうか

2021/03/31(水) 04:17:21.25

右クリックして[新規] じゃだめなのか？

**76です。** (ﾜｯﾁｮｲ b163-VcSF) · 2021/03/31(水) 18:32:23.42

回答くださった皆さん本当に有難うございます。
これから回答いただいた対処法を全て1個1個試していこうと思います。
なお6秒に一回とは、エクセルのセルに株価をリアルタイムで
反映するソフトがあるのですが、それで株のアラート機能を
VBAで自作で作っているので必要なのです。
ではでは。