Excel VBA 質問スレ Part59

2019/02/09(土) 15:12:30.70

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

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

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

2019/03/11(月) 20:46:16.50

>>849
自分はそれじゃなくてアレがイラッとする
だからよく ActiveSheet.DisplayPageBreaks = False
をやる

2019/03/11(月) 21:21:38.01

>>845
最初に「今Accessを使う意味」とか、言い出した奴が都合が悪くなると人のことを上から目線だとか言い出すからなあ。

そもそもの話はAccessにしちまうからExcelでDB処理することは多く無いって話から始まっている。
(実際は結構あるけどAccessと食い合うって話)

最初から、バックエンドSQLサーバーでフロントがExcelって話なら、SQLサーバーじゃなくて正にExcelでDB処理の話になって、俺の主張に一致するから反論にならん。それとも絶対にExcelフロントでAccessフロントはあり得ないって話ならもう一度センスね―と言ってやる。
Excelフロントはあり得るけどAccessフロントの方がよりあり得るわ。

いやいやそういう構成ではExcelでDB処理には該当しないというなら、ExcelでDB処理とはExcel単体でのDB処理ということになるから、それに対しての比較対象にSQLサーバー持って来たの?という話になる。
この場合もやっぱりセンスね―と言ってやる。

だからそもそもの話に反論としてSQLサーバーを挙げるってことはExcelフロントではないということを意味するんだろう。
つまりOffice系では無く、開発系で組むという話だろう。
Accessで済むようもん、場合によってはExcelで済むようもんをC#とかを使って組むの?

部署内での打ち合わせ資料をWordで作ると言ったらPagemakerの方が良いと言われた気分だわ。

2019/03/11(月) 21:56:59.54

accessが好きとか嫌いとか最初に言い出したのは誰なのかしら

2019/03/11(月) 22:03:37.96

>>852
3回読んだが意味がよく分からん
俺の最初の主張は>>729から始まってるんだが、「データはExcelじゃなくてAccessに置け」という他の意見に対して
「いや今時AccessなんかじゃなくてSQLServerでしょ」というのが意図だ。
そっからなんかこじれてるんだが、ここでの俺の前提はフロントUIはエクセルというものなんだよね。
ExcelからのSQL発行という点でUI表面上の差が大してないと考えて比較している。

2019/03/11(月) 22:45:47.44

>>854
馬鹿は死ねよ

2019/03/11(月) 23:03:46.33

>>854
フロントがExcel程度でなんとかなるデータならAccessで十分だろ
すでに大量のデータがSQLServerに溜まっているならそっち使えばいいけど
Excelでデータ入力とか考えただけでも吐きそう

2019/03/11(月) 23:17:24.96

まだやってんのかよ

2019/03/11(月) 23:24:53.12

>>856
フロントでやるのはデータ分析だろう。
データ入力は現場の仕事。
フロントと現場を混同してるだろ？

2019/03/11(月) 23:31:06.64

二人以上でAccess使うなんて想像してなかったわ。すまん

2019/03/12(火) 01:08:58.03

>>858
データ入力画面もフロントだよ。
現場でどうデータ入力させようとしてるのか書いてみろよ。

2019/03/12(火) 01:16:33.47

あの～そろそろ～

2019/03/12(火) 01:28:26.03

ID:tsrenNBW0

馬鹿は早く死ねよ

2019/03/12(火) 01:58:18.19

端から見てると何でこの人たちがVBAやらされる羽目になってるのか分かるよね

2019/03/12(火) 06:19:34.26

むしろこんな奴等に何かやらせる羽目になってる上司に同情するわ

2019/03/12(火) 07:21:53.89

弊社では無理です

**デフォルトの名無しさん** (JP 0H6e-q+B7) · 2019/03/12(火) 07:54:30.64

2日ぶりに覗いたらまだ同じ話してて草

2019/03/12(火) 08:15:48.17

うちなんかSQLServer導入して部署内誰でも使えるようにセッティングしてるのにずっとAccess使い続けてる。
Excel VBAからADOで繋げてるパターンなんで移行は手間じゃないと思うんだけど2～3人が試しに使い始めただけ。
思い通りになんていかないよ。
社内稟議通した人がお気の毒w

2019/03/12(火) 08:18:57.13

>>854
八ァ?
>>729から始まってるんだよな。
その前に「デ―タはExcelじゃなくてAccessに置け」なんて意見が何処にある?
>>729の前にAccessのことを言ってるのは>>728しか見当たらないんだが。

で、俺が言った>>728は別に「デ－タはExcelじゃ無くてAccessに置け」なんて言って無いんだが。
誰かにこうしろ等と言って無いし、データをAccessに置く等と限定してないぞ。

その上、「今時Access」?
お前は一体何様だよ。
どっちが上から目線だ?
上から目線でも回答が頓珍漢じゃなければセンスね―なんて書かね－よw

2019/03/12(火) 08:24:06.51

>>867
それはちょっと悲しいね。
まあ、ファイル単位で移動出来るメリットもあるからDBサーバーあってもAccess単体ってのもあり得るけどな。

2019/03/12(火) 10:47:16.64

>>867
それはね、ローカルに置いてる（としとこう）Accessの中に人に見せられないデータがあるからだよw

2019/03/12(火) 12:45:42.87

>>867
そもそもそれエンジンの話なのか帳票とかのデザインの話なのかはっきりしてくれ
あと人は今困ってないなら移行なんてしないよ、トラブル招くかもしれないしな
移行させたいならAccess使用禁止ぐらいしないとダメ
どうせ導入した奴もそこまで腹くくってないだろ

2019/03/12(火) 13:22:50.41

>>870
本当にあったりしてなw

2019/03/12(火) 15:31:02.26

>>867
SQLサバだとテーブル設計とか後からいじりにくいんだよな
連結フォームに全データ表示！とかも気楽に出来なくなるし

2019/03/12(火) 18:07:30.07

>>867
もしかしてクライアントサイドの管理ツールとか知らないのでは？

2019/03/12(火) 21:59:22.23

>>871
テーブルを移行して欲しいんだけどやってくれないって状況
導入した人は権力があるのでそのうち強制するかも

2019/03/12(火) 22:19:10.50

>>875
移行するメリットがないとねぇ
レコード数が少ないと速度なんてほとんど変わらないし…
100万レコードでも、きちんと作られてればAccessでも十分実用になるし…

まずは、めちゃ処理時間がかかるやつ一つを移行。ストアド作り込んで速くなるのを見せるとか。
せっかくSQLServer使うなら、ストアド使わないと勿体ない。ただレコード持ってるだけの箱になってしまう。
ストアドはとっつきにくいと思うが、レコードをこねくり回すような時間かかる処理には効果絶大。

逆に言うと、軽いデータや処理のmdbは移行後回し。（というより、そんなの移行しなくてよろしい。労力に見合わない）

2019/03/12(火) 22:41:48.72

>>876
いきなりストアドで大規模ロジックを書こうとしても、どう書くべきか（どう書くのがふさわしいか）わからないと思う。
小ロジック大量レコード処理辺りから書いていきましょう

2019/03/12(火) 22:50:26.01

>>876
自分含め誰もspとか知らない
一般的なプログラミングは割とできて、みんなVB/VBA、C#、C++、rubyとかでなんとかしようとしてる
まさにDBを箱としか見てない

2019/03/12(火) 23:05:56.66

ストアドはRDBの設計思想に反する邪悪な存在なので可能な限り避けるべきです
最適化をかけにくいのでインデックスの効いたクエリに比べて速度も遅くなります

2019/03/12(火) 23:30:38.78

>>879
マジで！？

2019/03/12(火) 23:43:55.90

Webシステムで全て開発側の手元にあるんならストアドなんか害悪しかないけど、
VBAだったらストアドやビューを最大限に活用して可能な限りDBMS側に寄せるべき
神VBAは作りっぱなしで逃げられるならいいけど、後々Excel側の変更やDB側のスキーマ変更で地獄を見ることになる

2019/03/12(火) 23:46:34.37

ExcelからAccessのデータを取得するきはこういうコードをクラス化してAccess立ち上げる必要ないようにした。
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_130_030.html
他言語使ってる人の事情は知らんけど似たような感じだと思う。
SQLサーバ向けのもADOだからインターフェース変えずにできた。
せっかくツールったんだから移行しろよ！
と思ってたけどもう何がいいのかわからんくなった。

2019/03/13(水) 00:27:28.10

>>875
それエンジンだけをすげ替えろって言ってるってことだよね？
そりゃわざわざ移行しないよ
メリットないから強権発動しかないと思うよ

2019/03/13(水) 00:29:37.94

>>880
>>879はなんか勘違いしてるだけ
ストアドは簡単に言うとサーバー側でロジックが動くだけの話だからインデックスが効くかどうかはストアドが発行するSQLに依存する

2019/03/13(水) 00:41:34.27

>>884
879が批判してるのは、ストアドの中でループしたり一時テーブルを安易に多用したりするような使い方じゃないのかな

2019/03/13(水) 00:41:51.32

>>882
そのめんどくさいselect文を作るのがaccessやssmsじゃね？

2019/03/13(水) 00:46:32.81

>>884
インデックスが効かないとは言ってませんが？

SQLなら期待される結果を返す範囲で最適化して高速化する余地があります
これはSQLが宣言的な言語だからできることです
最終的な答えが同じなら途中の結合や絞り込みを入れ替えたり省略することが可能です

逆にストアドは手続き型なので基本的に定義した順に処理が逐次実行されます
全く最適化できないわけではありませんが宣言的な言語と比べるとこの順序があるという性質のせいで制約が強く最適化のかかりが弱くなります

2019/03/13(水) 00:50:37.91

>>887
理念には同意するけど、VBAスレでそれ言う？
Excel VBAこそ、本来は宣言型であるものを手続き型に落としてしまいパフォーマンスを低下させている例の最たるものだよ

2019/03/13(水) 00:52:52.41

>>885
変な使い方を前提にストアドが遅いとか言われても困る

2019/03/13(水) 00:55:59.30

>>889
実際、ぐるぐるSQLを書いちゃうような人はDBに寄せた設計になるとストアド使いたがるからね

2019/03/13(水) 00:57:03.71

>>887
だからその逐次実行を行うのがクライアントなのかサーバーなのかが違うだけでしょ
そりゃストアドと単一のSQL比べたらストアドの方が遅いだろうけどクライアントが同様の処理すること思えば全体としては（ちゃんと設計されてりゃ）速くなるでしょ

2019/03/13(水) 00:59:49.50

>>891
SQLをコンパイルする必要がないからストアードの方が速くね？

2019/03/13(水) 01:13:08.97

>>892
理論的にはそうだけど、現実にはそこは全く関係ない
SQLのコンパイル時間はサーバーとの通信のコストと比較すれば無視してよい

2019/03/13(水) 01:22:09.36

そもそもSQLの発行がパフォーマンスに響くような状況はSQLの粒度が小さすぎる
大抵そういう時はうまく纏めれば一桁速くなる

2019/03/13(水) 01:32:35.47

>>891
アプリケーションとストアドの比較をしているのではなく同じ結果をもたらすSQLとストアドの比較をして言った
そもそもアプリケーションに書かれたビジネスロジックをそのままストアドに移植する時点で論外なので比較する発想がなかった

2019/03/13(水) 04:57:47.34

>>895
> 同じ結果をもたらすSQLとストアドの比較をして言った
そりゃSQL単体で書けるならその方が速いわなw
SQL単体で書けるのにストアド使うとか普通あり得ないでしょ？

2019/03/13(水) 06:19:07.90

>>896
残念ながらありえます
しかも少なくありません
単にSQLが苦手な人
ストアドの方が速いと思ってる人
深く考えずアプリケーションコードストアドに移植してしまう人
SQLでいいのにストアドを書いてしまう人というのは何処にでも居ます

2019/03/13(水) 06:45:04.68

>>897
>>889
お前の周りのことなんか知らんよw

2019/03/13(水) 07:00:38.50

>>898
このスレッドにもいましたよね

2019/03/13(水) 07:12:08.08

何を言いたいのかさっぱりわからんw

2019/03/13(水) 08:04:29.56

煽りか

2019/03/13(水) 10:36:45.92

一体いつまで同じ話をぐだぐだ続けるつもり？
プログラマ板のどっか雑談スレでやれよ低能プログラマどもは

2019/03/13(水) 11:12:26.48

だってここ以外ではマウントとれないんだもの

2019/03/13(水) 15:18:57.45

セルA1に =aaa と書き込んで数式エラーを作っておきます

その上で
Dim ary() As Variant
ary = Activesheet.Range("A1:A10")

MsgBox ary(1, 1)

結果：
型が一致しません

ary(1, 1)をVariant型の変数に代入する場合は何も起きないので
MsgBoxに表示するときに内部的にString型に変換するため
エラーがおきるのだろうと思っています

それはともかく、ary(1, 1) からエラー値を削除したいのですが

ary(1, 1) = ""
MsgBox ary(1, 1)

とやってもダメでエラーが出ます
ary(1, 1) を何もない状態にするにはどうしたらいいですか？

ary(1, 1)はこの先いろいろな操作をすることになるため、そのたびに
どこに隠れているか分からない数式エラーをチェックするのは大変なのです

2019/03/13(水) 15:24:34.25

ごめんなさい
>>904は取り消します

何か他の要因がからんでエラーが起きていたようです
上の例だとエラーはおきませんでした

ごめんなさい

2019/03/13(水) 15:25:55.52

とりあえず
Dim ary() As Variant
はエラーが出るので
Dim ary As Variant
に変更している

2019/03/13(水) 15:26:27.13

あー、荒れてるところ申し訳ないのですが、
ちょっと、質問…いいですかね…
初心者なんでさっぱり文が思いつかないから
例文みたいなのを作ってほしいんですけど…

B3:F○○の表があります。

・B3:F10にそれぞれセルに名前を付けていきたい。
・B3を「_0」としてC3「_2」というように、+2ずつ、
F3「_8」までいったらB4「_10」と同じように+2して、
最後のF10までやりたい。

こんな感じのってできますか？？

他のシートでも同様の作業を行ないたくて、
B3から、F列の表で、数字が入力されている部分だけ名前をつけたいから、
if文とかで、数字が入力されていなかったら名前をつけるのを終わるみたいなできたら嬉しいです。

2019/03/13(水) 15:27:20.71

>>905
あ、はい。

2019/03/13(水) 15:38:57.02

>>907
先に範囲選択してからマクロ実行
セルに何も入ってない場合に終了（数字かどうかは判定していない）

Sub Macro1()

番号 = 0
For Each セル In Selection
If セル.Value = "" Then
Exit Sub
Else
ActiveWorkbook.Names.Add Name:="_" & 番号, RefersTo:="=" & セル.Address
番号 = 番号 + 2
End If
Next

End Sub

2019/03/13(水) 16:22:51.30

以前、ifの条件に合わない時に実行したい時はthenに続けてelseと書いて実行したい内容を書くよりも、条件をnotで括った方が処理が速いと言われました
実際に計測したら確かに僅かな差が有ったのですが何故なのでしょうか

2019/03/13(水) 17:11:51.63

>>909
ありがとうございます！
やってみます！

2019/03/13(水) 20:59:56.96

>>910
Ifブロックは先頭の条件から逐次Boolean評価していって、一度Trueと判定されるとそこで条件を満たす場合の処理を実行し、残りの条件は評価しない仕組み
Elseのケースでの処理を実行するとなると、If条件とそれ以降のElseIf条件全てにおいてTrueが成立しないという条件が必要になる
なので、同じ条件数のIfブロックで比較すると、Elseより前に置かれた条件で処理を行わせる方がElse以降で処理させる場合よりも条件評価を行う回数が少なくなる

2019/03/13(水) 21:38:41.07

>>912
if not true then temp = 0
if true then else temp = 0
の二つのif文があった時、後者はtrueの評価の後にelseという評価をしてるという事でしょうか

2019/03/13(水) 21:39:18.39

>>912
VBAはショートサーキットしないけどな

2019/03/13(水) 21:40:15.61

>>912
何か微妙に勘違いしてる？
Elseifは関係ないぞ

2019/03/13(水) 21:43:27.67

>>913
elseという評価はしていない
評価というのは、判定してると言っていると思うが、判定はifのとこで終わってる

2019/03/13(水) 21:53:50.43

はっきりさせておきたい
VBAのとき、
if A and B and C then

ってなってるとき、A、B、C全部必ずチェックするのか、AがFALSEだったら、もうB、Cは無視するのか
どっち？
C言語みたいに、& と　＆＆　みたいなandに２種類ないよね

2019/03/13(水) 21:54:09.23

ショートサーキットないおかげでどんどんネストしてゆく…

2019/03/13(水) 22:06:55.44

ショートサーキットなんてねえよw

2019/03/13(水) 22:10:56.03

>>919
A、B、C全部必ずチェックすんだね

2019/03/13(水) 22:39:10.74

>>917
そういう場合は
Select Case False
Case A, B, C
' !( A && B && C)の場合の処理
Case Else
' A && B && C の場合の処理
End Select

と書けばショートサーキットになるよ。
直感的じゃないけどね。

2019/03/13(水) 22:41:50.24

不慣れな初心者ですみません

リストシートのボタンを押すと
リストに入力していたデータ行数分書類型シートが作られるのですが
その作られた書類型シートにもそれぞれボタンがあり
各ボタンを押すとデータ送信用シートが作られる二段構造？になっています

書類シートからデータ送信用シートを作るには
いちいち各シートのボタンを1つずつ押さねばならず大変です
選択したシートのボタンを一括で押すことのできるマクロはありませんでしょうか…？

本当はリストからボタン1つで書類シートとデータ送信用シートができれば最高なのですが
そこまでの改変は難しそうで…

2019/03/13(水) 22:46:46.17

>>922
それぞれのボタンに組まれたマクロを一つのボタンにまとめる

2019/03/13(水) 22:48:55.81

作り直した方が遥かに楽なパターン

2019/03/13(水) 22:53:39.78

ごめん>>923は内容勘違いなので忘れて
一括で送信シート作るボタンを一つ作ってその中に、

forで選択した書類シート分回す
　その書類シート内の送信シートを作るボタンClickを呼ぶ
Next

ってのはどうですか

2019/03/13(水) 23:04:41.65

>>922
見られちゃマズイデータ消してアップロードすれば誰かが作ってくれると思う

2019/03/13(水) 23:09:57.29

>>921
そんな技があったのね。　ありがと

2019/03/13(水) 23:13:30.84

Clickを呼んでも、クリックするわけじゃないが、
クリックされちゃいそうで怖い。

2019/03/14(木) 01:04:48.38

ピボットテーブルの集計結果使いたい時って今まで計算用シートで作成→せる決め打ちで取得　てやってたんだけど稀にカラムずれる
こういうのvbaだけで計算する関数とかないよね?

2019/03/14(木) 01:36:50.73

ピボットテーブルはアドホックな集計や分析に使うもんだろ
やることが決まってるんなら要らん

2019/03/14(木) 04:24:36.89

>>922
現物見てないからよくわからないんだけど、
リストシートや書類シートにボタンがついてるってのは
マクロ有効ブックの中にそれらのシートがあるってこと？

出来ればデータとマクロは別のブックに分離しといたほうが良いんじゃないかな。
そういう作りにしてれば改修も簡単になりそう。

2019/03/14(木) 08:13:54.22

>>927
普通に
If A Then
If B Tnen
If C Then
A And B And Cの時の処理
EndIf
EndIf
EndIf
とするかな。

でも、これでの速度アップより、他に速度ダウン要因を減らした方が良さそう。
自分はIf A AndB And C Thenを気にせず使うな。

2019/03/14(木) 08:39:22.13

>>932
Aが成り立たないときBを評価するとエラーで止まってしまう場合とかが悩ましいんだ

2019/03/14(木) 09:04:59.36

>>921
それ保証されてるんだっけ？

2019/03/14(木) 09:13:36.88

>>932
うん、他にやることありそうな気がするぞ
昔の俺みたいに実はコンパイルしてなかったとか笑

2019/03/14(木) 09:57:51.33

>>934
一応言語規格上保証されてるね

2019/03/14(木) 10:12:09.94

>>936
どこかでドキュメント化されてる？

2019/03/14(木) 10:14:23.93

なんとなくだけど、VBAであまり離れ業を使わないほうが良いとは思う

2019/03/14(木) 10:34:24.95

>>937
https://docs.microsoft.com/en-us/openspecs/microsoft_general_purpose_programming_languages/ms-vbal/d5418146-0bd2-45eb-9c7a-fd9502722c74
にある Published Version の PDF の 5.4.2.10 Select Case Statement に書かれている。
82ページの一番下二行だね。

2019/03/14(木) 12:36:06.81

>>933
それは別の問題だよ。
そういう場合は最初からIf A And B And C Thenなんてやらない。

Bの評価でエラーが出ない時でも必ずBの評価をすることで速度ダウンするのを防ぐために
If A Then
If B Then
EndIf
EndIf
とするかと言えば、普段はやらないということ。

それで得られる速度アップより他の修正をした方がよっぽど効果有るだろ。

2019/03/14(木) 12:53:41.51

アスペかよ

2019/03/14(木) 13:54:08.96

>>939
なるほど、ちゃんと明記されてるんだな、ありがとう

2019/03/14(木) 13:57:37.28

短絡評価の件で速度云々言ってるのはID:YBKQY7Hwdだけだし

2019/03/14(木) 14:46:00.52

いやいや、役に立った（参考になった）ボタン押してますから

2019/03/14(木) 15:13:23.25

ショートサーキットが活きてくるのは、Boolean型の関数を並べるときだな
If Func1() Or Func2() Then
みたいなケースだと実行時間がかなり違ってくることも起こりうる

2019/03/14(木) 15:21:32.05

標準関数の戻り値に文字列"false"が返ってくる言語があるらしい

2019/03/14(木) 19:08:46.48

>>943
伝わって無いようだな。

まず、>>910の速度の話から始まっている。
で、>>932は「俺は」速度を気にしないから普段はやらないけど、とにかくネストすりゃ良いって話。

>>927に速度が念頭に無かったとしても>>932は成立するんだよ。

2019/03/14(木) 19:08:54.68

で、>>933は俺が普段やらないという所に反応したわけだ。
それに対して>>940はBのエラーが想定される時は普段もネストするって話。
こちらも成立してる。

あえて言えば>>933が少し外してる。

2019/03/14(木) 19:33:40.81

>>947-948
速度の話（>>910）と短絡評価の話（>>917）は別な
混同してるのはお前だけだぞw

2019/03/14(木) 19:58:36.19

>>949
バーカw

短絡評価云々についてはネストすると言ってる。
でも普段は速度を気にしないからネストしないと言ってる。

ここでの速度の話はついでの話だ。
ついでの話で速度を蒸し返したのは俺が最初で、別に>>917が速度のことを考えていなくても何も問題ない。
ついでを書かないと、どんな時も俺がネストしてると思われるから書いてるんだ。
>>917が速度のことを気にしてると俺が思ったからでは無い。

このついでに反応した>>933は「俺が最初に蒸し返した」のでは無く、「俺が>>917を勘違いした」と思ったんだろう。

2019/03/14(木) 20:19:12.86

もっと争え