Excel VBA 質問スレ Part68

2020/08/30(日) 11:55:27.33

!extend:checked:vvvvv:1000:512
↑同じ内容を2行貼り付ける

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

※前スレ
Excel VBA 質問スレ Part67
https://mevius.5ch.net/test/read.cgi/tech/1593104489/

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

2020/09/11(金) 09:37:00.89

>>221
>>146 です。色々調べた結果、TreeViewのMouseMoveやMouseDownのイベントで渡される座標が他のフォームとは異なりピクセルで渡される事が原因でした。
なのでGetDesktopWindow,GetDC,GetDeviceCapを用いてDPIを取得し、
取得した値で1440(1440twips=1Inch)を割り、
その値をピクセル数に掛ける事でポイントでの換算に成功しました。
標準の設定ですとDPIが96になるそうなので1440/96で15、となるようです。

2020/09/11(金) 16:13:39.65

VB6だとScreen.TwipsPerPixelXってあったんだがなあ

VBAだとPointsToScreenPixelsXとかあるなと思ってみてみたけど、これ、つねに72DPIで計算するっぽいな

2020/09/11(金) 18:44:31.89

>>192
それだと1回めの選択範囲で動いて、移動したら動きますよね？
1回目の行や列を記憶させて2回目で比較する感じですか？

2020/09/11(金) 22:11:41.98

>>222
>>223
うん、その辺みんな知ってるけどTreeViewの問題って違ってたと思うんだが。

2020/09/12(土) 08:12:41.83

動的配列に値を追加していきたいのですが要素番号を0ではなく1から始める方法はありませんか？

2020/09/12(土) 08:24:59.90

Sub testtttt()
Dim arr() As Variant

ReDim arr(1 To 1)
arr(1) = 10
ReDim Preserve arr(1 To 2)
arr(2) = 20
ReDim Preserve arr(1 To 3)
arr(3) = 30

MsgBox arr(1) & " " & arr(2) & " " & arr(3)
End Sub

ちょっとはぐぐれよな
https://www.relief.jp/docs/excel-vba-shift-array-index-to-1-start.html

2020/09/12(土) 08:28:00.30

『[ VBA ]「Option Base 1」使うなよな！反論は聞かない！』

2020/09/12(土) 15:06:14.92

アレイリスト使うほうがかんたんじゃね？

2020/09/12(土) 20:26:44.99

先頭を１にするのはセンスないね

2020/09/14(月) 18:06:26.03

i++とか+=使えないのマジで不便だな
前置インクリメントもできないし一行でもend ifとかloop書かなきゃいかんしめんどくさくね？
この言語もうちょい進化しないの？

2020/09/14(月) 18:10:50.73

１行でEnd If書きたくないならThenの後に半角スペースいれたら改行しないで処理書いてみ

++とかは禿同

2020/09/14(月) 18:13:56.53

>>232
おおこんなことが、、、
ありがとう！

2020/09/14(月) 18:15:50.60

>>231
馬鹿は死ねよ

2020/09/14(月) 19:51:50.83

ガイジ君ブチ切れw

2020/09/14(月) 19:57:09.34

ざわ・・ざわ・・

2020/09/14(月) 20:46:20.36

+=使えるだろうが

2020/09/14(月) 20:54:32.80

使えません

2020/09/14(月) 20:56:59.34

あー、VB.Netスレだと思ってた。スマン、死んでくる。

2020/09/14(月) 21:01:00.11

逝かないで

2020/09/14(月) 21:01:06.64

制御構造は、キーワードの選び方がやや独特だけど、基本的なものについては、概ね他の言語と同等のことはできると思う。
どちらかというと、変数の宣言・定義が一緒にできないとか、配列・コレクションの要素参照が角括弧でなく丸括弧だとか、プロシージャ呼び出しの丸括弧の要否とかの方が不便さを感じるかな。まぁ、これらも慣れと言われればそれまでだが。

2020/09/14(月) 22:06:22.62

gotoとラベルでできるっちゃあできるけどcontinueとbreakが欲しい

2020/09/14(月) 22:20:37.59

breakは、一応Exit ～が概ね対応しているのでは（While～Wendでは使えないけど）？
脱出するループを明示的に指定するので、breakより便利な場合もごくたまにある。もっとも、二重のForループから抜ける場合などは結局GoToに頼ることになるので中途半端ではあるけど。

2020/09/14(月) 23:27:12.72

俺もたまにラベルでcontinueするけどめちゃくちゃ見栄え悪くて嫌

2020/09/15(火) 08:35:58.25

>>232
>>233
基本、それは使わないな。
読みやすさとか構造の分かり易さ優先。
勿論、時と場合による。

2020/09/15(火) 09:16:48.30

ユーザーフォームの２つリストボックスで選択された値を変数に代入する方法を教えてください

■Module1
Public str As String

■ThisWorkbook
Sub tes1()

UserForm1.Show vbModeless
Range("A1").Value = str
End Sub

Sub tes2()
Range("A2").Value = str
End Sub

2020/09/15(火) 09:17:03.49

■UserForm1
Private Sub UserForm_Initialize()
Dim r As Long
For r = Year(Date) - 1 To Year(Date) + 2
UserForm1.ListBox1.AddItem r & "年"
Next r
For r = 1 To 12
UserForm1.ListBox2.AddItem r & "月"
Next r
UserForm1.Show vbModeless
End Sub

Private Sub OKbtn_Click()
str = Replace(UserForm1.ListBox1.Text, "年", "") & "_" & Replace(UserForm1.ListBox2.Text, "月", "")
Unload UserForm1
End Sub

テストで変数strをＡ１・Ａ２に入れたいのですが、この方法だとどちらも入りません。

2020/09/15(火) 09:33:07.65

ボタン押してstrに代入するところModule1.strじゃないとダメなんじゃないの

**246** (ﾜｯﾁｮｲ 11cc-QmsY) · 2020/09/15(火) 09:45:50.10

>>248
試しましたがだめでした

追記
ブックを開いたまま、tes1を２回目に実行するとA1には書き込みますが、
ブックを開いて、１回目のtes1実行ではA1には書き込まれないようです。

2020/09/15(火) 09:57:04.92

モードレスだから
ボタン押した時にUnload UserForm1する前にModule1.tes2を呼び出さないとだ

Sub tes1()
UserForm1.Show vbModeless
End Sub

Sub tes2()
Range("A1").Value = str
Range("A2").Value = str
End Sub

**246** (ﾜｯﾁｮｲ 11cc-QmsY) · 2020/09/15(火) 11:27:58.65

>>250
ありがとうございました。解決しました

2020/09/15(火) 18:56:31.81

AccessVBAを使用しているのですが、標準モジュールを変数に格納する方法がわかりません。
フォームコントロールの様に変数に入れて扱う方法はありませんでしょうか。

2020/09/15(火) 19:29:57.34

ありません

2020/09/16(水) 00:02:05.57

Sub test()
MsgBox Me.TextBox1.Value
Me.Name="a"
Me.Name="Sheet1"
End Sub

1回目の実行 OK
2回目の実行 MsgBoxでエラー
3回目の実行 OK
4回目の実行 MsgBoxでエラー

なんでやねん!

2020/09/16(水) 01:13:07.34

>>254
F8 でステップ実行してみ、あとエラーの内容くらい書いてもよくね？

2020/09/16(水) 10:04:16.38

>>253
ありがとうございます。
フォームを格納できるなら標準モジュールも出来るのかと思って質問しました。
標準モジュール内に書かれた関数の引数として参照先の標準モジュールを渡したかったのですが、別の方法を考えてみます。

2020/09/16(水) 18:43:38.23

動画ファイル（拡張子 mp4,avi等々）の長さ（時間）をvbaで得ること出来ませんかね。
動画用のプログラムでは出ているから何らか手段はあると思うのだけど。

2020/09/16(水) 19:23:25.54

>>257
できる
はい、次

2020/09/16(水) 19:55:21.82

>>257
拾い物、動くかどうかは知らん

Sub ボタン1_Click()
Dim Shell, Folder
Set Shell = CreateObject("Shell.Application")
Set Folder = Shell.Namespace(Range("A1").Value)
Fil = Dir(Range("a1").Value & "\*.*")
Rows("5:65536").ClearComments
i = 5
Do While Fil <> ""
For j = 0 To 40
Cells(i, j + 1).Value = Folder.GetDetailsOf(Folder.ParseName(Fil), j)
Next
i = i + 1
Fil = Dir()
Loop
Set Folder = Nothing
Set Shell = Nothing
End Sub

2020/09/16(水) 20:18:38.96

手を抜くんじゃない！
まじめにヘッダー読み込んで解析しろよ

2020/09/16(水) 20:21:47.41

ファイルのプロパティからは取れない場合もあるよ

2020/09/16(水) 21:31:45.86

>>255
今環境ないんだけど、
アプリケーションの定義何とかか、
インデックス何とかか、
オートメーション何とかのどれか。
何か変なものがSetされちゃってるのかと思ったけど、
MeじゃMeしかないよね?

2020/09/16(水) 22:10:00.45

>>260
昔、解析したことがある。
aviとかは割と簡単だね。

2020/09/16(水) 23:12:03.30

すごく初歩的な質問ですみません

&演算子で文字列と数字をつないで文字列を作った時に
出来上がった文字列に""を付けなくていいのはなぜなのですか？

MsgBox "あいうえお"　は""を付ける必要がある
MsgBox "A" & hoge は文字列扱いのはずなのに""を付けて MsgBox ""A" & hoge"のようにしなくていいのが解せません

2020/09/16(水) 23:23:17.92

&とhogeは文字列ではなく演算子と変数

2020/09/16(水) 23:24:34.57

""で囲ったら演算子でも変数でもないただの& hogeという文字列にしかならない

2020/09/16(水) 23:48:53.36

ありがとうございます

>""で囲ったら演算子でも変数でもないただの& hogeという文字列にしかならない
なるほど確かにです

では、&演算子でつなぐと文字列扱いになるという認識が間違っているのでしょうか？
それとも文字列扱いにはなるけど上記の理由から外すことになっているという感じでしょうか？

2020/09/17(木) 00:09:43.18

>>267
その場合、
hogeは文字列として認識してください
という命令を出している
つまり
文字列(”A”) と(＆) 文字列(hogeという変数に入っている文字列) を繋ぎ合わせて表示してください
というのが”A” & hoge

2020/09/17(木) 00:09:48.11

ダブルクォートには引数の囲むような意味はない
引数を囲む記号は丸括弧になる
たとえば　X = msgbox("a" & hoge)　みたいな書き方になる
返り値をとらない場合は()が省略可能ってだけ

2020/09/17(木) 00:11:18.08

リテラルと変数について調べると理解できるかも

2020/09/17(木) 00:13:09.47

>>267
hoteの型は何なんだ

2020/09/17(木) 00:21:16.12

値に型ってのがあるのを理解しよう
表示したいのは、"A"という値と変数hogeの値（を連結したもの）だろ

&演算子でつなぐと文字列扱いになるってのは、演算対象を文字列に変換して結合するってこと
この場合、hogeの値が文字列以外でも文字列に変換して評価するってことだ

基本を理解したいなら、なんの説明もなく&でつなぐと文字列扱いとかいう教え方するところは避けたほうが良いかな
とりあえず動けばいいってならまあすきにすればいいけど

2020/09/17(木) 02:20:25.53

MsgBoxの第1引数は文字列型の値である必要があり、文字列型の値を表す式として典型的なのは、①文字列リテラルか、②文字列型の値が格納されている変数（文字列型変数）の参照。二重引用符は、文字列リテラルの表記方法に過ぎない。
&演算子は、本来、2つの文字列型の値を被演算数とする演算子だが、被演算数が文字列型の値と数値型の値であるような場合等は、適宜数値の方を文字列型の値に自動変換した上で本来の演算を行う……ということかと。重複になるが。

2020/09/17(木) 04:55:16.59

数値を文字に変換するなら CSTR がある

2020/09/17(木) 07:47:55.65

>>267
法則を暗記するからそうなる。
法則の仕組みを覚えるんだ。

2020/09/17(木) 11:44:40.11

同じフォルダにある"〇〇"というＣＳＶを開いて、そのシートの最終行を取得したいのですが
「インデックスが有効範囲にありません」というメッセージでとまります

Sub salegrp()
Workbooks.Open ThisWorkbook.Path & "\〇〇", ReadOnly:=False
MsgBox Sheets("〇〇").Cells(Rows.Count, 1).End(xlUp).Row
End Sub

他のブックの最終行を表示する方法を教えてください
（Ａ列には最終行まで値が入っています）

2020/09/17(木) 11:54:47.91

csv開いたてからActiveWork ook.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Rowすれば

2020/09/17(木) 12:14:37.64

>>276
必要なのは最終行に入ってるデータの内容？行数を知りたいだけ？
全データをワークシートに入れる必要がなければテキストファイルとして開いた方が早いかも

2020/09/17(木) 12:24:52.75

Workbooks.Openの返り値をとってそれを操作する

なおRows.Countも省略せずにシート指定する

2020/09/17(木) 13:03:15.94

>>277
できました。

2020/09/17(木) 16:10:59.23

>>267
Sub tessstttt()
Dim str1 As String
Dim str2 As String

str1 = "あいうえお"
str2 = "かきくけこ"

MsgBox str1 & str2 '結果「あいうえおかきくけこ」

MsgBox str1 & "かきくけこ" '結果「あいうえおかきくけこ」

MsgBox "あいうえお" & "かきくけこ" '結果「あいうえおかきくけこ」
End Sub

2020/09/17(木) 16:17:02.54

>>279
↑
VBA続けるつもりならこっちでやれるようにした方が

2020/09/17(木) 16:44:05.17

他のbookやsheet絡むととたんに冗長になるんだよなぁ
Range(.Cell(a,b))とか .Cellの前にもブックやシート指定しないとならんし

2020/09/17(木) 17:02:14.36

>>283
むしろ1シートだけしか使わない場合でも、実効中に人がシート変更とかしても大丈夫なようにブック明記した方がいい

2020/09/17(木) 17:07:16.63

ブックとシート明示してworksheetオブジェクトの変数作ってそれ経由でアクセスさせるのが鉄板かね～

2020/09/17(木) 17:41:26.80

csvの最終行だったらテキスト形式でEOFまで吸い上げてってやった方が動作時間的には良さそうな気がした

2020/09/17(木) 18:04:52.95

CSVの読み込みをしっかりやるならPowerQuery使おうみたいな話もあったりなかったり

2020/09/17(木) 18:51:11.07

>>285
てか、基本でしょ

2020/09/17(木) 18:59:48.75

>>288
鉄板と基本の違い教えて

2020/09/17(木) 19:13:46.34

シート明示してないコードほんと多い
よくやるわ

2020/09/17(木) 19:55:58.19

>>279
俺なんて、必ずBookから書くぜ。
WithとSetを多用することになるが。

2020/09/17(木) 19:56:59.36

>>283
その例ならRangeの前にも"."が必要。

2020/09/17(木) 19:58:31.27

>>286
うん、CSVはLine Inputで1行ずつ+Splitで分解にすることが多い。

2020/09/17(木) 20:23:48.98

VBA覚えたての頃はいちいちアクティブシート切り替えてシート指定省いてたけど
Sheet1.Cells(a,b)って書く癖は絶対つけといた方が良いって今は思ってる
可読性が段違いだもん
暫く同じシートのセルをいじくりたおすならWithで目立つように囲ってやりゃあ良いし

2020/09/17(木) 20:32:12.03

>>286
項目中に改行含むcsvとかも存在するからなぁ
汎用的なcsvを真面目にパースするとかなり面倒
そしてEXCELは最強のCSVパーサ(笑)

2020/09/17(木) 20:38:51.87

なんつーレベルの高い話だよ
初心者が覚えようと思ってどんなもんかとスレを見たのが大間違いか

2020/09/17(木) 21:39:36.45

この先いつまで初心者を名乗るつもりか知らないがいちいちアピールしない方がいいよ

2020/09/17(木) 21:45:39.26

>>294
可読性以前に怖いんだよな。
アクティブシートが切り替わっちゃったらどうしようって。
実行中の操作とかで。
DoEvents挟まなきゃ絶対切り替わらないってならいいんだけど。

2020/09/17(木) 22:36:46.26

可読性も考慮するならオブジェクト変数に入れたりWithで囲んだり、頻出する構文なら別Functionに飛ばすという手もある
めんどくさいけどバグを出すよりはマシ

2020/09/18(金) 01:50:57.23

そもそもオブジェクト指定のないCellsがいつでもActiveSheetを参照すると思うなよ

シートモジュールに書いたCellsはそのシートを参照するんだぜ

**デフォルトの名無しさん** (JP 0Hdf-iZ3/) · 2020/09/18(金) 10:32:05.02

VBAというよりVBSかもしれませんが
いろいろ試したのですが正規表現の(肯|否)定(先|後)読みに対応していないようなのですが認識に相違ありませんか？
また、擬似的に(肯|否)定(先|後)読みに対応させたコードを誰か公開していませんか？

2020/09/18(金) 14:09:52.14

>>301
多くのアプリで、正規表現はWindowsに標準で内蔵されてるDLLを使ってる
独自の仕様やバグがあるのは既知だから、それが気に入らない時は別のライブラリを見つけてくるしかない

2020/09/18(金) 17:55:04.61

VBSはスレ違いになるんだろうけどさー
今、仕事でそれで書かれたコード見てるんだよねー
VB.NetからVBAに戻るとそのインテリセンスの効かなさに辟易するけど
VBSで書かれたものはその比じゃないねー

参照もなーんも効かないところに持ってきて
インデントも正しく書いてないし
綺麗にスパゲティーコードになってるの見ると
殺意を覚えるわー

まぁVBS自体は同じファイルの中に
複数のクラスが書けたりするところは
VBAよりいいのかな？

2020/09/18(金) 20:53:29.32

見てるだけで殺意はないだろ
自分で分かりやすく直していけばいい

2020/09/18(金) 20:57:08.45

どんな人が書いたコードでも理解できるのがプロ

2020/09/18(金) 20:57:17.16

C列に"2007/3/26"などのような書式で年月日が入っていますが
それをＣ＝年、Ｄ列＝月、Ｅ列＝日にしたいのですが

↓だと年が１９０５,月が１、日が２６日のように変換されてしまいます。

Dim r As Long, dt As date
For r = 2 To 435
dt = CDate(cells(r,3))
Cells(r, 3) = Year(dt)
Cells(r, 4) = Month(dt)
Cells(r, 5) = Day(dt)
Next r

2020/09/18(金) 21:01:25.47

>>306
最初date型で取ってやれば確か年や月や日を取る関数が有ったと思うよー
じゃあねー

**デフォルトの名無しさん** (JP 0Hdf-iZ3/) · 2020/09/18(金) 21:05:10.29

>>302
私が気に入りそうなものはありますか？
肯定先読み肯定後読み以上に高度な機能は求めていないのですが

2020/09/18(金) 21:10:13.79

ていうかそうしてんじゃん
出力先のセルの書式はどうなってるの？
コードを見た感じじゃ少なくとも3列目は
日付型みたいだけど

2020/09/18(金) 21:14:07.50

>>306
year、month、dayの引数のdtの中身は2007/3/26ではない

2020/09/18(金) 21:16:37.69

または3、4、5列目の表示形式をそれぞれy、m、dにしてる

2020/09/18(金) 21:29:41.49

>>306
考えなしに書くからセンス皆無なコードだけど3列目の日付が文字列で入ってるならこんなんどうよ

Dim Ary(2 to 435,1 to 3) As Long,r As Long, a As String
For LBound(Ary,1) to UBound(Ary,1)
a = Sheet1.Cells(r,3).Value
Ary(r,0) = Split(a,“/“)(0)
Ary(r,1) = Split(a,“/“)(1)
Ary(r,2) = Split(a,“/“)(2)
Next

Range(Sheet1.Cells(2,3),Sheet1.Сells(435,5)) = Ary

2020/09/18(金) 22:40:09.47

>>306
Sub Macro1()
　　Range("C2:C435").Copy
　　Range("D2:E435").Select
　　ActiveSheet.Paste
　　Range("C2:C435").NumberFormatLocal = "yyyy"
　　Range("D2:D435").NumberFormatLocal = "m"
　　Range("E2:E435").NumberFormatLocal = "d"
End Sub

2020/09/18(金) 22:48:07.19

>>313
くそこーどを貼るのはやめてあげて

2020/09/18(金) 23:02:36.64

>>306
セルに表示されている文字とセルの値は別だって理解が必要

おそらく
C列の表示形式が日付になっているが、そこの値を数値の2007にした
数値の2007は日付にすると1905/6/29　その年だけが表示されている
同様に数値の3は1900/1/3、数値の26は1900/1/26

まあ、想定とは違うかもしれんが正しく動いてると思われるな
解決策は、表示したいのは数値なのか日付なのかちゃんと決めて然るべき値と書式を設定しろ

2020/09/18(金) 23:05:29.99

つヵ、C列の値でC列に書き換えるとかしないで
元の値を別の列にもっとけば、VBAなしで関数だけで出来るだろうけど

2020/09/19(土) 04:14:23.53

C,D,E列に表示させたいのは「数値」であるにも関わらず
「日付」の表示形式になっているからおかしくなる

C,D,E列の書式設定→表示形式を日付から標準にする
こうするとC列は39167のような形で表示されることになるが
お構いなしにマクロを動かせば、意図した通りの動きになる

でどう？

2020/09/19(土) 07:38:40.10

>>305
理解出来るからこそ腹立たしいこともあるよー
例えばaとbとcってBooleanの変数を定義して
a = b = c とか書いてるやつ

まぁbとc が同じならaにTrueが、
違えばFalseが入るってことを期待して
書いてるなら見づらいけどまだ許せるよ
でもcにbが代入されてそのままaにbが
代入されるとか思って書いてたらしく
システムバグらせてそのままにしてたりとか
テストどうしてたんだとか思うよねー

正直こんなのに関わりたくないけど
仕事だからやるって感じだねー

2020/09/19(土) 07:43:05.72

あ、逆だった
bにcが代入されてaにbが代入されると
思ってたらしいってことね

まぁこの4日間は連休だし
そうじゃなくてもテレワークだから
気楽に適当にやるつもりだよー

2020/09/19(土) 07:51:21.74

仕事の内容でそのソースを直せってことなら書き直せばいいんじゃねーの
そのまま残すと後任者に同じように思われるんだぜ

**257** (ﾜｯﾁｮｲ bf90-W2Jb) · 2020/09/19(土) 10:25:54.04

>>259
遅くなってしまいましてごめん。自分の欲しかった情報がこれで取れる
事ができました。

ありがと！