Excel VBA 質問スレ Part69

2020/12/13(日) 00:22:23.67

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

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

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

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

2021/02/13(土) 11:41:30.80

>>611
どんだけ凄いの？
だれか40歳にもわかるように説明して

2021/02/13(土) 12:21:31.92

>>633
例えば、A1, B1, C1セルの中身をカンマで繋いでD1に格納したい
その際セルが空なら"[空だよーん]"ってしたいとするでしょ
今までは
D1: =TEXTJOIN(", ", FALSE, IF(A1 = "", "[空だよーん]", A1), IF(B1 = "", "[空だよーん]", B1), IF(C1 = "", "[空だよーん]", C1))
って似たようなIF式を何度も書く必要があったけどLAMBDA使えたら
f: =LAMBDA(x, IF(x = "", "[空だよーん]", x))
って定義しておけば
D1: =TEXTJOIN(", ", FALSE, f(A1), f(B1), f(C1))
って書ける
さらに課長に"[空だよーん]"とかふざけんな、"[空でございます]"にしろって言われてもLAMBDAの１箇所を直すだけで済む

2021/02/13(土) 12:41:35.80

>>633
40のお前がバレンタインデーでチョコ貰えるくらい凄い

2021/02/13(土) 12:49:30.81

>>635
(>_<)\

2021/02/13(土) 14:07:20.92

ただの関数定義じゃないか

それで十分だった気はするが

2021/02/13(土) 15:14:43.31

VBAに影響ほとんどなさそう
一発でピボ集計とかシート挿入とかに関係ないし

2021/02/13(土) 15:33:24.19

と言うかラムダ式書けるなら、ついでにセル内でも数式の改行とインデントができるようにしてくれればよかったのにな
Excel職人のIFだらけの数式を読むのがまずキツイんだは

2021/02/13(土) 15:40:56.55

セルで分ければいいんだけど一つの数式にまとめられるのがすごいと思ってるからな
まあ自分もそうだったけど

2021/02/13(土) 17:28:35.52

単に複数セル使うのが嫌なだけってのが多いように見える

**デフォルトの名無しさん** (JP 0Hdb-DrOF) · 2021/02/13(土) 18:42:07.48

まとめて印刷とか名前を付けて保存みたいな手作業を置き換えるVBAはなくならないかな

2021/02/13(土) 19:19:56.21

>>639
> セル内でも数式の改行とインデントができるようにしてくれればよかったのにな
それ昔からできるぞ…
https://www.becoolusers.com/excel/img/long-formula-line-break20.png

2021/02/13(土) 19:28:08.81

営業の要ミーティング恐い((((；ﾟДﾟ)))))))

2021/02/13(土) 21:29:40.36

>>639
普通にやってるけど。
読むのキツイんだろ。

2021/02/13(土) 21:48:38.47

うかる！って、説明ヘタだね…

2021/02/13(土) 22:16:40.52

職場にIf and orの職人がいるオレに謝れ

2021/02/13(土) 23:44:53.12

んー？　今までセル内の数式って受身の動きだったけど
それ実装すれば自発的に動けるようになるってこと？

2021/02/14(日) 00:40:24.25

>>643
まじか、しらんかったわ
勝手にスペース削られたりするもんだと思ってたわ。。

2021/02/14(日) 00:53:19.53

>>648
自発的には動かないよ
今まではVBAでしかユーザー定義関数は作れなかったけどVBA使わなくても作れるって言うだけ
ただマクロを有効にする必要がないから実務としてはそれなりに意味はある

2021/02/14(日) 00:53:54.05

>>649
まあ俺もつい1年ぐらい前に知ったんだけどw

2021/02/14(日) 05:33:27.25

それよりかパイプライン演算子を実装して欲しい
ラムダ関数はその足掛かりな気もするけど

2021/02/14(日) 08:25:32.74

セルの書式を調べる関数はよ

2021/02/14(日) 09:01:53.08

>>653
Cell("format", a1) じゃ駄目なの？
https://dekiru.net/article/4431/

2021/02/14(日) 09:44:10.83

EXCELって意外と古いの使ってたりするから受け渡しがどうかな

2021/02/14(日) 09:46:12.37

>>652
一部がダメ文字の文字コード

2021/02/14(日) 10:33:56.69

>>654
ああごめん
表示形式以外の書式ね
色とかフォントとか罫線とか

2021/02/14(日) 10:46:19.10

もしかして >>653 は
データの「表示形式」だけでなくて
フォントや横位置/縦位置、塗りつぶし、罫線なんかの戻り値が欲しいのかしら？

2021/02/14(日) 10:47:17.03

ありゃ。本人からの返事の方が早かった

2021/02/14(日) 11:28:53.22

>>657
4.0マクロ関数でどうぞ
https://blog.goo.ne.jp/koyaman2/e/58506c9b0f18f1577aa694ab4af30edf

2021/02/14(日) 16:36:27.43

VBAはSelectのCaseで、False,Empty,Nullあたりが同一判定されて、個々に行えないのがクソ

2021/02/14(日) 17:23:19.56

>>661
VarTypeも知らない僕はアホです
っていうカミングアウトか？

2021/02/14(日) 19:08:55.75

ねこ

2021/02/15(月) 11:39:26.92

DIR関数を利用して以下のりようなロジックを見たのですが、
foldercheck = (Dir(wrkpath, vbDirectory) <> "")

()の中のロジックはどのような技法なんでしょうか
if Dir(wrkpath, vbDirectory) <> " then

**664** (ﾜｯﾁｮｲ 53fd-OxJ8) · 2021/02/15(月) 13:15:47.99

https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/comparison-operators

Dim MyResult, Var1, Var2
MyResult = (45 < 35) ' Returns False.
MyResult = (45 = 45) ' Returns True.

自己解決しました。上記と同じなのですね。お騒がせしました。

2021/02/15(月) 19:45:33.20

フォルダへの書き込みのアクセス権をチェックするようなサンプルないですかね。

イメージとしてはフォームのフォルダ選択ボタンを押して選んだフォルダが書き込みできなければ選んだ時点でそういうメッセージを出したい。このあとアウトプットフォイルを書き込む際にエラーが出る前に

**デフォルトの名無しさん** (JP 0Hdb-DrOF) · 2021/02/15(月) 20:10:33.97

>>666
まじめにやるならGetFileSecurityとGetAclInformationを使う
もっと簡単にやりたかったら、ダミーのファイルを1つ作ってみてOn Errorでエラーになったかどうか判定

2021/02/15(月) 20:15:42.96

FSOのGetFileでフォルダーを取得して
Attｌibutesプロパティ見ればいいんでないかな

2021/02/15(月) 20:16:42.83

>>668訂正
GetFile　→　GetFolder
だった

2021/02/15(月) 20:30:32.32

>>668
いいわけないだろw

そもそもフォルダのR属性は他の用途に使われている

2021/02/15(月) 22:00:51.83

普通にicacls呼べばいいじゃん

2021/02/15(月) 22:59:53.76

>>662
いや、それだと0と1がIntegerとしか返ってこないし、
TrueとFalseもBooleanとしか返ってこないから判定できないだろ。バカかお前？

2021/02/15(月) 23:48:13.25

isnullはあったよなたしか

2021/02/16(火) 02:55:26.52

CSV形式で保存する前に、Serial値の表示形式をyyyy/m/dにマクロから変更して
保存してからその後開くと、"1/1/2020"とか意味分からんStr型になっちゃうんだけど、
誰か分かるひといる？
csvを取り込むソフトがどうしてもこの表示形式に変更しないと、うまく取り込めない謎仕様なんだ。
ちなみに、いったん保存してから開いてやってもダメだった。
（もちろん、手作業で開いて表示形式変更したらバグらないんだけど・・・）

rngObj.NumberFormatLocal = "yyyy/m/d"

2021/02/16(火) 07:43:45.82

>>672
型を判定してから値を判定すりゃいいだけだろ
応用力ないのか？

2021/02/16(火) 10:30:54.61

整数の1と小数の1.0だと値(バイナリ)が違うって知ったら狂いそうだな

2021/02/16(火) 11:04:29.70

>>674
いま手元にExcelないから確認できないけど
rngObj.NumberFormat = "yyyy/m/d"
か
book.SaveAs …, Local:=True
に、してみたらどうかな

2021/02/16(火) 11:41:24.25

2021/2/15 6:43

みたいに、時間が入ったセルの、
日付の部分
だけ変えたいんですがやり方がわからないです

セルの日付(2021/2/15)を変数で定義して、新たな日付(2021/2/16)の変数も定義して
replace(セルの位置,セルの日付,新たな日付)
で入れ替えようとすると型が合わないって出てしまうのですが
どうすればいいでしょうか？

2021/02/16(火) 11:56:30.35

>>678
もしかして変数で定義してる型がDateだったり？

2021/02/16(火) 11:58:31.61

>>679
dateとstring両方試しました

2021/02/16(火) 12:13:25.96

CStr(Format(a,″yyyy/m/d“))

2021/02/16(火) 12:24:02.20

すいません自己解決しました
変数の定義がおなしかったみたいです

2021/02/16(火) 13:04:12.03

>>677
いけましたサンクス！！！

**666** (ｵｯﾍﾟｹ Src7-Wb2C) · 2021/02/16(火) 19:15:33.72

遅くなりましたが、結局Dummyファイルの書き込みにしました。アドバイス頂いた方ありがとうございました。

2021/02/16(火) 21:00:08.21

スミマセン、セルの特殊な機能だと思うのですが、

セルにポインタを合わせると、セルの外側右下に小さな▼が出現、
▼を押すとセルの下に小さなウィンドウが展開し、羅列された文字や数字を選択できる。

上記機能の名称とは何なのか、
どこでイジれるのか、作成できるのか等わからず困ってしまいました。

機能が生きているセルもありましたため、
新規で作成せずともその機能をコピーしようともしましたが方法わからず。

お詳しい方いらっしゃいましたら
お力添え願いたいです。

失礼します。

2021/02/16(火) 21:24:43.99

VBAとは関係なく
「セルの入力規則」から「リスト」を選択してあとは適宜
詳細は上記キーワードで検索して

2021/02/16(火) 22:46:03.00

教えていただけると助かります。
VBAでIEからExcelに文章を写したいです。
アクティブなIEからカーソルで範囲指定して、その内容を写すことは可能なのでしょうか？

例えば、ヤフーで気になる記事の内容だけをコピペといった感じです。
気になるものだけなので、検索を入れ込む必要はないです。

IEとYahoo起動まではできるのですが、その先が分かりません。

よろしくお願いいたします。

2021/02/16(火) 23:02:29.70

それ手動でコピペしたほうが早いんじゃね

2021/02/16(火) 23:41:10.89

気になる記事の条件は?
それを自分で判断するなら>>688が正解だと思うが

2021/02/17(水) 09:01:14.53

>>687
ちったぁググれ
https://konowaza.com/excel/copy_paste_macro.html

2021/02/17(水) 10:16:38.01

>>690
優しすぎる

2021/02/17(水) 12:53:19.33

>>686
ありがとうございますm(_ _)m
トライします。

2021/02/17(水) 12:57:54.00

いえいえ

**デフォルトの名無しさん** (JP 0Hdb-DrOF) · 2021/02/17(水) 12:59:55.94

クリップボードを監視して、新しいデータがコピーされるたびに保存してくれるアプリとか使えばいいんじゃない？
いちいちウィンドウを切り替えたりペーストしなくていいやつあるだろ

2021/02/17(水) 15:46:44.57

>>686
機能が生きているセルを上記指南で解析し、
貼り付けで解決しました！
助かりました。
ありがとうございました！

2021/02/17(水) 22:01:12.58

最終列までコピーをしたいんですがエラーを繰り返しなかなか実現できません…
例えばA3セルがデータの始まりでA7セルが最終列の場合、以下の文になるかと思いますRange(“A3:A7”).copy

ただ、半年に一度最終列が一列追加になるので、メンテナンスしなくていいように上の文のA7の部分が最終列になるようにしたいです

この場合何かいい方法はあるでしょうか

2021/02/17(水) 22:41:15.13

行じゃなくて列？

2021/02/17(水) 22:42:03.66

オーソドックスなのはこれだけど、物凄い珍回答を聞いてみたいわ
Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row).Copy

2021/02/17(水) 22:45:44.66

>>696
last_row = Sheet1.UsedRange.Row + Sheet1.UsedRange.Rows.Count - 1
last_col = Sheet1.UsedRange.Column + Sheet1.UsedRange.Columns.Count - 1
Debug.Print "A3:" & Sheet1.Cells(last_row, last_col).Address(RowAbsolute:=False, ColumnAbsolute:=False)

こんなん？

2021/02/17(水) 22:46:54.85

>>690

ありがとうございます❗
探してたものに出会えました。
ググってはみたんですが、このサイトには巡り会えなかったので、本当に助かります。
やってみたら、思う結果になりました。
ありがとうございました。

2021/02/17(水) 22:47:30.28

行じゃなく列です
最終列が半年に一回追加になるのです…

2021/02/17(水) 22:50:45.05

>>697
ごめんなさい、最終列の話だったのでA3セルからD3セルをコピーする場合、以下になるんでした
Range(“A3:D3).copy
今はD3が最終列ですが、半年後にはD3が最終列になるイメージです

**696** (ﾜｯﾁｮｲ f215-LlBx) · 2021/02/17(水) 22:51:42.12

何度も間違えてすみません…
今はD3が最終列ですが半年後にはE3が最終列になるの間違いでした

2021/02/17(水) 22:55:52.03

最終列取得の仕方調べないの？

2021/02/17(水) 23:06:07.99

Range("A3").CurrentRegion.Copy

2021/02/17(水) 23:08:53.17

ColLst = Sheet1.Сells(3,Columns.Count).End(xIToLeft).Column
Range(Sheet1.Cells(3,1),Sheet1.Сells(3,ColLst))
みたいにケツのColumnIndexを可変化させりゃええ
指定範囲より右側にコメントが入るのであれば別の方法を考える必要があるけど

2021/02/17(水) 23:09:11.52

CurrentRegionは使えそうで使わない

2021/02/17(水) 23:16:43.14

UsedRangeだって使うの怖ない？
どっかにドットあるだけども持ってかれる

2021/02/17(水) 23:18:02.78

これってやり方は何通りかあるけど、
どの状況でも使える完璧なのが無いよな
一見よさげなUsedRange.Find法とかも、
検索と置換の状態が変わっちゃうし

2021/02/17(水) 23:21:06.27

ちゃんとやりたい場合列のヘッダの名前の有無を確認とか意味解析するしかないよ

2021/02/18(木) 00:18:06.29

vbaしかつかえないうんちなんだけど、ダイアログに変数名入れたら、その名前を反映させた定型コード作ってくれるような支援ソフトないの？
たとえば、Dim var: var= xとかFor i=LBound(arr) to UBound(arr)とか。いちいちめんどい。
cliborはつかってる。スレチだったらすまそ。

2021/02/18(木) 02:16:18.22

それこそ自分で作れよ
自分だけにに都合よく自動化したいならそれしかないだろ

2021/02/18(木) 08:21:36.53

>>711
そういうの別言語だと見たことあるけど
VBAでは諦めた方がいいんじゃないかなぁ
https://mobile.twitter.com/johnsundell/status/1221758718188060673
こういう奴でしょ？
https://twitter.com/5chan_nel (5ch newer account)

**696** (ﾜｯﾁｮｲ f215-LlBx) · 2021/02/18(木) 08:30:28.40

最終列の取得をしてコピーしたいと申した者です
具体的には2シート目以降のC8セルから最終列までコピペしたくこのような文を書いてました

Maxcol = Worksheets(1).Range(“C8”).End(XlToRight).Column

For i = 2 To Worksheets.Count
Worksheets(i) = Range(Cells(8,3), Cells(8, Maxcol)).Copy
Worksheet(1).Cells(Maxrow,1).PasteSpecial Paste:=xlPasteValues
Maxrow = Maxrow + 1
Next i

上には書いていませんがMaxRowで最終行していて、1シート目の最終行にペーストするイメージです

このとき以下の箇所でエラーになりました

Worksheets(i) = Range(Cells(8,3), Cells(8, Maxcol)).Copy

2021/02/18(木) 08:31:50.99

>>713
やっぱVbaだと厳しいか、、、別言語だとこんなのあるんだな！ありがとう。

**696** (ﾜｯﾁｮｲ f215-LlBx) · 2021/02/18(木) 08:45:37.53

解決しました…
Rangeの中にCellsを入れる際はCellsの方にもワークシート名を記載してあげなければいけなかったんですね…
お騒がせしました

2021/02/18(木) 10:27:10.11

4点指定でrange作れればいいのにね

2021/02/18(木) 13:38:16.10

>>717
まあ、
Sheet.Cells(開始行, 開始列).Resize(行数, 列数)
もしくは
Sheet.Cells(開始行, 開始列).Resize(終了行 - 開始行 + 1, 終了列 - 開始列 + 1)
ってやるだけだし

2021/02/18(木) 17:41:06.06

>>714
これで動くのか
それならまあいいけども

2021/02/18(木) 20:47:31.34

コンパイルしたときに「ユーザー定義型は使用できません」といったようなエラーが出るのですが、
エラー箇所が選択されず特定できません。
ググってみたのですが「関数を一個ずつ切り貼りしながらコンパイル」的な記事しかなく、
結構大きなプロジェクトファイルになってしまっていて現実的ではありません。

原因って何が考えられますでしょうか。

2021/02/18(木) 21:08:20.83

情報が少なすぎるだろ
で、ユーザー定義型を使ったの？

2021/02/18(木) 21:48:45.33

>>720
VBAでは珍しいかも知れないけど
VBSではよくある話
実行前コンパイルの概念がないからね

別に1関数単位で抜かなくても
纏めてごっそり抜いてみて
エラーが発生しなくなったら
残った部分には原因がなくて
抜いた部分に原因があるんで
まずそうやって切り分ける

抜いた部分から半分くらい戻してやって
またエラーが起きないか確認するのを
繰り返して行く内に
原因の可能性がある範囲はどんどん狭まるんで
そうやって確認して行く

2021/02/18(木) 21:52:25.45

原因として考えられるのは
ない型を指定した場合なので
型宣言の書き間違えとかかな

2021/02/18(木) 21:59:48.04

>>721
使ってます。
また、クラスモジュールやライブラリも沢山使ってます。
以前なら間違っている名前の箇所が選択されるので、直ぐに原因が特定できたのですが・・・

>>722
今、出来る範囲でモジュールを解放・インポートを繰り返して検証して見たのですが、
フォームモジュールのいくつかが原因の様で、これらを抜くとコンパイル（実行前コンパイル）が成功します。
ただ、その原因と思われるモジュール内の全てのコードを削除しても同じエラー発生します。
意味が分かりません・・・

2021/02/18(木) 22:36:52.79

フォーム内のエラーはフォーム呼び出しコードが選択されるから、F8の手押し連打で進めて行けばエラー箇所で止まるんじゃね

2021/02/18(木) 23:05:06.07

いつもの人っぽいけど、クラスモジュールでエラー止める設定すれば分かるかもしれん

2021/02/18(木) 23:14:40.89

>>726
シートに書くとエラーの場所がわからないから標準モジュールに書け、
なんて書いてるサイトがあって、その解決策しばらく気づかなかったわ

2021/02/18(木) 23:38:17.80

ズボラせず1個ずつちゃんと調べなよ

**デフォルトの名無しさん** (JP 0Ha7-W5tZ) · 2021/02/19(金) 00:07:23.60

DimとかConstとかでタイプミス
SubやFunctionの宣言部でもよくある
Visual StudioでVB.netプロジェクトを作ってコピペで発見できる場合もある

2021/02/19(金) 03:59:43.95

>>725,726
実行時例外じゃないです。

>>728
>>724の通り、発生元と思われるモジュール内コードを全て削除しても発生し、
モジュール自体を解放すると発生しなくなるため悩んでいます。
私も一個づつ調べたいのですが、もはや調べ方も分からない状況です・・・。

>>729
宣言文のタイプミスでも、通常はエラーが発生すれば原因箇所が選択（反転）されると思いますが、
今回はそのエラー箇所の選択がなく困っています。

vscodeですがVB.netプロジェクトへのコピペもやってみましたが、
関係のないエラーや注意が大量発生して特定困難でした。
というか互換性ないと聞いたのですが・・・

2021/02/19(金) 04:14:33.39

お礼言うの忘れてました。
沢山回答ありがとうございます。
些細な事柄でも気になる点があればレス欲しいです。

一応質問内容をまとめます
・エラーが発生しているのはコンパイル時です
・通常あるエラー発生箇所の選択がされません（この「発生個所が選択されないという現象」自体の原因も調べています）
・原因と思われるクラスモジュールを解放すると発生しなくなります
・しかし、そのモジュール内のコードを消すだけでは引き続き発生します

2021/02/19(金) 05:06:58.61

なるほど
つまりVBAのコード側の問題ではないと

詳しい状況が分からんから何とも言えんけど
考えられる状況としては
・ファイルがぶっ壊れてる
・フォーム側に使ってるバージョンにそぐわないオブジェクトが設定されている
・使ってるバージョンにそぐわないAPIとかが使用されている
・何か変な参照設定切ってそれを使ってる

とかじゃない？

クラスとかライブラリ(何をもってライブラリと言ってるのかは分からんけど)使ってて
結構大きなプロジェクトファイル使ってると言うなら
元々動いてたものだったと思うんだけど
今改修を掛けているものが同じ環境下(EXCELのバージョンとか使ってるならランタイムとかが同じ)
で動くか確認した方がいいんじゃないかと思うよ

2021/02/19(金) 05:44:12.15

>>731
クラスモジュールを開放したあと、同名のクラスモジュールを空で新規追加したらどうなるの？
そのクラスモジュールの名前は何？