Excel VBA 質問スレ Part61

2019/04/25(木) 23:41:03.61

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512

ExcelのVBAに関する質問スレです
コード書き込みや作成依頼もOK
※前スレ
Excel VBA 質問スレ Part60
https://mevius.5ch.net/test/read.cgi/tech/1552736349/

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

2019/05/16(木) 12:51:40.47

>>257
基本それだが、ずっと掴んでるやつがいるとバグ修正や機能追加のときに困る

2019/05/16(木) 14:16:39.46

>>258
読み取り専用にしてもダメか？

2019/05/16(木) 14:16:55.41

もちろん自分が編集する時は読み取り専用解除ね

2019/05/16(木) 14:43:29.72

コード書く画面を
背景は黒に、文字は白に、する方法を教えれ

2019/05/16(木) 15:42:20.38

ツール -> オプション -> エディターの設定

2019/05/16(木) 16:12:18.52

>>256
OS起動したら、サーバからコピーしてくるバッチ等を走らせる
小さいならいちいちバージョン確認しなくてもよい

**デフォルトの名無しさん** (ｱﾒ MM35-C3gu) · 2019/05/16(木) 18:04:23.53

みんな一々シャットダウンするのか？
スケジュールでコピーだろ

2019/05/16(木) 18:58:39.63

>>>261
これ
https://tonari-it.com/excel-vba-vbe-syntax-highlight/

ちなみに書くとこはVBEって言うから覚えておくと検索できて便利

2019/05/16(木) 19:18:23.52

マクロ終了時にマクロ開始から終了までにかかった時間が秒までメッセージボックスに表示するってできる？

2019/05/16(木) 19:18:36.52

>>264
個々人のローカルフォルダにxlam があってそれが定期的に書き換えられるって事？
他のブックがxlamを掴んでる最中でもできる？

2019/05/16(木) 19:23:31.84

>>266
できる

2019/05/16(木) 19:26:21.47

>>266
楽勝

Sub foo()
開始 = Now
'処理
終了 = Now
MsgBox Format(終了 - 開始, "hh:mm:ss")
End Sub

2019/05/16(木) 19:26:44.10

>>268
URL春だけでもいいから教えてあげたらｗ

2019/05/16(木) 19:31:25.53

>>268はなんとかookスレで最近流行りの流れだよw
>>269ありがとう！

**デフォルトの名無しさん** (ｱﾒ MM35-C3gu) · 2019/05/16(木) 20:10:17.30

>>267
掴んでたらスルー

2019/05/16(木) 22:17:19.76

>>256
起動時に何らかのファイルを参照するようにしておいて、
参照出来たらそのままOK。
出来なかったら、新たにファイルをDLする仕組みにする。

で、更新させたい時は、わざと参照用ファイルを消しておく。

2019/05/16(木) 22:24:00.58

>>273
xlamを共有するならそこのWorkbook_Open にそういう処理を入れておけばいいでしょうね

2019/05/16(木) 23:04:51.54

>>256
最新バージョン書いたテキストかなんかを共有に置いといて、起動時に見に行ってそれより古かったら更新促す警告出すとか

**デフォルトの名無しさん** (ｱﾒ MM35-C3gu) · 2019/05/17(金) 02:52:09.97

Excelの起動時、マクロを実行させる：Excel（エクセル）マクロ
https://www.excel.usefulhp.com/excel-macro/excel-macro50.html
で良いだろ

2019/05/17(金) 09:22:35.60

共有パス見て実行とか大変そう面倒そうて主観があったけども
Application.Run "共有のパス!マクロ名 "
の1行でいけちゃうもんなのか…？
とりあえず試してみる
すでに誰かが開いてる？とか参照できないときの対処も色々試してみるありがとう

2019/05/17(金) 09:26:03.52

>>277
そっちは使わないかな
俺はボタンにマクロの場所を登録する

2019/05/17(金) 09:26:30.66

なんか日本語変だ
まぁいいか

2019/05/17(金) 12:25:15.46

ちょっと筋外れるかもしれないけどvbeに行間なく詰めて書いたコードの選択範囲に1行ずつ改行入れるいい方法ない？

2019/05/17(金) 12:28:45.29

そんなのできない。サクラエディタで置換して貼り付けろ

2019/05/17(金) 22:00:27.88

>>280
VBEを操作すればできると思う。面倒だけど
まあ>>281の方法が合理的だろうな

2019/05/18(土) 00:21:37.50

>>280
改行コードを改行コード+改行コードで置換

2019/05/18(土) 00:55:20.52

コードウィンドウ内の改行はCRLFじゃなくてLFだった筈。
そんなに難しくない。
でもこれが動く設定はマクロウィルスも動くから危険だよ。

Sub test()

Dim strCode As String
Dim stRW As Long
Dim stCL As Long
Dim edRW As Long
Dim edCL As Long

With Application.VBE.ActiveCodePane
.GetSelection stRW, stCL, edRW, edCL
strCode = .CodeModule.Lines(stRW, edRW - stRW + 1)
.CodeModule.DeleteLines stRW, edRW - stRW + 1
.CodeModule.InsertLines stRW, Replace(strCode, vbLf, vbLf & vbLf)
End With

End Sub

2019/05/18(土) 19:27:39.15

専用のメソッドがあるのが凄いわ。
何を想定して用意してあるんだろう?
プログラムがプログラムを書き直すAIにでも使うのか?

2019/05/18(土) 20:16:07.59

Application.VBE.ActiveCodePane
こんなの合ったのか
PaneはPanelかと思ったら、ペインの事なのね

2019/05/18(土) 20:29:02.38

VBEを拡張するためのオブジェクト群が用意されてるんだよね

2019/05/19(日) 09:22:38.02

Excel4マクロは自己書き換え自由だったから
機能的に負けるわけにはいかなかったんじゃね？

2019/05/19(日) 11:43:42.11

配布済マクロの修正パッチとかで大量のファイルや行を一括修正したいときは便利

2019/05/19(日) 12:03:05.34

単価の低そうな涙ぐましい仕事だな

2019/05/19(日) 12:39:30.70

マクロ4はできる人がいない＋やりたくないから単価超高いぞ
で、単価高くしても足が出る・・・

2019/05/21(火) 19:13:05.44

実行から終了まで数分かかるマクロを途中で止める方法ないですか？f4押したら「処理を止めました」のメッセージボックスとともに停止するとか。

2019/05/21(火) 19:31:11.94

Break
ESC

2019/05/21(火) 19:37:07.71

ループが長いならDoEvent入れておいて
msgbox "終了します"とendだけ書いたのを起動できるボタンどっかに置いとけばいい

**284** (ﾜｯﾁｮｲ 3591-H0mX) · 2019/05/21(火) 20:04:23.19

ESCやBreakで止まるなら良いけど、それで止まらない処理ならウィンドウESC押しながらタスクバー上でマウスでウィンドウの切り替えをすると止まる。

どっかのブログで見て知ったけど役に立ってる。

2019/05/21(火) 21:15:28.57

こうする
https://youtu.be/h-xo-5krq2Y

2019/05/21(火) 21:30:20.69

ほー
明日試してみます

2019/05/21(火) 22:08:13.75

2009/12/16に公開って何だよ。
20年くらい前に見たぞそれ。

**デフォルトの名無しさん** (ｱﾒ MM35-C3gu) · 2019/05/22(水) 06:58:44.20

>>298
映像はどうみても２０年以上前だな
的確な対応で現在も使える

2019/05/22(水) 09:27:28.15

セルの値を数式に代入って無理でしょうか？
たとえばA1セルに乱数が入ってた場合に
どっかのセルに =A2-乱数という数式を代入したい感じです

2019/05/22(水) 09:41:17.90

=A2-A1

2019/05/22(水) 09:43:43.65

演算あり値コピーの話？

2019/05/22(水) 09:52:11.18

いやセル番地じゃなくて値を数式に代入したい感じです
たとえばA1セルに2,134という数字が入ってた場合に
=A2-2,134　という数式をどっかのセルに代入

A1セルが387だとしたら
=A2-387

説明下手糞で申し訳ないです

2019/05/22(水) 09:54:20.78

>>300
書いている意味がよくわからんど
セルに式でも値でも代入できる
ただ、乱数は曲者で常に変わるから
もう少しやりたいことを具体的に書き込もう

2019/05/22(水) 10:00:56.85

>>303
乱数は曲者で
A1セルが387だとしたら
=A2-387
ほかのセルに「=A2-387」を入れた時点でA1セルは変わるから
もう少し何をしたいのか書くように

2019/05/22(水) 11:59:27.46

そもそも数式はスレ違いだろ
VBA関係ないならどっかエクセルの使い方の板かスレかさがせ

2019/05/22(水) 13:17:58.06

>>305
A1セルの乱数結果が現時点で387だとしたら
変数(仮にrとする)に、387を格納して
ほかのセル(仮にB1とする)に"=A2-r(中身387)"を入力したいって事？

それなら確かに関数じゃできないよね

**284** (ﾜｯﾁｮｲ 3591-H0mX) · 2019/05/22(水) 13:58:22.19

>>303
Range("C3").Value="=A2-" & Range("A1").Value
じゃ駄目?

2019/05/22(水) 16:05:46.41

>>307
それに意味あると思えない、乱数なんてふつうは仕事に使わない
例えば、学校の先生あたりが、引き算足し算などの問題を自動的に作りたいとか
であれば、A列に乱数１の結果をB列に乱数２の結果を
C列に計算式を、D列に回答をで別シートなどで問題文を作る
このぐらいだな

2019/05/22(水) 18:12:45.70

説明下手糞ってレベルじゃねぇぞ…

2019/05/22(水) 18:48:51.39

>>309
ちょっとしたシミュレーションで使うかもしれない。（Excelでやるとは言ってない）

>>302じゃダメなの？

**デフォルトの名無しさん** (ｱﾒ MM35-C3gu) · 2019/05/22(水) 19:16:53.47

>>311
ちょっとしたシミュレーション・・無いだろ
この文書力だとセルに入れたい式って
計算式の文字列かもしれん

2019/05/22(水) 22:59:24.50

もうあれだ。
ここは、何の用途に使うのか当たられたやつが優勝スレにしよう。

2019/05/22(水) 23:13:17.16

完全乱数を使って円周率の計算をする

2019/05/23(木) 07:43:57.81

別に用途は何でもいいさ
興味ゼロってわけじゃないけど

2019/05/23(木) 10:22:05.81

こっちで作って欲しいと言われたものをつくってると
そんなもの作られたら困る
楽になって人が減らされると困る、と古参がちくちく
この古参、自分の作業を楽にしたい時には
これがもう偉そうな態度で、「作らせてやる」とでも言わんばかりに頼んでくる
どうしたいんよもう

っていうことないっすか

もう面倒なんで職場で作るのやめようかな
すんません落書きしちゃって

2019/05/23(木) 10:56:07.55

厚労省で配布されている
https://www.mhlw.go.jp/stf/seisakunitsuite/bunya/000116890_00001.html
にある
https://www.mhlw.go.jp/content/10900000/000494621.xlsm
なんだけど、内容は単純で

1件いくらの検査があって、件数を入力すれば請求書が作成されるというもの
1シート＝1自治体なので、複数の自治体へ請求する場合は、ユーザーがシートコピーして作る
VBAで各シートを集計して総括表に転写する

なんだけど、VBAみて
変数が多すぎてコーディング中に間違えても気づかんだろってのと
引っ張ってくるセルをConstで決め打ちしてるけど
これ後のメンテナンスでセル一個挿入することになったら修正大変やん　と思いました。

というか、エラーメッセージの所で間違えて
行を表示すべきところを列表示しているけど
これこそ変数多すぎて分かりにくくした事が原因だと思います。

じゃあどうする、と言われたらみなさんならどうしますか？

同じ形の複数の2次元の表を合計するだけなんだから
スタート地点とゴール地点決めてやって
後はずらしていけばいいやんと思うのですが

後、小数とか負の値ぶっこんでも動作するのはモヤモヤするんだけど
まぁこの辺やりだしたらキリがないので使わせる相手によってどうするかは変えますかね

2019/05/23(木) 11:43:15.47

>>316
義務教育でプログラミングを扱うようになって、自分の仕事くらい自分でプログラミングできないようでは大変な事になるよ、
と教えてやればいい。
もしかしたら「手下にプログラミングさせればいい」と勘違いするかも知れないが、
それでは通用しないよと言ってやろう。

こういう話もあるし
https://lite.blogos.com/article/376203/

2019/05/23(木) 16:11:36.77

>>316
ただ単純に業務全体を見ずに作るから役に立たないんだよ
業務に対して目的と効果を文書ではっきり示せば古参の意見を聞かなくていい

2019/05/23(木) 16:13:59.49

>>316
その古参の上司に報告しろ

2019/05/23(木) 16:14:38.98

>>317
すまん、リンク先を読む気もしない

2019/05/23(木) 17:56:30.55

>>317
べき論の先には「VBAを使うのをやめるべき」という結論しかないから不毛よ
俺ならどうするか？下請けに丸投げして終わりだよ

◆QZaw55cn4c (ﾜｯﾁｮｲ df47-SwLn) · 2019/05/23(木) 19:35:05.76

>>318
＞義務教育でプログラミングを扱うようになって、自分の仕事くらい自分でプログラミングできないようでは大変な事になるよ
脅かすことでプログラミングができるようになるとはおもいませんが…
プログラミングって極めて創造的な作業ではないでしょうか、似たようなものは数学の証明問題だと思いますが証明問題を脅かして解けるようになるとは思えません

2019/05/23(木) 20:39:50.63

機械学習やweb系ならともかく、
VBAレベルなら極めて事務的な仕事だろう。どこにも創造性なんて無いよ
ルーチンワークを自動化させてるだけ

◆QZaw55cn4c (ﾜｯﾁｮｲ df47-SwLn) · 2019/05/23(木) 20:58:31.34

>>324
でもそのルーチンワークを実際に記述するとなると一本道というわけにはいかないでしょう？
論理的な一貫性を維持しながらしたいことを記述するのは、いわれているほどやさしい仕事ではない

2019/05/23(木) 21:06:10.14

>>325
一本道だよ、全然難しくない
強いて言えば例外を先に全部列挙した上でシステムに放り込むという順序の変更がある程度

面倒なのは仕様変更と、使いにくいというオバハンの嬌声。オバハンにalt+F8は覚えさせたけど、まぁそれぐらい

**デフォルトの名無しさん** (ｱﾒ MMcb-bNEy) · 2019/05/23(木) 22:12:48.96

まあ、自己満足で終わるなら簡単だな
おばはんにも満足いくように作るんだよ

2019/05/23(木) 22:27:50.56

>>317
コンスト過ぎてﾜﾛﾀ。

2019/05/23(木) 22:55:08.55

>>323
それもそうだが、その老害を大人しく
させることができれば上出来と考えた

2019/05/24(金) 01:42:55.95

Excel2016にてpersonal.xlsbに「保存せず閉じて開き直すマクロ」を作ったところ挙動が変です

Sub ReOpen()
　　Dim wb As Workbook
　　Dim path As String
　　Set wb = ActiveWorkbook
　　path = wb.fullName

　　'※1
　　If MsgBox("本当に変更を取り消しますか？", vbYesNo) <> vbYes
　　　　Exit Sub
　　End If

　　wb.Close(False)
　　Application.Wait([Now() + "00:00:01"])

　　'※2
　　Workbooks.Open(path)
End Sub

このマクロ自体は動くのですが、※1のIf～EndIfを消すと※2のOpen実行直後にExcelが通知無く落ちます
また、※1※2を両方消してマクロ実行した後、手動でブックを開き直すとやっぱりExcelが落ちます

対象のブックはフォームやマクロを含んだ3MB程度のxlsmファイルです
MsgBox出さないだけでなぜ不安定になるのか、思いつく原因を教えて頂けると幸いです

2019/05/24(金) 01:44:27.87

あ、Ifの末尾のThen忘れたので脳内で補完してください

**デフォルトの名無しさん** (ｱﾒ MMcb-bNEy) · 2019/05/24(金) 06:40:29.33

>>331
ハイ　イイエ　が逆なのも利用者が自動変換するんですね

**デフォルトの名無しさん** (ｱﾒ MMcb-bNEy) · 2019/05/24(金) 06:54:20.73

>>332
すまん、継続がキャンセルか

2019/05/24(金) 09:59:52.88

>>330
2019で試したけど落ちなかった
＞　Application.Wait([Now() + "00:00:01"])
これを抜いても同じ

カンで答えると、再計算が邪魔してるような気がする

・一度再計算や描画、イベントを切ってみてはどうかな
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

・あとはこの部分のウェイトをもう少し長くする
＞Application.Wait([Now() + "00:00:01"])

・まっさらなブックで試してみる

2019/05/25(土) 07:54:51.56

アドインって、ファイルを閉じたら、
一緒に閉じられるものだと思ってたけど、
VBA実行中は残るということか。
一緒に閉じられたら、途中で止まっちゃうからな。

2019/05/25(土) 08:55:38.15

vba実行中というかずっと残るっぽい

2019/05/25(土) 09:55:42.63

エクセルの機能に追加してるからな
アドイン使ったファイルをアドインがないエクセルで編集すると壊れるよね

2019/05/25(土) 10:07:50.37

>>330
経験上納得できない動作をするときは何か見落としているところがある
step実行で場所特定して、各変数の値が壊れてないか確認
イミディエイト画面で入力してみるなど

2019/05/25(土) 10:14:01.11

Excelに限って言えば、Excel自体に変な仕様が多い
これが男を惑わせる

**デフォルトの名無しさん** (ｱﾒ MMcb-bNEy) · 2019/05/26(日) 04:10:46.96

>>334
元に戻さないアホがいると大迷惑になるな
>>335
アドインはExcelに組み込むよね
>>339
配列数式なんか使うとブーイングになるよな

2019/05/26(日) 15:23:19.13

以下の状態だとB1～B3は全て1:1048576と表示されてしまいます
Cells.SpecialCellsをRange("A1").SpecialCellsに変更した場合はB1～B3は全てA1と表示されます
Ctrl + Endで選択されるセルの番地を文字列として取得するにはどうすればいいですか？

　　A　　　　B
1　Sheet1　=fn(A1)
2　Sheet2　=fn(A2)
3　Sheet3　=fn(A3)

Public Function fn(sSheetName As String) As String

fn = ActiveWorkbook.Worksheets(sSheetName).Cells.SpecialCells(xlCellTypeLastCell).Address(False, False)

End Function

2019/05/26(日) 15:41:58.63

関数呼び出しではなくマクロやボタンクリックでなら問題なかったので
そっちの方法に切り替えますすみません

2019/05/27(月) 06:52:50.90

入力項目を複数用意したinputboxって作れますか？

2019/05/27(月) 07:55:59.33

>>343
どんなものを考えているんだ

2019/05/27(月) 08:12:50.88

>>343
ごめん入力フォームじゃなくていいかも
最終的にやりたいことは、以下のボタン等が設置されたユーザーフォームを作ることです（他の要素も考えたら普通にinput boxでは無理と気づきました）
・AUTO openで開く
・押すと特定セルに文字が入力されるボタンを複数設置する
・カレンダーを設置する
・押すと印刷処理を行いつつファイルを上書き保存し閉じるボタンを設置する

**デフォルトの名無しさん** (ｱﾒ MMcb-bNEy) · 2019/05/27(月) 08:30:52.72

>>345
こんな特殊なものAUTO openでやるって非常識だな
シートのマクロで十分だろてかマクロは印刷して保存だけでいいだろ
それも自動記録でも十分

**デフォルトの名無しさん** (ｱﾒ MMcb-bNEy) · 2019/05/27(月) 08:37:02.21

Excelの勉強不足だろ

2019/05/27(月) 08:47:00.50

>>345
それユーザーフォームにする必要がないような
ユーザーフォーム超めんどいから止めたほうがいいよ
あと、カレンダーはデイトピッカーと言う

2019/05/27(月) 09:45:56.73

この程度の理解力だとカレンダーが必要かもわからない
カレンダーなんか別にシートでもできるけど、どこまで使うかがわからないから
最適な解もわからない
入力をシートに反映するなんて普通にセルを参照すればいいだけ
ExcelもVBAも１％以下の理解で動作仕様を描くのではなく結果で何をしたいかを描くべき
これでも少しおだててるが

2019/05/27(月) 10:37:56.08

おだてるの意味間違えてるよ。

2019/05/27(月) 10:41:41.92

>>350
いや～そうでもないよ
理解力は両方とも0.1％～0.01％程度と思ってたから

2019/05/27(月) 11:25:38.71

てか、みんなが
>>345←この説明で全容を理解できてるのがすごいとおもったけど
いろんな意味に解釈できそうで、おれにはそのうちの一つには絞りきれないんだけど

2019/05/27(月) 11:44:36.09

>>352
教えたい人種の大半は問題の表層を自分の得意分野に転換して持論を得意気に展開してるだけ。
質問者の問題の背景に寄り添って解決を計ろうとする者は僅か。

2019/05/27(月) 13:50:33.03

>>352
どういうパターンであれ、大体ロクでも無い物だという判定ぐらいなら可能

2019/05/27(月) 14:00:43.29

>>345
ここの人達はマウント取りたいだけで実際はあまりスキルが無い人が大半なので程々に聞いていた方が良い。

あなたの要望については疑問が1つ、可能だけどあなたのスキルから考えて技術的に難しい点が1つある。

疑問は、AutoOpenで開いて、閉じるボタンで保存終了するのならExcelで作る必要は無いのでは?という疑問。
普通にC#辺りで作った方が適してると思う。この後に述べるけど、カレンダーの件もC#等の方が簡単。

技術的に難しいのはカレンダーの実現。
サブクラスとWin32の知識が無いと難しい。
後々を考えると汎用的にクラス作った方が良いからそういう技術も必要。
もっとも、ファイル追加とか自作するとかで良ければスキルが無くても出来るともいえる。

2019/05/27(月) 14:36:42.16

>>355
この人はマウント取りたいだけで実際はあまりスキルが無い人なので程々に聞いていた方が良い。

2019/05/27(月) 14:38:46.48

>>356
そうくると思ったw