Excel VBA 質問スレ Part49©2ch.net

2017/06/26(月) 02:15:14.60

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

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

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

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

※関連スレ
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/

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

2017/07/09(日) 20:42:05.40

>>174
Dim sheet as Worksheet
Set sheet = Sh

これをmsgboxの前に追加すると全部の変数がウォッチで見られると思う。

ググってみたらNewで作ったオブジェクトは初めて利用するまで空ですよ
らしいので、新シートのオブジェクトは内部的にNewで作成している？
Sh.nameが引けるのは何ででしょう？

2017/07/09(日) 20:43:00.97

それよりも改行しないコードかく人は何を考えてコード書いてるのか気になる

2017/07/09(日) 20:43:34.76

>>174
objectで受け取ってるからshのままでは何のクラスなのかわからないからどんなプロパティがあるかわからない

Nameプロパティを指定したら
Nameプロパティを持っているクラスとして解釈して
Nameプロパティの位置を文字列として表示しただけ

worksheetクラスのデフォルトプロパティがNameだからかな

countプロパティとかとして解釈させたら想定外の値になると予想

2017/07/09(日) 20:46:50.81

>>177
VBAマスター現る。
なっとく。

2017/07/09(日) 20:49:10.77

>>178
VBAだけじゃなくて他のプログラミング言語C言語とかと共通の考え方

メモリ確保とかキャストとか

2017/07/09(日) 20:54:20.52

>>164
1が実行されるまでの間にactivesheetが変わるかもしれないからactivesheetでは不正解だと思う

出来るだけactivesheetを使わない方が想定外の動作にならないと思う

2017/07/09(日) 20:55:59.71

てかお前らそんなに一か八かでactivesheet使ってんのかよw

2017/07/09(日) 21:19:51.98

これが通るけど、ウォッチ式には現れない
多分ウォッチ式の不具合だと思う
そう考えないと気持ち悪くて仕方ないし疲れてきた・・・

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Calculate
MsgBox Sh.Index
End Sub

http://stamp-uploda.com/src/file7299.jpg

>>175
sheetでもShでも見れるようになりました
Shの参照をコピーしたのでインスタンスが生成されたのかな

>>177
indexプロパティでも通りましたし
Sh.Calculateメソッドでも通りました

>>181
使ってないよ。sheetの指定はsetした変数を使ってる
昔はactive～やselect～を使ってたけどね

2017/07/09(日) 21:38:18.71

>>182
worksheetクラスではないクラスのプロパティを指定したらどうなると予想する？

例えばRangeクラスのプロパティとか

もしかしたらサイズとかを見ていてサイズが異なる場合はキャスト出来ないかもしれないど

メモリ領域には値が格納されているけどそれをどう解釈するかはクラスが決まらないと特定できない

2017/07/09(日) 21:45:51.77

>>183
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Debug.Print Sh.Range("a1").Address
End Sub
で
$A$1
が返ってきたわ

http://stamp-uploda.com/src/file7301.jpg

2017/07/09(日) 21:47:17.93

写ってないけどウォッチ式のshのプロパティは不明なままね
ウォッチ式がおかしい以外考えにくい

2017/07/09(日) 22:03:56.91

Private Sub Workbook_NewSheet(ByVal sh As Object)
　Dim obj As Object
　Set obj = sh
　MsgBox obj.Name
End Sub

これだと中身が見られないけど通る。
やっぱり>>183の型キャスト説じゃないかなぁ。
ローカルウィンドウでも見られないし。

obj.Value = "aaa"　で実行時エラーになる。
「ん～何か知らないけどValueに入れるよ？あっねえわｗ」　ってことでは？

.Nameや.Range().Addressはあったんで通る。

2017/07/09(日) 22:23:32.11

>>186
それはrange指定してないっす・・・

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Cells(1, 1).Value = "aaa"
End Sub

で通ったよ

2017/07/09(日) 22:31:30.92

>>184
Debug.Print Sh.Range("a1").Address
ではなくて
Debug.print sh.address
だったらどうなるかって意図だったけど
ためしたら実行時エラーになる
これはtypename(Sh)を表示したらわかるけど
Shという変数自体にクラス(型)に関する情報を持っているから
無理矢理Rangeクラスのプロパティを表示させようとするとエラーになる

dim rng as Range
set rng = Sh
とかやっても実行時エラーになる
文法的にはエラーじゃないからコンパイルは通る

VBAが型チェックしてバグにならないようにしていると思う
他の言語だとこれができるものもある

2017/07/09(日) 22:35:23.27

>>187

ShがWorksheetクラスだっていうのが判ってるからRangeを指定するってわかるけど
Objectクラスだったら何のプロパティを持っているかわからなくない？

Objectクラスはすべてのクラスのスーパークラスだから何でも参照できる
Sh as Objectと宣言されているShが実態はRangeクラスである場合もある
その場合は、Sh.Value = "a"でも実行時エラーにはならない

2017/07/09(日) 22:50:14.83

>>188-189
なるほど、意図が分かりました。コンパイルエラーになる意味もわかりました、ありがとうございます

以下の状態だと、shの型はobject/Sheet5としてウォッチ式に認識されます
この「Sheet5」というのは何でしょうか？
デフォルトプロパティでしょうか？

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim sheet As Worksheet
Set sheet = Sh
Debug.Print 1 'ここでブレークポイント
End Sub

2017/07/09(日) 22:58:28.17

しかし>>175がどうしてもわかりません

object型をobject型に渡してもプロパティは分からない、それは分かります
http://stamp-uploda.com/src/file7303.jpg

object型をworksheet型に渡すと、ウォッチ式で見れるようになる、それがわからない
http://stamp-uploda.com/src/file7302.jpg
全部のプロパティ・メソッド見たら全部一致してたからworksheetとしてコピーしますね～ということ？

2017/07/09(日) 23:03:28.23

>>190
Set sheet = Sh
を実行した時点で、Sh = sheet = Worksheetクラスのオブジェクトと特定可能
なのでworksheetクラスのデフォルトプロパティを表示しているのではないか

ウォッチ式にsheet変数も追加すると、そのタイミングで
Shの他のプロパティも見えるようになった

2017/07/09(日) 23:06:31.26

>>191
型キャストで
Shがワークシートだと判明したので
Shとsheetは同じ物なので
両方のプロパティが同じで表示されるのではと思うが。
詳しくはマスターにまかせる。

と思ったらもう回答されていた。

すごい勉強になった。

2017/07/09(日) 23:09:27.92

>>191
ウォッチ式の表示更新タイミングはその内容が特定されたときに
即座に表示更新されるとは限らない
つまり表示更新されるよりもずっと前に表示すべき内容が特定されていた可能性もある

2017/07/10(月) 09:22:58.03

activesheet使うのはworksheets.copy使うときぐらいだな
真面目に作るときは書式とか直で指定するように頑張るけど

2017/07/10(月) 12:08:47.45

レコードセットをそのままシートに張り付けると、元の型を継承して数字のみのカラムも文字列として貼られるんだけど、さすがにどうしようもない？

2017/07/10(月) 12:39:11.04

>>196
あとで一括して和0すりゃいいじゃん

2017/07/10(月) 12:47:22.77

>>197
和は加算の結果を表す言葉
0を加算とか足す0じゃないと意味が違ってくる

因みにレコードセットの型はcolumnの最初の7行分のデータから推測されるとかだったと思うから
目的の型になるようにソートすればいいかも

2017/07/10(月) 13:37:23.50

関数の引数にbyval ws as Worksheetってやるとどうなるの？

worksheetに対しては、byrefしか使ったことない

2017/07/10(月) 14:25:22.38

オブジェクト型はbyval渡し出来ないんじゃなかったっけ？

2017/07/10(月) 14:36:15.11

>>198
なるほどそうだぬ
「ゼロを一括して加算(D)すりゃいい」と言い直させてもらう

もともと具体的に書かず曖昧な表現を目指していた

2017/07/10(月) 14:38:53.50

>>200
できるよ。
メソッド呼び出しもそのオブジェクトに対するものとなる。
違いが出るのは代入(=)演算の意味だけだと思う。

2017/07/10(月) 15:15:06.40

>>199
たぶんプロシージャ内の set ws=hoge が引数に反映されるかされないかが変わってくる

2017/07/10(月) 16:10:50.58

>>203
ありがとうございます。
時間あるときに試してみます！

2017/07/10(月) 17:19:20.24

thisworkbookをoptionalな引数のデフォルト値にしたいんだけどできない
何かいい方法知りませんか

2017/07/10(月) 18:34:12.31

>>205
確認せずに答えるけど、できないならOptionalのデフォルト値をNothingにして、
モジュール内で判断・分岐する

If a Is Nothing Then Set a = ActiveWorkBook
とか

2017/07/10(月) 18:40:40.88

デフォルト値は定数式で、って言われるな

デフォルト値を文字列にして"thisworkbook"、evalで…とか考えたけど2013持ってないから試せない
そこまでしても読みにくくなるだけなので、素直に>>206みたいに条件分岐させたほうが良いと思う

2017/07/10(月) 19:04:18.69

質問です。
ユーザーフォームをShowModal=Falseで表示しており、フォームを表示したまま、フォーカスをフォームからセルに移動することは可能でしょうか？
cells(1,1).select や application.goto cells(1,1) ではフォーカスがフォームから動きませんでした。

2017/07/10(月) 19:17:57.83

>>208
昔やったことあるなぁ
と思って試したら、仕様変更でできなくなってるっぽい
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=44929;id=excel
2010は無理だった

**208** (ﾌﾞｰｲﾓ MMdb-MhHl) · 2017/07/10(月) 19:26:18.02

>>209
回答ありがとうございます。
後出しで申し訳ないですがバージョンは2007です。
試せるのが明日になりますが、appactivate試してみます。

2017/07/10(月) 20:52:31.16

>>206
やっぱりそうなるか
でもなんでできないんだろうインスタンスとして実体化されてないから?
そんなことないよな

2017/07/10(月) 21:00:26.90

>>211
コンパイル時点で決まってないからかな～

**208** (ﾌﾞｰｲﾓ MMdb-MhHl) · 2017/07/11(火) 12:39:40.24

>>209
AppActivateもApplication.Visible=Trueでもいけました！
ちなみに以下のも試しましたが全部ダメでした。
ActiveWindow.Activate
ActiveWorkBook.Activate
ActivateSheet.Activate

2010以降だとWIN32APIとかでシートのマウスクリックを挟まないとダメかもですね。

2017/07/11(火) 19:59:21.82

>>213
良かったね
2007と2010でそんなとこに差があるとは驚いた

2017/07/12(水) 19:38:42.24

Sh As Object
Sh

2017/07/12(水) 19:41:14.38

↑誤送信すまん。

Sh As Object
Sh As Worksheet

どちらでも動くのは分かる。
Objectにするのって、
どんな値が入るかわからないから、
Variantでいいやって考えるのと同じ?

2017/07/12(水) 19:46:47.71

どんな値が入るかわからないコードってどんなコード？
デバッグ用にエラーで分岐させた先とか？

2017/07/12(水) 19:50:58.17

つっこむならせめて値と型の違いぐらい分かっていて欲しかったなあ

2017/07/12(水) 19:53:10.98

>>217
variantさんディスってるの？

2017/07/12(水) 20:00:38.68

>>218
普通にすまん

>>219
あってもなくても一緒では？
variantを書くのは「どんな型が入るか分からない」と明示する以外に利点があるのだろうか

2017/07/12(水) 20:10:18.44

NewSheetイベントの引数の場合、Shには
「Worksheet オブジェクトまたは Chart オブジェクトが渡されます」
と書いてあるので
どっちが渡されてもいいようにObjectなのでしょう

2017/07/12(水) 21:14:58.86

俺の書くVBAはVariantばっかりだけど問題ないよね？

2017/07/12(水) 21:19:09.13

個人利用である限りはね

2017/07/12(水) 21:27:49.15

書籍にさえ糞みたいなコードが平然と書かれてるVBA
どんなコードだってヘーキヘーキ

2017/07/12(水) 21:28:35.71

そもそも宣言しなくても特に問題はない
vbaの型なんてやわらかすぎてあってないようなもん、死にはせん

2017/07/12(水) 21:34:45.69

>>223
もちろんさ！
ちょっと単体テストすりゃ全然平気！

唯一気をつけるのはNULLが来た時の扱いだけど、「NULLがあり得る」場合は結局避けられない問題だし

2017/07/12(水) 21:40:21.42

ただこーゆー所でVariantプログラムって言うとなんやかんやめんどくさいので胸の内に秘めとくことをオススメする

2017/07/12(水) 21:47:21.99

俺がそのVariantを全部なくしてやるよ！
　↓

2017/07/12(水) 21:48:46.29

NULLじゃなくてnothingでは

2017/07/12(水) 21:54:45.14

>>229
まあNothingが来れば気を付けなきゃいけないだろうけど、NULLはDBから読んだデータソースに含まれている場合が多いから。

2017/07/12(水) 21:55:54.59

She As Object
Set She = New LoveDoll
She.DressUp(MaidCostume)
She.Attach(SilliconVagina)
I.Fuck(She)
I.FinishAlone()
She.Detach(SilliconVagina)
I.Wash(SilliconVagina)

2017/07/12(水) 22:01:13.24

５行目にSet her = She があればよかった

2017/07/12(水) 22:40:32.03

VBEのインテリセンスを止める方法教えてください

2017/07/13(木) 06:37:01.82

NewSheetイベントの引数ShがObject型なのは、WorkSheetクラスとChartObjectクラスの両方でNewSheetイベントの呼び出しが定義されていて、実行時にしかShの型が決まらないからだと思うが
Workbook.Sheets(Index)の戻り値がObject型なのと一緒

2017/07/13(木) 12:57:05.14

variantとかobject形はコンパイルエラーで止めてくれないからめんどい
今ちょうど使いたい場面出てきてるんだが二の足踏んでる

2017/07/13(木) 13:12:36.23

指定日(A)から現時点(B)までの経過時間をミリ秒で取得したい場合、
どのようにしたら上手く取れるでしょうか？
VB.NETの時はGetTimeMillisecondsがあったので、(B-A).GetTimeMillisecondsで取得出来たんですが。

2017/07/13(木) 13:25:10.65

APIのGetTickCountを使う

2017/07/13(木) 13:36:43.69

>>237
ありがとうございます。
GetTickCountも試してはみたんですが、システム稼働してからの時間だからか
自分では上手く取得できませんでした。
「指定日」（今回やりたいのは1970/1/1）からの経過時間をミリ秒取得するには
どのようにしたら良いでしょうか？

2017/07/13(木) 14:33:52.00

>>236
doubleに変換して計算してまた変換するとか

2017/07/13(木) 17:11:00.53

>>238
ミリ秒に換算しろ

2017/07/13(木) 18:03:16.52

>>238
どうせそんな長いスパンならミリ秒はなんちゃってでいいんでしょ

Application.WorksheetFunction.RoundDown((Date - CDate("1970/1/1")) * 86400000 + Timer * 1000, 0)

2017/07/13(木) 20:31:54.44

>>236
timediff()関数だかdifftime()があったはず

2017/07/13(木) 20:34:22.33

datediff()だった
ただ秒単位が最小だった

2017/07/13(木) 21:05:07.35

Win32API の GetSystemTime( ) を呼び出して後は頑張って差を計算するなりすればいいだけでしょ

2017/07/13(木) 21:30:25.15

そもそもソースがクソ精度のシステム時刻だからmsecなんてあてにならない気がする
直前にNTPで同期をとるとか？

2017/07/13(木) 21:37:03.54

指定日から当日までをdatediffの秒単位で取得して
当日からその時刻までをmsで取得して
足し合わせればいいんじゃね

2017/07/13(木) 21:47:59.63

ミリ秒の精度が求められる操作をVBAでって嫌だなぁｗ

2017/07/13(木) 22:44:18.16

作成するファイルやデータの一意な命名、識別とかに使うんじゃないの
だいたいでいいと思うわ

2017/07/14(金) 06:02:22.03

要件推理大会は終わった？

**デフォルトの名無しさん** (JP 0H32-v2r+) · 2017/07/14(金) 10:03:16.31

要件って喪前らは横浜のシューマイ屋さんか!!!!

(´・ω・｀)b

2017/07/14(金) 10:25:11.37

Range("250").ClearContents

2017/07/14(金) 22:15:23.51

!!!?

その手があったか!!
今まで、真面目に名前を付けることしか考えなかったわ。

Dim Sh As Worksheet
Set Sh = Thisworkbook.Worksheets("(´・ω・｀)b")

こんなのもありだな。

2017/07/14(金) 22:29:36.70

そういやみなさん
その場限りの適当マクロじゃなくて
ずっと使い続けてメンテも必要なVBAでセルにアクセスするなら名前経由でやってます？

自分はまずセルに名前をつけて
Const hoge = "セルにつけた名前"
Range(hoge).value = "うんこ"

みたいなことやってます
ちなみに、シート名でもこれやってるんですが‥

シート名やセル名が変わる可能性を前提にしたら
こういうアプローチになるとは思うんですけど
結局凄く無駄なことをやってる気がして…（結局変えることあんまないし）

特にセルの名前に関しては、どうせその文字列をConstのところにハードコードするなら
名前つけずに
Const hoge ="A1:B5"
でもええやんけとか思ってるんですが
行挿入とかに追従してくれる名前の方を使うべきかなぁとか思ったり…

2017/07/14(金) 22:44:26.51

>>252
こういうのもあるよ
https://github.com/masarakki/nyaruko_lang

2017/07/14(金) 23:03:04.97

>>253
セルに文字列で意味をつけて
その文字列を検索して特定する
そのセルからのオフセットを固定しておく

例えば列名とか行名とか

2017/07/14(金) 23:10:22.98

>>253
よくある表の列の増減対応する場合は
列挙体で列名を定義しておいて
Cellsで列名を参照させてる。
途中にぶっこんでも列挙体のほうをちょろっと書き換えればいい。

行にも対応させると、面倒かも。

2017/07/14(金) 23:41:07.46

>列の増減

会社のやつ、一切弄れないようにしたわwwwww
セルを保護するとかじゃなくて、
データの更新時に、全消しして再描画するのwwwwwwwwww
列挿入とかしても無駄ですよってwwwwwww

2017/07/15(土) 00:03:43.14

そういうの、このスレだと基本だと思う
・正規化したデータを引っ張ってくる
・ボタン押したらvbaで構築。シートすら削除して再作成
こんなんばっかだろ。誰もさわれなくて非難轟々だけど、どうかんがえてもこっちのほうが安全なんだよな

2017/07/15(土) 00:11:34.38

https://www.youtube.com/watch?v=dpizDazz9Fg
https://www.youtube.com/watch?v=HpX_uJUX92U
https://www.youtube.com/watch?v=XHV9lUNP6rA
https://www.youtube.com/watch?v=4UPAWP730a0

2017/07/15(土) 00:12:14.92

学ぶ気のない人からとても怒られるやつ

2017/07/15(土) 00:49:22.52

>>253
普通にやってるしシート名とかも定義する
あとは汎用的なクラスや関数いくつも作っておいて雛形エクセルにしてる

2017/07/15(土) 04:37:45.96

>>256
コードをいじらないといけないじゃないか

2017/07/15(土) 04:53:42.10

>>258
データの更新自分しか出来ないんじゃね

参照しか出来ないならwebシステムにすればいいんじゃね

2017/07/15(土) 08:42:06.63

>参照しか出来ないならwebシステムにすればいいんじゃね

ヒント：権限

2017/07/15(土) 09:43:36.21

>>253
> ずっと使い続けてメンテも必要なVBAでセルにアクセスするなら名前経由でやってます？
状況次第だけどよくアクセスするとか重要なセルは適当なところでまとめて変数に代入してる
Dim hoge As Range
Set hoge = Range("A1")
...
hoge.value = "うんこ"

> 行挿入とかに追従してくれる名前の方を使うべきかなぁとか思ったり…
行とか列の挿入を想定するなら名前をつけてそれで参照する
ユーザーが作成するようなシートなら特定の文字列を検索して場所を特定するとかもやる

2017/07/15(土) 09:44:21.75

>>264
自分に権限がないなら権限を持ってる人に許可貰えばいいだけじゃね？

なんの権限か不明だが

2017/07/15(土) 10:08:30.41

>>258
批難がでるって事は不便な点とかがあるって事で
仮にそれが商品だとしたら売れない可能性があるってこと

競争相手がいたら使われなくなる商品

2017/07/15(土) 11:40:57.26

>>267
売れてるiPhoneのスレでも見てこいよ
売れない/使われないものをいちいち非難するほど人は暇じゃない

2017/07/15(土) 11:48:46.46

>>268
売れないなら批難は出ないと言う主張なら
批難轟々なら売れるって事だよな

じゃ売ってみればいいんじゃね？

2017/07/15(土) 11:50:44.37

>>268
それは代替品がある場合だ
その代替品に乗り換えれば済むから

社内とかの代替品がなければ嫌々でもそれを使うしかない

2017/07/15(土) 12:33:52.46

>>269-270
レスを分けて書く奴はもれなくバカの法則 w

2017/07/15(土) 16:36:10.60

>>271
それ面白いか？
別に面白くないけどなぁ

2017/07/15(土) 17:34:03.53

VBAのマクロって

・マクロ制作者本人の操作を補助する為のものであり、他人に使わせる事は想定されていない
・オフィス製品の操作を補助する為のものであり、アプリケーションの本来の振る舞いを超えた動作は想定されていない
・ユーザーフォームはマクロの操作を容易にする為のものであり、別のアプリケーションであるかの様に振る舞う事は想定されていない

なので想定されていないものを作ろうとしたら不便さを感じるのは当然
どんな言語もそうだけど、想定された使い方なら便利だし、そうでないなら不便だよ

2017/07/15(土) 17:40:17.34

>>255
なるほど
例えば
○年○月　の○のセルを指定する際に
「年」のセルを探してOffset(0,-1)する、みたいな感じですかね
自分はこれも名前つけて管理してましたが
セルが１つの場合はそれで良さそうですね
ただ、全く同じ内容のセルが検索範囲にないことが保証された運用にしないとバグりそうなのでちょっと怖いかも

>>256
表に対してレコードセットみたいなアクセスをしたい場合に
つい最近その方法を知って目からウロコでした
とにかくインテリセンスを効かせたい派なので・・
http://thom.hateblo.jp/entry/2015/12/17/000132
こういうことですよね

行方向に関しては、列のように各行に別々の意味を持たせるような使い方はしたことないので
問題ありませんね

>>261
自分は簡単なSQLを作る（ANDとかORで結合するだけ）に特化したStringBuilderみたいなのだけ使ってますね

>>265
やっぱ可変範囲のこと考えると名前つけるのは意味ありますよね