Excel VBA 質問スレ Part51

2017/11/08(水) 11:26:30.13

スレ立ての際は一行目に
!extend:checked:vvvvv:1000:512
と入れてスレ立てして下さい

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

※関連スレ
VBAなんでも質問スレ Part2
http://mevius.2ch.net/test/read.cgi/tech/1432173164/
Access VBA 質問スレ Part1
http://mevius.2ch.net/test/read.cgi/tech/1328536426/
Excel総合相談所 126
https://mevius.2ch.net/test/read.cgi/bsoft/1496487719/

※前スレ
Excel VBA 質問スレ Part50
http://mevius.2ch.net/test/read.cgi/tech/

VIPQ2_EXTDAT: checked:vvvvv:1000:512:----: EXT was configured 👀
Rock54: Caution(BBR-MD5:0be15ced7fbdb9fdb4d0ce1929c1b82f)

2017/11/08(水) 11:28:45.81

早速質問
今vbaでマップ使って
とある表の集計を行なっております
一つのkeyに対しアイテムが複数あるので
配列で入れようと思ったのですがうまくいきません
何か良い方法はありませんか？

2017/11/08(水) 18:45:21.53

Dim dic
Dim a()

redim a(1)
a(0)="test1"
a(1)="test2"

Set dic = CreateObject("Scripting.Dictionary")
dic.Add "key1", "アテネ"
dic.Add "key2", "ベオグラード"
dic.Add "key3", a

msgbox dic("key3")(0)
msgbox dic("key3")(1)

2017/11/08(水) 19:49:47.05

前スレ
Excel VBA 質問スレ Part50
https://mevius.5ch.net/test/read.cgi/tech/1503805217/

2017/11/08(水) 20:24:39.43

マップをやめて配列「で」入れる
って意味にとってたけど
マップの中に配列「を」入れる
って意味かよ

2017/11/09(木) 13:04:15.65

アイテムが複数あっても同じkey 使えばいいんじゃねと思った

2017/11/09(木) 14:39:20.19

集計をしたいって事じゃない？

2017/11/09(木) 20:19:01.12

>>6
やってみてから発言することをお勧めする

2017/11/09(木) 20:29:22.40

いい言葉を見つけた
Those who know don't talk. (知ってる人は教えようとしない)
Those who talk don't know. (教えてる人はあまり知らない)

2017/11/09(木) 21:19:05.18

立て乙です。
自宅にエクセル環境が無いのでうろ覚えで
細かい間違いはあるかもですが、該当箇所以外はokという前提です

日本という名前のブック
各都道府県名のシート
があるとき

Dim Bn as Workbook
Dim T as Worksheet

Set Bn = Workbooks("日本")
Set T =Worksheets("東京都")

Workbooks("日本").Worksheets("東京都").cells(1,1) = "123"
Bn.T.cells(1,2) = "456"

を実行すると
上段は問題なく実行されて、東京都シートのセルA1に123が入力されますが
下段でエラーがでてしまいます。また

Bn.Worksheets("東京都").cells(1,2) = "456"
T.Worksheets("東京都").cells(1,2) = "456"

のどちらでも動きました
ブックもシートも両方変数で指定するとエラーになってしまうのでしょうか？
ブックもシートもそれなりに切り替えるので、可能であれば別で指定したいのですが……
心当たりがあればご教示いただけると助かります。

2017/11/09(木) 21:25:44.32

>>10
×　Bn.T.cells(1,2) = "456"
○　T.cells(1,2) = "456"

オブジェクトの操作は慣れが必要だけど、こういうもんだと思っておくれ

2017/11/09(木) 22:48:42.62

>>10
Bn.Tだと
workbookオブジェクトにTって言う属性や関数が無いからエラーになるのは当たり前

2017/11/09(木) 23:25:52.19

>>10
> Set Bn = Workbooks("日本")
> Set T =Worksheets("東京都")
ここは
Set T =Bn.Worksheets("東京都")
って書くべき

10 (ﾜｯﾁｮｲ 5f61-KQ8c) · 2017/11/09(木) 23:38:24.62

返答ありがとうございます。

やっぱり両方同時に変数指定はできないんですね……
何というか、指定先が変わる度にセットするのは見づらいような感じがしてまして

明日何とか考えてみようと思います。ありがとうございました

2017/11/09(木) 23:57:00.50

　　●●●宇宙の外側に何があるのか？●●●
　　http://jbbs.livedoor.jp/bbs/read.cgi/stud▲y/3729/1069922074/84-87

　　この掲示板(万▲有サロン)に優秀な書き▲込みをして、総額148万円の賞金をゲットしよう！(*^^)v
　　http://jbbs.livedoor.jp/stu▲dy/3729/ →リンクが不良なら、検▲索窓に入れる！

2017/11/10(金) 00:13:48.87

>>10
名前（Workbooks）が一致していればいいってものじゃない。
オブジェクト.メンバ　という構文なんだよ。メンバとはそのオブジェクトが持っているメソッドとかプロパティとかのこと。

しかし　Bn.T と書くと、オブジェクトとオブジェクトを . で繋いだことになり、これは意味不明なものになる。

2017/11/10(金) 02:06:21.33

コレクションかユーザー定義クラス使うかしたらいいと思うが

2017/11/10(金) 02:16:45.89

>>16
質問者はオブジェクト.メンバの構文の意味が分かってなくてオブジェクトブラウザも見たことない人なんだろうな

オブジェクトのメンバ名を指定してメソッド関数やプロパティ関数を呼び出すことで別のオブジェクトインスタンスのコントロールを取得しているんだってことが理解できてないんだろう

2017/11/10(金) 10:37:51.15

>>10
>T.Worksheets("東京都").cells(1,2) = "456"
これホントに動いたのか？
WorksheetにWorksheetsプロパティとか無かったはずだけど

何がやりたいかよくわからんが
とりあえずブック名とシート名受け取って該当Worksheet返す関数作ればいいんじゃないかと

2017/11/10(金) 12:27:51.12

ワークシートchangeでtargetのA1が代わったらコードが流れるようにしてて、それ以外はexitで抜けてるんだけど、コードがセルに転記してて毎回changeの所に戻ってはexit、戻ってはexitで見辛いのはなんとかなりませんか？

2017/11/10(金) 13:11:21.32

>>20
マクロの実行中に何度もセルに書き込みが発生する処理が美しくないので設計を見直す。

2017/11/10(金) 13:45:49.16

作った人に聞いてみたら？

2017/11/10(金) 17:25:45.54

EnableEvents=False
でイベント制御したらだめなの？

2017/11/10(金) 18:26:04.03

SolverOk関数のEngineまたはEngineDescの引数の規定値って何でしょうか？

2017/11/11(土) 03:04:03.09

>>24
俺の使ってるバージョンだとその引数が無いっぽいから分からんけど
オブジェクトブラウザで表示させれば分かるんじゃね

2017/11/11(土) 08:36:27.60

MP4ファイルのプロパティ値を取得するプログラムでエラーが発生してしまいます
★の箇所で「'NameSpace'メソッドは失敗しました：'IShellDispatch6'オブジェクト」のダイアログが出ます
参照設定Microsoft Shell Controls and automaitonを追加してもだめでした
解決方法がわかれば教えてください

Private Sub CommandButton1_Click()
Dim Shell, Folder
Set Shell = CreateObject("Shell.Application")
Set Folder = Shell.Namespace(range("A1").Value) ←←←★

2017/11/11(土) 09:03:00.19

>>26
こちらのサンプルでとりあえず動きましたお騒がせしました
Dim FSO As Variant, SHell As Variant, Folder As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SHell = CreateObject("Shell.Application")
Set Folder = SHell.Namespace(FSO.GetFile(Songs(1)).ParentFolder.Path)
Cells(1, 1) = Folder.GetDetailsOf(Folder.ParseName(Target), 0)

2017/11/11(土) 12:39:17.23

エクセル２０１３を使用しています。
今、日付を手入力しているのですが、何かで見たのですが
カレンダーから選択して入力できる機能があるのですがエクセルでもできる方法ないでしょうか？
いろいろさがした所アクセスはできるのですがエクセルは標準でもなく
カレンダーコントールなるものあるみたいですが、アクセスもインストールしていないので
VBAマクロの中にもありません

2017/11/11(土) 14:37:40.42

>>14
両方同時にではない。
Worksheetを変数に入れたということはブック情報も込みで入ってる。
だから便利なの。

Aブックのαシートを変数に入れてもその変数でBブックのαシートにアクセスできるわけじゃない。
君のBn.T式のやり方だとBブックのαシートにもアクセスできることになる。
それはBn.Worksheets(T)式と変わらない。
一方、もしBブックのαシートにアクセス出来ないのならBn.T式の意味が無い。
T式で良いわけだから。

2017/11/11(土) 15:59:40.18

>>27
動くのは結構なことだけどFSO無くてもファイル情報取れるよ。
他の部分で便利にFSO使ってるなら良いんだけどその部分だけ見たら無駄だよね。

Dim objShell As Object
Dim objFolder As Object
Dim objFItm As Object
Set objShell=CreateObject("Shell.Application")
Set objFolder=objShell.Namespace("C:¥Users¥Hoge¥Desktop")
Set objFItm=objFolder.ParseName("fuga.txt")
Debug.Print objFolder.GetDetailsOf(objFItm,3)

参照設定とか書いてるけど、それぞれの意味分かってる?
CreateObjectってのは、その場で名前を元にオブジェクトを作成する。
だからコード書いてる間にはどんなメソッドやプロパティがあるか分からないからインテリセンスでの候補が出ることもない。
参照設定は使うオブジェクトを事前に指定することでオブジェクトの型とかメソッド、プロパティにアクセス(インテリセンス等)しやすくするためのもので、CreateObject使ったり、オブジェクトの型をObjectにしてるんだったら意味がない。
CreateObjectで生成出来ないのも有るし参照設定のがある意味上位互換だけど人にコードを提示する場合は参照設定するよう指示も書かなくちゃなんないからCreateObjectでの例が多いんだけどさ。
もちろん、参照設定したら動くようになるなんてことも無い。

Dim objShell As Shell
Dim objFolder As Folder
Dim objFItm As FolderItem
Set objShell=New Shell
Set objFolder=objShell.Namespace("C:¥Users¥Hoge¥Desktop")
Set objFItm=objFolder.ParseName("fuga.txt")
Debug.Print objFolder.GetDetailsOf(objFItm,3)

2017/11/11(土) 16:02:23.93

参照設定は極力しないほうが良いよね
参照設定を忘れてドハマリすることがたまによくある

2017/11/11(土) 16:04:50.69

>>28
エクセルでも出来るんだけど非常に上級者向けなんです。
上級者でも自分で1から組めない人が多いんじゃないかと思う。

上級者って何?、お前の勝手な分類だろって言われればそうなんだけど。

2017/11/11(土) 17:24:06.37

>>28
エクセルアドインカレンダー
で検索すれば色々見つかるよ
使いやすいもの選べばよろし

2017/11/12(日) 05:18:36.72

>>28
UserForm使えば簡単に自作できますよ。
年と月用のテキストボックスをつけて、年と月それぞれ増減させるために、スピンドルボタンをつける。
日付表示用のラベルを必要数用意。
年と月のテキストボックスの値に応じて、ラベルの表示を変更。
ラベルのクリックイベントで、年と月のテキストボックスとラベルのテキストを連結して、日付に変換。

ラベルのクリックイベントは、クラスでまとめれば、クリックイベントをたくさん書く必要もありません。

2017/11/12(日) 06:52:57.92

スピンボタンな

2017/11/12(日) 11:00:33.46

コードっていろんなパターンあってたくさん書いてると思うんだけど、コードの保管ってメモ帳？？

2017/11/12(日) 11:17:54.47

>>36
エクスポートできるよ
gitとかsubversionとか使えばいい

2017/11/12(日) 11:23:55.96

連投すまんが、セルを方眼紙にして、フォームの中に直線コマンド、円コマンド、角コマンド等CADみたいなそれぞれ押したらそれが、反映されて作画って出来る？

2017/11/12(日) 11:28:28.89

>>38
できるかできないかで言うと出来る

各図形の位置に当るセルの背景色を変えれば良いだけ

2017/11/12(日) 12:15:44.63

>>36
ブログ作ってそこによく使うコードを置いてある
いつでもどこでも検索すれば出てくるので非常に便利

2017/11/12(日) 12:16:32.95

>>38
できるけどかなりめんどくさい
cacooとか既存サービス使った方が良いと思う

2017/11/12(日) 17:01:38.36

>>34
簡単じゃないだろw
しちめんどくせーよ

2017/11/12(日) 17:02:12.03

>>36
モジュールをそのまま保存できるだろ

2017/11/12(日) 18:22:11.84

>>42
他に手軽な方法がないからUserFormで作れるようにしておくのが一番いいぞ。

2017/11/12(日) 18:36:30.88

>>44
まあそれしかないのはそうなんだけどさ

2017/11/12(日) 20:02:01.58

vbのランタイムをインストールすればDateTimePicker使えるけど、Formでカレンダー自作しておけば簡単に使い回したり配布出来るから一個作っておくといいと思いますよ。

2017/11/12(日) 22:30:08.29

フォルダ1
┗XXX29年10月.xlsx
　XXX29年11月.xlsx
　YYY29年10月.xlsx
　YYY29年11月.xlsx
フォルダ2
フォルダN

フォルダ1内に今月のファイルがあったらコピーして名前を翌月に変更して保存
これをフォルダNまで同じように処理する方法教えてください

年月の取得はユーザーフォームが理想ですが全く分らんので
マクロ実行用ファイルのA1に年、A2に月入力して取得する感じを考えてます

2017/11/12(日) 23:03:42.29

>>47
今月のファイルを探したいならDate関数使えば今日の日付が出るからそっからformatなりyearとmonthくみあわせるなりで取得すればいいからわざわざ入力させる必要がないね
フォルダないのファイル名の取得ならググればいくらでも出てくるぞ
俺はfilesystemobjectを使うけど

2017/11/12(日) 23:12:47.43

>>47
まずは最初の部分だけ

今年 = Format(Now, "e")
今月 = Month(Now)
来月 = DateAdd("m", 1, Now)
年 = Format(来月, "e")
月 = Month(来月)

2017/11/12(日) 23:16:31.66

変数に残す必要なかった
今月 = Format(Now, "ee年mm月")
来月 = Format(DateAdd("m", 1, Now), "ee年mm月")

2017/11/13(月) 00:50:26.46

どうしてもカレンダー欲しいんだったらSysDateTimePick32使って頑張るだろうな。
面倒くさいけど他のコントロールもMSFormsを使わないウィンドウ作成もやったことある。
きちんとプロパティ作るの面倒くさいから使いたいものしか実装してないけど。

2017/11/13(月) 00:58:21.51

>>48
ファイル名取得にfilesystemobjectは激重だった気がする。
Dir関数で頑張る方がよっぽど速い。

これが原因でfilesystemobjectは使わなくなってしまった。
他では速いものもあるのに食わず嫌いになった。

2017/11/13(月) 01:02:34.35

>>52
処理対象のファイルのロングパスがDirの引数の字数制限を越えてるときはどうするの？

2017/11/13(月) 06:21:29.66

そもそも劇重なんて思ったことないが...

2017/11/13(月) 08:01:19.66

なんか別のものと勘違いしてるんじゃないか

2017/11/13(月) 08:26:57.35

たかがファイル名を取得するのに処理負荷を感じるほど重くなるとか考えられないな。
読み取り先が等速ドライブに突っ込んだCDだったとか、回線の細いNASだったとかなら分かるけど。

2017/11/13(月) 09:22:13.43

>>52
クラスにして使いやすくしてるならDir関数でもいいと思うけど生で使う気は更々起きねーなあれは
めんどくさすぎる
早い遅いは環境次第だろう
何をしようとしたか知らんが

2017/11/13(月) 12:42:29.30

またVBAでクラス語る奴が来ちゃったよ...

2017/11/13(月) 12:44:49.20

なんかWindows10でマクロの挙動が安定しなくなったんだけど、そういうのある？

2017/11/13(月) 13:17:08.13

>>58
語ってるつもりはないし、普通に機能としてあるのに使ってバカにされる筋合いもない

2017/11/13(月) 13:21:12.07

vbaでクラス使うメリットあるの？
一応あるインターフェースは使い物にならないし、継承も使えないし、コンストラクタに引数も設定出来ない。
vbaでクラス使うメリット教えて

2017/11/13(月) 13:21:56.90

>>61
カプセル化

2017/11/13(月) 13:43:14.72

>>61
たまにある
単体の処理してる間だけじゃなく、しばらくの間は状態を持っててほしいとき
ごく素朴な使い方しか出来ないし、しようとも思わないけど

2017/11/13(月) 13:48:11.54

というか、クラスを使うデメリットって逆に何

2017/11/13(月) 13:52:55.00

そもそもクラスが必要になるほど大規模なコードを書く用途にVBAは向いてない、使うべきじゃない

ちゃんとした開発環境を使え
会社が許可しないなら転職するか我慢するかの二択、あるいは出世して自分で規則を変えろ
仕事の能率がアップして利益が増えるんなら反対する理由はないだろ

2017/11/13(月) 15:44:46.11

自分の好きなコードが書けないから転職とかww

2017/11/13(月) 15:54:24.73

不合理なことを押し付けてくる会社なら、転職も選択肢ってことだろ。日本語ﾀﾞｲｼﾞｮﾌﾞ？

2017/11/13(月) 16:07:38.26

クラスを使うとシンプルに表現出来る場合がある。規模の問題では無い。
普通に使ってるとなかなか活用するイメージが湧かないけど、覚えておくと役に立つよ。

2017/11/13(月) 17:08:14.63

ここでも前に書いたけど、配列のラッパークラスとか、一度作ると便利よ
コレクションだと微妙に取り回し悪かったりするからね
これ以上はスレチかな

2017/11/13(月) 17:09:30.17

>>68
そうだよね

ただ、いちいちクラスモジュールを追加しなきゃならないから、使い捨てクラスをほいほい定義しづらい

2017/11/13(月) 17:20:46.73

FileSystemObjectのファイル一覧取得が遅いのなんて結構有名な話だと思うんだが。
ハードディスクにあるファイル名全部取ってくるとかすれば分かる。

昔、どっかに比較してる所が有ったんだが見つからないな。
Win32APIが一番速かったがDirでもそんなに大きな違いは出なかった。
が、FileSystemObjectは糞遅い結果になってた。
そもそも自分で組んだら遅くてネットで調べたらやっぱり遅かったという。

2017/11/13(月) 17:23:43.47

クラスは凄く便利だが、無理に使う必要は無いと思う。
個人的には仕様を考えた段階でクラス使うのがぴったりと感覚で分かる。
そういう時以外は使わない。

2017/11/13(月) 17:47:02.85

>>71
そうなんか
まあ、今んとこそんな大量のファイルのやり取りしたことないから体感したことなかったわ
まあ、その辺使うにしても生で扱うよりクラスでまとめちゃった方が取り回し良さそうだがね
その辺は好みなんだろうけどね

2017/11/13(月) 18:55:14.32

>>64
まともな言語ならクラスを使うデメリットはないけどVBAのクラスは制限が多すぎて使う意味がない

2017/11/13(月) 19:08:08.64

>>59
bit数の関係で安定しなくなったことはある

>>61
多分、ない
class使ったほうが見通しがよくなる場合はあるけど、
正直そんなに変わらんというか、めんどくさくなるだけの場合が多い気がする

2017/11/13(月) 21:36:08.79

この流れで突然クソみたいな質問するの恐縮なのですが…
共有で使っているExcelのデータの行がランダムに大量に消える現象が起きてます
多分誰かがオートフィルタかけた状態でうっかり削除したんだと思うんですが、
消えた行に全く共通点が無いので「マクロにウイルスが付いたんだ！マクロやめろ！」って因縁付けられています

まずVBAにウイルスを仕込むならともかく、VBAを使用しているせいでウイルスに感染するって無いですよね？
あとこのコードに行を消すような要素があるか見てもらいたいです
オートフィルタをかけるだけの簡単なコードなんで無いと思うんですが周りに誰も聞ける人が居ないので一応…
簡単な指示だけどすごく頻繁に使うので消したくないんです

Sub filter1()
Range("K2").AutoFilter Field:=9, Criteria1:="<>"
Range("L2").AutoFilter Field:=10, Criteria1:=""
Range("O2").AutoFilter Field:=13, Criteria1:="AA"
End Sub

Sub filter2()
Range("K2").AutoFilter Field:=9, Criteria1:="<>"
Range("L2").AutoFilter Field:=10, Criteria1:=""
Range("O2").AutoFilter Field:=13, Criteria1:=""
End Sub

Sub filter3()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub

2017/11/13(月) 21:42:36.96

シート保護かけてみるとか

2017/11/13(月) 21:45:59.98

>>76
ウィルスに感染していて、セキュリティをsageているためにウィルスが実行されているというパターンなら考えられるが
其れ以前の問題として
・オートフィルタをかける
・全体をコピー
・ペースト（値として貼り付けなど）
で行が減っている可能性のほうが高いと思う
この仕様
、意外と知られていないんじゃないかなぁ