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/12/26(火) 07:54:39.17

>>733
>>734

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

最悪、officeの再インストールも覚悟しています。
レジストリかも？とはITには言われましたが・・・・

新規ユーザーはまだ試していません。
セキュリティ対策ソフトはITの管轄なので、切れるかどうか不明ですが
聞いてみます。

こちらの書き込みは自宅からなので、次のレスが遅くなるかもしれません。
よろしくお願いします。

2017/12/26(火) 08:20:51.65

>>735
運用の話までは管轄外じゃね
あとは自分で考えりゃいいよ

2017/12/27(水) 09:27:07.83

>>719とか>>730とかのコードで意味わかんないところがいくつかある
（コードが間違ってるって意味じゃなくて、こっちの知識が足りなくて理解が追い付かないって意味です）
まず>>719は
なんでコレクションを宣言してるのか？
しかもなぜ宣言したのに使わないのか？
変数eは何のために使ってるのか？
ファイルのパスをsplitで分割してるけど、
ファイル名だったらinstrrevで\を探したほうが話が早いんじゃないか？
等々

>>730は
ループの中でdim使うのは大丈夫なの？
複数回宣言したらエラーになりそう
後、一番知りたいのが
If strFolder <> "." And strFolder <> ".." Then
これ、どういう意味なのか解説をお願いしたい
（書いてる人たちにケチつけてるんじゃなくて、本当に分らない事を教えて欲しいだけです）
どなたか教えてください、よろしくお願いします

2017/12/27(水) 09:45:06.41

>>738
コマンドプロンプトで cd .. って打ってみたら判る　
c:>test>test1> cd ..
親ディレクトリに移動する。つまり親フォルダの事

「.」　は「.」　カレントフォルダの事

2017/12/27(水) 10:22:00.13

寝ぼけて書いてたからめちゃくちゃだな^^;

c:\test\test1> というフォルダがあったとしてそこで
c:\test\test1>cd .. と打てば
c:\test> と親ディレクトリ（フォルダ）に移動する

2017/12/27(水) 10:45:52.46

>>738
ループ内でDimしてもエラーにはならないよ
その都度メモリが割り当てられるだけじゃないかな
フォルダ名の"."と".."は相対パスじゃなかったかな
一個上と二個上の親フォルダだったと思う
間違ってるかもしれないから調べてね

2017/12/27(水) 10:46:46.56

あ、間違ってたw
.がカレントフォルダで..がひとつ上の親フォルダね
すんませんな

2017/12/27(水) 10:48:05.37

あとsplitの問題に関しては読みやすさとか書きやすさじゃね
俺もよくやる
楽だからね

2017/12/27(水) 12:51:59.50

beforeStr = "あ12月い"

afterStr = beforeStr.sub(/\d\d月/) do |matched|
month = matched.to_i + 1
month = 1 if month == 13

sprintf("%02d", month)
end
puts afterStr

#=> あ01い

Ruby で、文字列を置換した。
こういう処理は、Ruby が良い。

PowerShell でも作ってみたが、簡単な置換方法がなかった。
置換時に、コールバック関数を取れる、sub のような関数が無かった

わかりやすいように質問者が投稿する時は、名前欄に、717 と入れてくれ

**744** (ﾜｯﾁｮｲ cf80-fDvV) · 2017/12/27(水) 12:58:13.04

間違っていたので、修正した

beforeStr = "あ12月い"

afterStr = beforeStr.sub(/(\d\d)月/) do |matched|
month = $1.to_i + 1
month = 1 if month == 13

sprintf("%02d", month) + '月'
end

puts afterStr #=> あ01月い

2017/12/27(水) 13:38:32.87

VBA勉強し始めて間もない時に組んだマクロに
replaceという名前のプロシージャを作りました
そのことはすっかり忘れてたのですが、最近になってそのマクロを手直しする必要があって
その中でReplace関数を使おうとしたらエラーが出る
おかしいなと思い調べてみたらSub replace()　を発見
プロシージャの名前を変えたら大丈夫かと思いきやなぜかどこかに何かが残っているようでエラーがでる
一旦そのモジュールを解放したらReplace関数は使えるようにはなりました
しかし、なぜかReplaceと打ってもreplaceとなる
replaceと打ってもそのままreplace
replaceをReplace関数と認識してくれてはいるものの、どこかに何かが残っている
他のブックでは起きないのでまぁそれほど支障はないものの、もし分かる方いらっしゃったら
どうすればいいか教えてください

2017/12/27(水) 13:47:19.08

Replaceで変数宣言してみ

2017/12/27(水) 15:20:29.55

>>746
大文字小文字は、現在開いてるコード全体で統一されてしまうからな。
変数でもプロシャージャでもいいから、定義し直すと内部で覚えてる大文字小文字が上書きされるぽ
この仕様のせいで、他人の書いたブック開いたまま修正保存するといつのまにか変わってたりする。
するとWinMergeとかでDIFFしたときにイラッ
独り言でした

2017/12/27(水) 18:43:06.62

プロシージャ外に書いたオブジェクトの寿命はいつまでなのでしょうか？
グローバル変数にオブジェクトを保存してそれをfunctionで返すシングルトンオブジェクトを作ったのですが、
プログラムが一度終了して再度起動した際もオブジェクトが残っており、ゴミデータでエラーが起きています。
一度ファイルを全て閉じるまでは残るものなのでしょうか？

2017/12/27(水) 18:55:21.26

>>749
グローバル変数ならエラーで止まるかブックが生きてる限りは生きてるんじゃね

**デフォルトの名無しさん** (JP 0H27-amUm) · 2017/12/27(水) 19:03:21.38

>>749
あとEndで止めると死ぬ

2017/12/27(水) 20:23:28.17

>>751
End なあ、せめて Class_Terminate()して欲しかった

2017/12/27(水) 20:40:54.92

>>732　です。

officeの再インストール試してみましたが
不具合解消せずでした・・・・無念

幸い既存マクロは動くので、新規作成と実行は
他のPCでやることにしました。

不具合はExcelだけで、Accessでは特に問題ありませんでした。
一応ご報告まで。

2017/12/27(水) 20:55:52.26

>>752
てか、マクロ終わったら初期化すりゃよくね？
できない仕様なの？

2017/12/27(水) 21:08:27.21

>>754
マクロ終わったら誰が初期化するん？

2017/12/27(水) 23:24:01.01

>>753
マクロの記録から操作を記録すると
標準モジュール作成される？

2017/12/27(水) 23:29:35.39

>>751

Endだとなにがまずいのでしょうか

2017/12/28(木) 02:02:28.11

>>757
Globalで保持していると想定していた変数がいつのまにかクリアされていて想定外の動作をおこし痛い目にあったことがあります。
調べてみたら、エラー発生時またはEnd通過時にGlobal変数の寿命が尽きるようで、、
それ以来、Global変数もEnd Statementも避けています。
記憶が必要な場合は、Cell値やValidation値またはDocumentProperty等を使うようになりました。

**デフォルトの名無しさん** (JP 0H96-FiVz) · 2017/12/28(木) 02:45:31.50

VBA初心者です
2つのセルがイコールになるように別のセルの値を変化させるというVBAソルバを作成したいのですが、エラーばかりで全く進みません・・・
現在は7行目から10行目まで構文エラーが出ています。
どなたかご教授願います・・・

Sub Solver()
i = 1
Do Until i = 313
'-------------------------------------------
Application.DisplayAlerts = False
SolverOk SetCell:="K" & i, ByChange:="J" & i
SolverAdd cellRef:=Range("K1:K313"), _
Relation:=2, _
FormulaText:=Range("L1:L313"), _
Application.DisplayAlerts = True
'---------------------------------------------
SolverSolve
i = i + 1
Loop
End Sub

2017/12/28(木) 05:04:43.84

>>747
>>748
ありがとうございました。

2017/12/28(木) 08:06:18.47

>>755
マクロ終わったらオブジェクト破棄するんじゃダメなのかと言うことだが
まあダメな使用なんだろうけど

2017/12/28(木) 08:14:47.46

イミディエイトウィンドウにあるオブジェクトのことだよね

2017/12/28(木) 17:26:42.52

>>749,758
そもそもプログラムが終了ってどういう事をさしてる？
ホントにプログラムが終了すれば、すべての変数の内容は失われるよ
たんに実行するコードが無くなっただけではプログラムは終了していない

Endはプログラムを終了させるけど、エラー出ただけでは終了するとは限らない
デバッグで再実行や変数見たりできるのはプログラムは中断したけど終了してないから

**753** (ﾜｯﾁｮｲ 5aeb-RYVm) · 2017/12/28(木) 23:10:38.77

>>756

753です。

マクロの記録そのものができないのです。
記録しようとすると「マクロの記録ができませんでした」と
エラーが出てしまいます。

新規作成と編集が全て制限されているようです。
セキュリティは「すべて有効」で設定しており、
そこでの問題は無いと思うのですが・・・・

2017/12/29(金) 04:00:07.55

>>759
"_"　は次の行をつなげるためにつけたのだろうけど
Application.DisplayAlearts = True の前の行にも "_" が付いているから
何か貼り付け忘れか、間違って削除してしまったものがあるのでは

2017/12/29(金) 04:08:47.98

セキュリティーレベル一番高いとダメなんじゃね

2017/12/29(金) 07:22:34.45

>>732
あー、昔そんな事象に当たったことあるよ
原因は同じかどうかは分からんけど
俺の場合とんでもなく深い階層のフォルダに置いてたら
VBEのシートモジュールが青く表示されてコードが作成できなくなった

そのファイル、とりあえず浅い階層のフォルダや
どこかのサーバに置いているなら
クライアント側にコピーして内容確認出来る？

2017/12/29(金) 08:53:00.31

732、753です
皆様レスありがとうございます。

作成の不具合は、全くの新規Bookでも起こります。
既存マクロの場合は、コードの表示と実行は行えますが
編集が制限されています。

問題が発生してから、他のPCで新規作成したマクロを
該当PCで実行しようとしましたが、それはコードの表示すら
できませんでした。ファイルサーバー経由とかではなく
デスクトップ上に置いて実行しても同じです。

Accessでは特に問題ないので、VBAそのものではなく、
Excel単体の何かだとは思うのですが、皆目見当がつきませぬ。

2017/12/29(金) 10:57:41.44

>>765
>>759です
一部コピペして使ったので何か消してしまったのかもしれません・・・
確認してみます、ありがとうございます。

2017/12/30(土) 12:22:18.46

CreateObject("System.Collections.ArrayList")ってやったら
.NETのArrayListがVBAで使えてしまったんですが
これ隠し機能とかじゃなくて正式に使っていいんですよね？

2017/12/30(土) 13:25:09.57

前にも話題に上がったけど
.NETのほんの一部はVBAからも使える
リファレンスにも書いてあったような気がする

他で代替できるのでArrayListをわざわざ使うメリットはないということで
そのときは終わったと思う

2017/12/30(土) 15:32:35.43

>>771
参考にします。ありがとうございました

2017/12/30(土) 15:33:00.15

>>770
.Net Frameworkの機能でコンストラクタに引数を渡す必要のないものは使えるらしい
>>771
ArrayListは各種メソッドが揃ってるからコレクションより使いやすいんじゃね？キーを使わないなら
ただ遅いのがたまに傷

2017/12/31(日) 08:34:16.68

>>772
Windows APIだって使えるんだぞ？

2017/12/31(日) 08:58:37.79

VBAの保存方法について
バックアップどうしてますか？
VBAのマクロだと戻る釦聞かないし、バグって復旧するとマクロの個所全部消えちゃうので２つくらいのバックアップを別ファイルでとってあるんですけど皆さんはどうしてますか？
まだ素人なので思ったのと違う処理になって戻せなくなることがしょっちゅうあるので
バックアップは大事

**775** (ﾜｯﾁｮｲ de81-DSDK) · 2017/12/31(日) 09:02:17.31

>>775
戻る釦ってまるい矢印の奴ね

2017/12/31(日) 09:09:26.61

>>775
バージョン管理システム入れてないなら同じになるでしょ。
俺も同じくでファイル名_Ver nnn.xlsmと言うかたちで3世代別フォルダに保存してるけど
（時たま忘れたりする^^;

2017/12/31(日) 09:49:30.47

マクロ有りエクセルファイル自体にデータを入れて運用するのと、
データは別エクセルファイルにしてマクロ有りエクセルファイルでデータ入りエクセルファイルを操作するのと、
どっちがメンテナンスしやすい作り方だろうか？
いつも悩んでしまう

2017/12/31(日) 09:52:29.70

アドインマクロにしましょう

2017/12/31(日) 11:06:17.25

マクロのバージョン管理ってどうやってます？
インポートファイルを分けすぎると不便なのでshareFanction見たいなインポートファイルに使いそうな関数全部入れてますが、初心者なもので開発中に関数の引数や戻り値を変えたくなるような場面が多々あり
今までのshareFanctionと互換性が保てなくなりますが、引数と戻り値を修正した関数以外でバグを潰したような関数は最新を使用したいです。
今はコピペで対応してますが

2017/12/31(日) 11:27:58.03

個人なのか仕事なのか書かないと有効なレスがつかないのではないかな

2017/12/31(日) 11:34:47.18

>>781
個人です
個人で開発、個人で使用です

2017/12/31(日) 11:36:51.05

>>782
ならGithubとSourceTreeで快適にできるんじゃないの？
俺も全然高度な使いこなしできてないけど楽ちんだ

2017/12/31(日) 11:55:17.97

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

ざっとみた感じソースファイルのバージョン管理はできそうですが、
例えばサーバーあるいはローカルマシン上にそのshareFanctionを使用している分散しておいてある場合、
それぞれのマクロを調べて更新したいファイルを選択、修正版マクロを適用する、
みたいな使い方ってできるんですかね？

2017/12/31(日) 11:57:21.02

>>784

>>例えばサーバーあるいはローカルマシン上にそのshareFanctionを使用している分散しておいてある場合、

修正
shareFanctionを使用しているファイルが

2017/12/31(日) 12:53:47.48

ヴァージョンって仕事じゃないからいつから何時まででUP履歴にしたらいいかわからなん
機能変更したら必ずかもしれないけど、個人用だとやりながら変更しているからバージョン数が半端ないことになる。

2017/12/31(日) 13:10:06.47

バージョン管理は大変だよな
インストーラーみたいなのつくってどうにかしようとしてるがうまく行くかわからん

2017/12/31(日) 13:25:39.05

>>786
> バージョン数が半端ないことになる。
問題ないでしょ

2017/12/31(日) 13:31:04.61

>>787
専用のソフトがなければ1sheetをマクロ関数管理用にして各関数内にver x.y.z(x変更で互換性無し、y変更で互換性あり、z変更は互換性ありバグ修正みたいにして)
モジュールをインポートしたファイルをオープン時に、イベント検知で所定の管理ファイルを検索、新しいバージョンがあれば自動で今までのモジュールを解放、y,zバージョンが最新のファイルをインポート
みたいにすれば実現可能そうですが結構手間がかかりそうで…

2017/12/31(日) 13:56:42.14

マクロで書けよ。
バージョン管理。

セキュリティが低下するけどな。

2017/12/31(日) 18:06:59.07

そもそも論として、たかがマクロでバージョン管理が必要になるような力作()作るなよw
キミたちは一体エクセルマクロで何を築こうとしておるのかねw

2017/12/31(日) 18:13:51.04

仕事でVBA扱ってると色々な理由から小さいマクロでもバージョン管理が必要になってくる。

2017/12/31(日) 19:27:51.32

>>791
内容がぐちゃぐちゃになったら困るものにはバージョン管理が必要さ
力作()でなくても再構築はかったるいからな

2017/12/31(日) 19:58:25.49

>>793
力作()じゃないのにぐちゃぐちゃになるってどんだけ当てずっぽうにマクロ書いてんだよw

2017/12/31(日) 20:00:31.71

社内に展開するとかでサポートが必要ならバージョン管理ぐらいは必須だろ
>>791は無知すぎ

2017/12/31(日) 20:24:53.68

>>795
確かにおまえらの考える事は見当もつかんけどなw
別にいいや無能思考に明るくならなくてもw

2017/12/31(日) 20:28:42.31

またメゾット君か。

2017/12/31(日) 20:42:44.41

メゾット君って雰囲気ですぐ分かるよね

2017/12/31(日) 20:54:32.08

OLDフォルダにファイル名の後に日付つけたファイルをコピーしてる

2017/12/31(日) 21:20:00.26

ていうかバイナリファイルをバージョン管理システムに登録したところで
単なる時系列バックアップにしかならんうえに
シートを更新しただけでバージョンが変わるから意味ないのでは

2017/12/31(日) 22:37:10.58

力作2.0

2017/12/31(日) 22:50:02.43

悩みはみんな一緒なんだね。俺はバージョンアップに悩んでアドイン化したが
これはこれで悩ましいところがあるんだな。シートを初期化（つまりClearとかClearContents）する
のにもよーく考えないと結構危ない。アドインはどのブックでも実行出来てしまうからPCに疎いヤツが使うのも考えると
別の方法を考えた方が良いんだろうかとか考えてる。

2017/12/31(日) 22:55:00.56

>>802
アドインてxlamのこと？
だったらそのシートは一切使っちゃダメだよ
プログラムモジュールだけの存在にするべき

2017/12/31(日) 23:12:43.15

>>803
そりゃ分かってるての。ブックを指定する時は「ActiveWorkBook」とするしかない（決めつけはよくないが）
からよーく考えてコードを書かないと危ない場合もあるって話。使う連中の9割はマクロどころかシート関数も
知らないような連中なんだから。

2017/12/31(日) 23:41:30.90

>>804

・処理をスタートするFunction や Subはアドイン側にはない。当該Book側にある
・そのFunction や Subがアドイン側の機能を使う

だとするとアドイン側に「ActiveworkBook」なんて書く必要はなくて、処理対象のSheetやRangeを
引数で受け取るだけになると思うんだがね

2017/12/31(日) 23:52:05.59

>>805
作業するBookはxlsxなんだよ。マクロはxlms側に書いてある。

なんでこうしたかというと、仕事柄、ブックは地方自治体に提出する場合があるから。

何の事かわからないだろうからもうちょっと書くと、測量データの野帳なんだよ。

**806** (ﾜｯﾁｮｲ 8aa4-ycq7) · 2017/12/31(日) 23:53:24.81

xlmsじゃねえや、xlamだな。

**デフォルトの名無しさん** (JP 0H27-GXwu) · 2018/01/01(月) 04:27:25.57

>>806
マクロ有効ブックから
マクロ無効ブックを開いて処理して保存して閉じる
という方法も有ると思う

2018/01/01(月) 09:37:05.14

自動処理したいなら>>808だろうし、操作者が開いて何かしたいならメニューに追加しとけばいいだけだと思う
>>806は無知なのかちょっと頭固いんだろうな

**デフォルトの名無しさん** (JP 0H27-QCE3) · 2018/01/01(月) 13:40:19.53

>>809
作業ブック側にコード書けないんだよ。知ったかすんなよボケ

2018/01/01(月) 13:55:03.62

>>810
作業ブックにコードなんか要らんだろ w
知らないなら黙ってろ
てか、アドインでメニュー追加なんて基本中の基本だと思うが
http://toshimana.hatenablog.com/entry/2014/01/26/204938

2018/01/01(月) 14:25:45.74

知らないことを素直に知らないと言いなさい

2018/01/01(月) 14:27:07.60

今年はいい年になるかなｗ

2018/01/01(月) 14:29:26.10

>>811
Workbook_AddinUninstall　てのは知らなかったな。
Workbook_Open　するのと効果は違うんだろうか？

2018/01/01(月) 14:54:13.21

またメゾット君が暴れてるな
あの馬鹿が出入りするようになってから荒れすぎ

2018/01/01(月) 15:45:18.49

>>814
よく考えろよ

2018/01/01(月) 16:11:19.12

>>816
おっと、 Workbook_AddinInstallを Workbook_AddinUninstallと書き間違えた

2018/01/02(火) 09:36:26.90

>>817
だとしてもだ
イベントの言葉の意味そのままだよ
よく考えろよ

2018/01/02(火) 12:29:02.41

>>818
「対象のアドインを参照設定しているブックを開いた」というイベントを想定してた
そうじゃないのね

2018/01/03(水) 10:57:08.56

>>803
そんなことはない。
設定値を保存するためのiniファイルの代わりにするとか、テンプレートの代わりにするとか、使い道は沢山ある。

2018/01/03(水) 11:46:24.84

アドインにするにしてもなんにしても運用がしっかりされてないと意味がない
ファイル名やシート名程度が保証されてないならマクロ化なんて諦めろ

2018/01/03(水) 11:59:35.70

>>820
設定値を保存するのはやめたほうが良いと思うな。
バージョンアップしたときに設定値がリセットされてしまうとか、エクセルバグの巻き添えでブック破損の危険性がある。
できるだけアドインは読み取り専用で運用するべきだと思う。
過去に内部シートに保存するように作って問題が起きたので、バージョンアップ時にxmlにコンバートさせた事があるわ

2018/01/03(水) 12:49:50.09

お前のバグを根拠にされてもなぁ

2018/01/03(水) 14:45:49.49

まあ、設定は外出しにするのが普通に安全策じゃね？
xlamのシートは明らかに利用してほしくないっていう思いが伝わる作りな訳だから

2018/01/03(水) 15:32:51.77

利用者が不用意にセルデータをクリアしてそのままセーブしても自覚すらしないかも

2018/01/04(木) 05:46:13.89

質問よろしくお願いします。

A列,B列,C列,D列
Aa,111,bbb,20
Bb,111,aaa,30
Aa,222,bbb,10
Bb,111,aaa,10
Aa,111,bbb,15

上記の様な表を下記の様にしたいです。

A列,B列,C列,D列
Aa,111,bbb,35
Aa,222,bbb,10
Bb,111,aaa,40

A,B,C列の値が一致したらD列を加算して行を削除するという処理です。データは約1000行程です。

2018/01/04(木) 06:37:20.49

1000行程度ならSUMIFSとかでできるんじゃないの

2018/01/04(木) 06:55:39.59

>>826
要件によるけどそういうビューを作ればいいんじゃね？
create view V as (
select A列,B列,C列,sum(D列) as D列
from 表
group by A列,B列,C列
)

2018/01/04(木) 06:57:20.78

すまん、寝ぼけててSQLスレと勘違いしてた...
>>828はなしで

2018/01/04(木) 07:39:39.14

>>826
見るからにデータベース向けの案件
集計するキーとなる列が左端一列、その右に合計対象の数値列という2列形式なら
データタブにある統合でできるんだが

2018/01/04(木) 08:01:11.56

>>827
すみません説明不足でした。
キー項目が1000種類以上あり、毎回CSVから読み込んでから処理をする為、事前にWorkSheet関数を作っておくのが難しい状況です。

何か手はありますか？

2018/01/04(木) 10:15:47.38

データベース系のデータは素直にデータベースソフトで管理しようよ・・・。
アクセスでやればマウス操作だけで終わる様な問題だよ。

2018/01/04(木) 10:22:48.62

>>831
ADOでcsvファイルをデータベースとして接続して >>828 に挙げられた
SELECT文を実行する、という手段がとれる

2018/01/04(木) 10:31:44.20

sqlならselectとorder byだけですむ案件だな
愚直にプログラム組むと厄介だけどね

2018/01/04(木) 10:38:07.48

ピボットでいいんじゃね