Excel VBA 質問スレ Part64

2019/12/02(月) 23:43:54.18

!extend:checked:vvvvv:1000:512

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

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

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

2020/01/20(月) 08:18:58.19

お手間をかけます

セルの値は00～FFの範囲で　セルの見た目そのままをバイナリに落とし込むことを目的としています

例えば書いていただいた　>>360　では
セルの値が　01　だと書き出したほうは　01　00　となるので　
後ろの　00　は省いて書き込みたいと考えています

2020/01/20(月) 11:15:52.64

>>361
それなら最初の宣言を
Dim a As Byte
に戻すだけ

2020/01/20(月) 12:30:47.41

ああ、出来ました！
i の範囲もLongにしたら数十万も対応できたので思っていたのが完成しました

長々と丁寧にお付き合いいただき、ありがとうございます！

2020/01/21(火) 16:05:59.09

>>363
エクセルの最後までいくとえらーでるんじゃね

2020/01/21(火) 18:50:01.59

実行するとめっちゃ重くなり、一度エラーがありましたが、
その時以外は、成功しています

2020/01/21(火) 18:53:25.76

話しは変わりますが、”Microsoft Office Desktop Apps” と表示されてる方がデスクトップ版じゃなくてストアアプリ版だって本当ですか？

2020/01/22(水) 18:27:54.50

一つのシート上の複数セルに関数が設定されています。

すべてのセルの関数の処理が一つ一つ処理されていき、最終的にすべてのセルの更新処理が終わったときに、あるマクロを動かしたいのですが、どうやればいいのでしょうか？

2020/01/22(水) 18:51:55.87

もはやExcel大喜利だな。

2020/01/22(水) 19:00:23.17

ここには北海道のスーパーハゲザーはこないのー？

2020/01/22(水) 19:20:56.10

>>367
セルの関数を指定のタイミングと順番で処理したいってこと？
関数の入ったシートのEnableCalculationプロパティをFalseにして、
計算したい関数の入ったRangeのCalculateメソッドを順次呼べば一個ずつ処理できる
それで最後の関数の計算が済んだ後にマクロを実行するとか

一応関数の計算終了時に動かすマクロはシートのCalculateイベントに書いても実行できるけど、
シート全体が対象だからCalculateメソッドが走るたびに発火するから使えないと思う

2020/01/22(水) 21:36:24.98

>>370
ありがとう
試してみます

自分で作った関数を引数変えて500セルくらいが呼び出す状態なんです。
んで、かなり遅くて、全部が終わるまで5分位待つことになるので、それではつかいものにならないので、高速化を図りたいのです。

2020/01/22(水) 21:45:06.72

そろそろ python とか考えた方がいいデータ量だな

2020/01/22(水) 22:07:39.73

>>371
そういうのは、いちいちワークシートとか数式を使わずに、全部配列で処理すれば5秒で終わるよ

2020/01/22(水) 23:41:11.62

そう思っていた時代がありました。
セルに数式とかダセェぜ、これからはSQL

2020/01/22(水) 23:42:00.02

だ、とか思って、DSUMのあまりの遅さに撃沈。

2020/01/23(木) 04:03:57.39

500セルに対するETL処理だからPowerQueryでインメモリ処理すれば良いのでは

2020/01/23(木) 12:47:14.66

>>375
DSUMなんて使うからだろw
というか、Dの付く奴は手軽だけど使わないのが基本じゃ無いの?

2020/01/23(木) 12:48:15.57

というか、普通は>>373だろ。

2020/01/23(木) 23:18:33.64

dsumよりサブクエリの方が速いのが納得いかん。
あんな遅いの、何であるんだ?
俺の使い方が下手なだけで、速く使える人もいるのか?

2020/01/24(金) 07:02:09.49

>>379
db関数はAccessから持ってきただけのなんちゃって関数だと思っている
実装後にブラッシュアップでも考えていたのかもしれないが、今となってはもう・・・

2020/01/24(金) 10:44:07.60

あなたのハゲにサクセスしたい

2020/01/25(土) 05:46:11.97

新聞配達しながらプログラミングのこと考えてたんだが
オブジェクト指向こそプログラミング的発想の1丁目1番地で
オブジェクト指向が解らないやつは3流のクズってことで結論出ました。

2020/01/25(土) 07:10:00.59

>>382
高評価

・プログラミングできるのに新聞配達？何も分かっていないだろうと思わせる
・コンビニ店員ではなく、新聞配達というというのも良いチョイス。考え事ができそうなイメージがある
・ただ惜しいのは「プログラミング的発想」。プログラミング的発想が原初のプログラミングを指すならさすがに手続き型になる
・恐らく元は別のコピペであることは容易に推測できる

コピペにしろ、なかなか良いチョイスではあるが、詰めが甘い

2020/01/26(日) 10:47:31.88

転記元の最終行を取得するところで1004エラーがでる

Dim fl As String
Dim WBo, Wbd As Workbook
Dim a As String
Dim ex As String
Dim syamei As String

Sub 変換ファイル選択()
syamei = Range("B3")
fl = Application.GetOpenFilename
ThisWorkbook.Worksheets(1).Range("C1") = fl
Set WBo = Workbooks.Open(fl)
MsgBox "出力先を選択"

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
a = .SelectedItems(1)
End With
With ThisWorkbook.Worksheets(1)
ex = a & "\" & .Range("B3") & "_" & Year(Now) & "_" & Month(Now) & ".xlsx"
.Range("C2") = ex
.Activate
End With
Set Wbd = Workbooks.Add
Wbd.SaveAs ex
ThisWorkbook.Worksheets(2).Activate
ActiveSheet.Copy Wbd.Worksheets(1)
ThisWorkbook.Worksheets(1).Activate
End Sub

2020/01/26(日) 10:47:38.48

Sub 変換処理()

Dim Wsd As Worksheet '請求書
Dim Wso As Worksheet '転記元
Dim i As Long 'ForNext用
Dim j As Long
Dim ko As Long '個数
Dim Kohani As Range
Dim fnkohani As Range
Set Wsd = Wbd.Worksheets(1)
Set Wso = WBo.Worksheets(1)
Const Dhin = 1
Const Dtan = 2
Const Dko = 3
Const Dkin = 4
'転記元定数
Const Ohin = 4
Const Otan = 3

Wsd.Activate
With Wso
Range("E3") = Year(Now) & "/" & Month(Now)
j = 7
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If .Cells(i, 1) = syamei Then
MsgBox syamei
End If
Next i
End With
End Sub

2020/01/26(日) 10:48:01.26

for i = のところね、なんで？

2020/01/26(日) 11:12:02.47

>>384
もうちょっとテストしやすい形にしてくれ

>>384　最期から三行目ぐらいでエラー。原因は多分ファイルの構成かなんかだろう
>>385　エラーにならない

エラーになるファイルか、スクリーンショットでも貼ってくれ

2020/01/26(日) 11:21:15.83

>>386
たぶんこうじゃないかな
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row

2020/01/26(日) 11:33:50.54

Excel VBA初心者です。
やりたいことに似ているサンプルを読み解きながら
勉強中です。
初歩的なことで申し訳ないのですが、ご教示いただければ
ありがたいです。
クローズメソッドの説明を
　https://www.tipsfound.com/vba/11006
読んででいたのですが、説明文で
　Call wb.Close(SaveChanges:=True) ' 変更を保存して閉じる
　Call wb.Close(SaveChanges:=False) ' 保存しないで閉じる
というのが出てきました。
Call と付いている意味がわからず、call について調べてみたの
ですが、プロシージャの呼び出しについての説明しか見つけられず
困っています。
どうぞよろしくお願いいたします。

　

2020/01/26(日) 12:13:28.33

>>389
Callは引数に指定したプロジージャに制御を渡して呼び出すVB6のステートメントで使用するキーワードだよ
CallキーワードはVBAの文法上省略可能
Callを省略しない場合、呼び出すステートメントに渡す引数__ argumentlistは括弧括り・カンマ区切りの形式にしないといけない

つまり以下の二つの命令はどちらもCallステートメントで、同じ動作・効果をする

wb.Close SaveChanges:=False

Call wb.Close(SaveChanges:=False)

2020/01/26(日) 12:37:50.96

エクセル以外はインストール出来ないって特殊会社にこき使われてる社畜は無視してその他の人はVBAなんて覚える価値は全くないから騙されるなよ
エクセルデータを読み込んで加工するなんて簡単な事

2020/01/26(日) 12:49:58.68

場合によりけりだろう
Excelの読み込みができる言語ならエクセルデータの加工が可能なのは事実だが、VBAが手軽なのは間違いない
今やPower Queryの操作もVBAで行えるから、VBAを呼び出し元にして複雑なデータ加工を高速で行うことも可能になってる

2020/01/26(日) 13:53:16.01

>>389
call つかうと引数の括弧が必須になるんだよ
使わないときは括弧つけるとエラー
そんで変数＝関数(引数)　みたいに返り値を取る場合も括弧が必須になる
つまりcall 使うと括弧有無の記述を統一できるんだね

2020/01/26(日) 15:15:52.15

VBAでのPower Query操作ってクソだよね
letからinまでのステップ全体がFormulaプロパティに長い文字列として収まってるだけ
vbaの貧弱な文字列操作で弄りたくない

2020/01/26(日) 15:46:06.33

>>391
>>392
まず、基本的に対象が違う。
Excel以外はExcelブックとの連携であり、Excelファイル操作が主題。

ExcelではExcelを開いた状態で、Excel上での作業を効率化するもの。
従って基本的に競合しない。

何故、Excelファイルが使われるかと言えば、Excel上で何らかの作業をする為であり、その作業が仕事に大きな意味を持つから。
その大きな意味によってExcelというアプリケーションが使用されている以上、VBAの優位性は動かない。

2020/01/26(日) 15:49:42.04

データの共有や統合が出来ないのがエクセルの欠点

2020/01/26(日) 17:01:12.87

できるけど、もう使わないだろう

2020/01/26(日) 18:03:51.13

あなたのハゲにサクセスしたい

2020/01/26(日) 19:14:52.64

>>390
>>393
わかりやすいご説明ありがとうございました。
もっと勉強して、みなさんの会話に混ざれるように
なりたいです。
職場でExcelのひな形をコピーして、顧客リストの顧客コードを
シートの指定のセルに書き込み、Book名にも付加して、名前を付けて
保存、というのを2000個くらい毎年度初めに作るのが若手のお仕事よ！
と仰せつかり、歴代の先輩方は手分けして「手作業で」やっていたと聞き、
「いやいやいや、何時代よ！」と勉強を始めました。

2020/01/26(日) 19:24:52.26

>>399
ここ煽りやアンチばっかだからもっとマシな場所に混ざりに行ったほうがいいよ

2020/01/26(日) 22:14:31.23

>>394
長くなるかどうかは書き方次第だろう
Let In構文はMの文法上必須ではないので、変数を極力使わずLispのような記法で短くまとめても良い

2020/01/27(月) 00:51:05.27

>>399
馬鹿しかいない職場か？

2020/01/27(月) 08:07:38.79

>>399
コーディング含めて1時間あれば出来そう

けど便利屋になるのだけは避けた方がいいぞ。
今後そういう処理は全部お願いってなって、雑用みたいな仕事ばっかり来るはめになる。
プログラマーは評価されにくく、いいように使われやすいから。

コードをしっかり管理して、そうならないようにな！

2020/01/27(月) 08:22:04.82

アホな職場上司もとい老害同僚からは
仕事減るから余計な事するな
と言われる可能性も

2020/01/27(月) 12:05:43.13

出る杭は

2020/01/27(月) 12:31:00.54

まじでそういう馬鹿上司いそうで困るな
しかも外のセミナーで自動化で人件費コストカットとか吹き込まれたら躊躇なくクビ切ってきそう

2020/01/27(月) 19:27:17.58

シート上に配置したコントロール（ボタンなど）の名前を調べたり変更したりするには
どうすればよいのでしょうか

シートにたくさん画像が貼り付けてあるので、それらを一斉に消去するために

Public Sub pics_delete(sname As String)
Dim delShape As Shape
For Each delShape In Worksheets(sname).Shapes
If (delShape.Name <> "btnDelete") Then
'delShape.name = btnDelete
delShape.Delete
End If
Next
End Sub

こんな感じのコードを書いているのですが、配置したボタンの名前をワークシート上で
参照／変更することが出来ません
仕方ないので、一旦全てのシェイプが消えたあと再度ボタンを配置して、上記プログラムの
コメント部分を切り替えています

プロパティを見ても、オブジェクトとしてワークシートが選択されてしまうようで、ボタンを
選択した状態でプロパティを見てもボタンの情報が参照できません

2020/01/27(月) 19:50:12.48

>>407
shapesは図形
コントロールはフォームコントロールかActiveXコントロールで扱い方違う

2020/01/27(月) 20:35:24.91

>>408
あ、すいません
プログラム上ではなくて、excelの画面上でボタンの名前を確認、変更したいのです
ちなみにボタンは「開発」タブの「コントロール」で「挿入」から選べるものを使ってます

2020/01/27(月) 20:37:54.19

>>409
だから、フォームコントロールとActiveXコントロールのどっちなの

2020/01/27(月) 21:01:21.61

>>410
失礼、フォームコントロールでした

2020/01/28(火) 07:51:36.53

>>411
名前を調べるなんて煩瑣な処理にしなくても、フォームコントロールだけを消さずに画像だけを消したいなら下のようにやれば良いのでは

Public Sub pics_delete(sname As String)
Dim delShape As Shape
For Each delShape In Worksheets(sname).Shapes
With delShape
If .Type <> msoFormControl Then
If .Type = msoPicture Then .Delete
End If
End With
Next delShape
End Sub

delShape.Typeで取得できる図形タイプを表す定数値がMsoShapeType.msoFormControlだったらフォームコントロール、MsoShapeType.msoPictureだったら画像だと判断するようにする
他にも消したいタイプの図形があるなら削除判定の箇所で任意のMsoShapeType列挙体に当てはまるかType属性を持っているかどうかを判定させたら良い

2020/01/28(火) 09:01:31.91

>>411
ホームタブ右端の検索と選択→オブジェクトの選択と表示
からシート内オブジェクトリスト出てくるけどこれが名前だったかな

2020/01/28(火) 09:48:07.01

終わりの鐘は鳴り響いた
https://ascii.jp/elem/000/004/000/4000725/

2020/01/28(火) 10:02:58.52

あとは新しいVBAウィルスが出てセキュリティ業者が騒いだらVBAは本当に終わりだな

2020/01/28(火) 10:46:16.03

>>414
それ前にも貼られたけど、今までのとの違いが分からんのだが

2020/01/28(火) 15:16:34.29

>>414
VBAでもできる範疇なら全く覚える気起こらん

2020/01/28(火) 17:56:01.82

覚えてないことはできるかどうかも判断できない

2020/01/28(火) 18:12:38.87

>>412
本来はこれがやりたかった、shapeのプロパティをちゃんと調べていなかったので
出来なかった処理でした
他にも似たような処理があるので、コード使わせていただきます

>>413
私の質問に対する回答は、これが求めていたものでした
開発タブしか見ていなかったんですが、ホームタブでいけたんですね

お二人共ありがとうございました

2020/01/28(火) 18:28:16.91

>>417
そうか？
俺はVBAの代わりにTypeScript使えるなら乗換えるよ

2020/01/29(水) 10:51:07.25

>>420
どの程度使えるかだな。
全く同じことができるなら乗り換えるだろうな。

2020/01/29(水) 20:53:08.43

まったく同じことしかできないなら乗り換える要素がない

2020/01/29(水) 21:53:26.65

全く同じことができるなら、新規に憶えるだけじゃん。
それはこっちの労力だけで済む。
今後移行していくとすればその程度大した問題じゃない。

今まで他言語では出来なかったり、不便だったからVBAを使っていたわけでね。

2020/01/29(水) 22:24:20.41

>>422
同じことが楽にできるなら乗り換える理由になるでしょ？

2020/01/29(水) 23:05:27.98

今までと同じことをするだけなら、今までに書いたコードをそのまま使い回すのが一番楽だよな
わざわざ書き直す手間はだれが負担するんだ

2020/01/29(水) 23:12:48.36

>>425
いうてVBAなんて実質的には大したことやってないのが大半だろ
資産価値なんかゼロだよゼロ
コーダーの能力が低すぎて驚くほど冗長なコードになってるのは多いけど

2020/01/29(水) 23:27:08.67

sheet("sheet1").select！
さよか～

個人的にはjQueryが一番嬉しい
あれ、記述が超絶楽ちんなんだよ。cssセレクタとかExcel vbaと感覚的にかなり似てる部分があると思う
$("sheets!").cell(1,2).backgroundcolor("red")
とかね
と書きながら思ったけどこれ別に言語買える必要ないな

googleのapps scriptは冗長でかなりめんどくさい　
強制的にアドイン扱いだしタイプが長い

2020/01/30(木) 01:32:32.11

>>425
書き直す必要は無いでしょ。
これからの話。

2020/01/30(木) 01:33:34.48

>>426
君のコードは大したことやってないの間違いじゃね?

2020/01/30(木) 08:06:07.77

ぶったけインテリセンス次第なんだよなぁ
javascriptでインテリセンスが優秀なら移らない理由ないし
インテリセンスが不自由ならVBAやめないし

2020/01/30(木) 08:13:50.21

>>429
VBAに大したことやらせなきゃいけないなら設計ミスだよ

2020/01/30(木) 18:17:30.68

VBAで大したことやっちゃいかんでしょ

2020/01/30(木) 18:33:44.35

自分の仕事の範囲で統計解析とかやるけどな

2020/01/30(木) 18:53:21.09

それがPythonだったら同じことやってても倍の年収貰えるのにもったいない

2020/01/31(金) 00:09:52.88

jQueryももう大分レガシー扱いだぞ

2020/01/31(金) 02:51:37.91

Pythonでも無能は収入低いぞ。

2020/01/31(金) 02:57:44.63

VBAのセキュリティは大問題
まともな会社は使用禁止
あんたの会社は禁止されてるよね？

2020/01/31(金) 03:12:26.11

今まで、銀行、通信大手D社、保険、金融で働いたが禁止になってる所なんて無かったがね。
寧ろ、他アプリのインストールは基本禁止だったけど。

2020/01/31(金) 03:14:49.50

そういやあ、某電気メーカーとかも2ヶ所有るけどどっちも禁止じゃ無かったな。

◆QZaw55cn4c (ﾜｯﾁｮｲ 4a47-Mlsf) · 2020/01/31(金) 19:54:00.67

>>437
VBA にどんなリスクがあるというのですか？

2020/01/31(金) 20:31:33.67

アスタリスク

2020/01/31(金) 20:37:16.44

フリスク

2020/01/31(金) 20:38:06.28

バジリスク

2020/01/31(金) 20:58:33.01

VBAはブックを開いただけで何でもやりたい放題で、脆弱性とかそういう以前の問題なの
例えるなら、訪問者に対してインターホン越しに応対するなどの身の安全を守る手段が何もなく、
無視するか家に上げるかの選択肢しかない

2020/01/31(金) 21:01:57.72

古いExcelしか使ったことないんだろうね

◆QZaw55cn4c (ﾜｯﾁｮｲ 4a47-Mlsf) · 2020/01/31(金) 21:05:51.22

>>444
その例えは例えになっていないと思いますよ
具体的にどういう危険性があるのか、それがごく普通の Windows アプリ　by win32api & MFC と比べてどのように危険になったのか、それを示していただけませんかね

2020/01/31(金) 21:11:45.14

>>444
そんなのアプリも一緒じゃん
バカなの？
〉無視するか家に上げるかの選択肢しかない
あぁ、真正バカですね

2020/01/31(金) 23:14:09.09

俺も知らんが、Excel脆弱性でググると色々出てくるってことは、そういうことなんじゃないの?

2020/01/31(金) 23:17:05.34

シート1のA行を1列目から右にセルに入ってる文字を配列に順番に入れるにはどうしたらいいですか？

2020/01/31(金) 23:23:17.84

　1　2　3
A
B
C

こ、こうか?
画面が横になってないかそれ。

2020/01/31(金) 23:34:43.97

>>449
WorksheetFunction.Index(取得したい範囲or取得した二次元配列,1)

2020/01/31(金) 23:35:50.05

>>449
dim ary as variant
ary = シート1.range(A行1列目から右のセル).value

2020/01/31(金) 23:42:44.02

それだと二次元配列になって使い勝手悪くないか？
イテレータで回すならいいんだろうけど・・・

2020/01/31(金) 23:57:41.94

ミス
WorksheetFunction.Index(取得した二次元配列,1,0)
だった

2020/02/01(土) 00:00:37.73

>>453
「A行1列目から右」が一次元か二次元か判断付かん

2020/02/01(土) 00:45:50.64

>>449
Excelでは、縦の並びが列、横の並びが行
普通はA列1行目みたいな表現になる

2020/02/01(土) 01:30:43.62

配列って言われたら普通はVBAの話だと思うが、実はシート関数の話をしているんじゃないか?

2020/02/01(土) 11:40:24.75

>>446
まあ、マクロウィルスは作成が簡単というのはあるな。
VBAのプログラムを作成するプログラムが簡単に作成出来るので。

但し、Excelもそれを封じる為にセキュリティーセンター内で、そういうプログラムの実行をするための設定としての「VBA プロジェクトオブジェクトモデルへのアクセスを信頼する」のチェックがデフォルトで外れている。
このチェックを付けて運用するのは危険だな。

でもここでチェックが付いてるとVBAが無くても勝手にVBA組まれちゃうんで、VBAの有る無しにかかわらずExcel自体が危険だな。
従って>>444のVBAの危険には該当しない。

>>444の言う危険って何だろうな?
所詮、よく知らない奴が知ったかぶりで語ってるだけ?

2020/02/01(土) 11:46:35.95

他の言語で業務を仕組化するって要は自分の価値を切り売りしてるようなもんだからな。
自分への属人性を排除して誰でも自分と同じ作業ができるようにする。
そんなんで金もらっても用がなくなれば切られるだけ。一時的に給料が上がるだけで自分にとって何のメリットもない。
それよりVBAで自分の仕事をブラックボックス化して属人化しといたほうが得。

2020/02/01(土) 12:24:16.95

>>459
転職後にそういうカスを追い詰めるのが趣味です
徹夜でプログラム読み切ってバグとミスを指摘し続けて「え、ここじゃないんですか？みた瞬間にわかりますけど・・（大嘘）」
で大概の人はイライラMAXでデバッグが手につかなくなるｗざっこｗｗｗ