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/06(水) 00:29:35.50

>>655
既存のウインドウを触るのは初心者にとっては結構大事だよ
閉じれてないのはQuitメソッドの前に次の処理が入ってしまってるだけだと思うから、
処理が終わったと思われる場所に自分でコードを挿入したらどうだろう

2019/03/06(水) 00:33:49.27

>>654
改行が含まれてるセルは""で括られてるという認識でいいなら、
丸ごと文字列として読み込んだ後に正規表現で""内の改行を全部改行コードに書き換えるのが良いと思う

2019/03/06(水) 00:36:57.21

>>657訂正
改行を全部改行コードに書き換える
↓
改行コードを全部書き換える

2019/03/06(水) 00:54:59.22

>>657
試してないので想像ですが1ファイル数mb程あるので一括読み込みで取得出来るんでしょうか…？
変数に入るのか、pc動作的にどうなのかが気になりますが…一度試してみます

2019/03/06(水) 01:00:18.09

>>654
replace(str,vbcrlf,"うんこ")

2019/03/06(水) 01:08:03.42

>>659
文字列型に格納すればいいと思います
確か数Gbぐらい格納できたはず

**デフォルトの名無しさん** (JP 0H4f-j5yB) · 2019/03/06(水) 07:35:28.45

IEってBusy以外にステータスも見てないとループ抜けちゃわなかったっけ

2019/03/06(水) 07:51:07.24

抜けた気がする
要素がありませんってよく怒られた

2019/03/06(水) 08:14:30.41

両方見てても抜ける時が有る。
滅多に無いけど。

例えばClickで遷移する場合に、抜けてからBusyになることがあった。
Submitに比べてClickは単純に文字通りClickするだけなので遷移しないページも有るから必ずBusyになると想定されていないと想像してる。
それ以降Clickの時は最初にSleepを入れるようにしてる。

2019/03/06(水) 08:20:35.12

>>654
別に方法は色々あると思うけど1行ずつ読み込んでも良いんじゃない?
1行ずつでも条件に合うまで1レコードとしなければ良い。
1レコードの最後にも改行は有るんだろ。

もし1行ずつ読むなら1レコード内に複数改行が有る可能性を考慮すること。

2019/03/06(水) 08:57:22.69

>>654
Ruby では、列をダブルクオートで囲むと、その中にカンマや改行を含める事もできる。
他の仕様も含め、詳しくはRFC4180を参照

require 'csv'

p CSV.parse_line( "a,\"あ\nい\"" )
# => ["a", "あ\nい"]

\n は改行

列区切りはカンマで、外側の"～" は文字列を表すための囲み。
内側の\"～\" は、"～"内での"～"を表すため、\ でエスケープしている。列囲みを表す

a の部分は、\"～\"で囲まなくてもよい

2019/03/06(水) 09:17:24.17

rubyって・・・
もう完全に終わってる言語なんだからそんなもの勧めないでよ

2019/03/06(水) 09:47:16.43

Twitterで紹介されてたこれ一度試してみたけど機能してたよ

https://www.excelspeedup.com/readcsv/

2019/03/06(水) 11:28:25.04

下記リンク先のページ下部にあるユーザ定義関数がVBAとして正しく書けてるかチェックして貰えませんか
https://peltiertech.com/loess-smoothing-in-excel/

2019/03/06(水) 11:39:12.31

>>669
どういう計算をしてるかは知らんけど、コード上は問題ない

2019/03/06(水) 15:05:39.78

多分スレチだろうと思いますが、>>669をLibreoffice最新版に実装したところ戻り値に=LOESS(x,y,z,n)で指定したyの配列がそのまま表示されたのでコードどこか間違ってるか仕様変更したのを俺が見落としてるのかなと思って聞きました
ありがとうございました

2019/03/06(水) 15:12:15.84

>>666
市ね

2019/03/06(水) 21:16:11.63

指定した列以外の列を削除する方法を模索しています。
たとえば、ABCDE列の１行目にそれぞれ

あ　い　う　え　お

と入っていて、
"あ"と"う"と"お"以外の列を削除し、

あ　う　お

というよにしたく、
このようなプログラミングをしたのですが意図するように動作しません。
ド素人と罵られること承知で質問に参りました。
論理式自体考え方がおかしいものとも思っています。
どなたかお力添えいただけないでしょうか？

Sub 不要列削除()
Dim i As Long
For i = 1 To 50
If Cells(1, i).Value <> "あ" Or Cells(1, i).Value <> "う" Or Cells(1, i).Value <> "お" Then
Columns(i).Delete
End If
Next
End Sub

2019/03/06(水) 21:30:16.04

>>673
ifの条件がおかしい

2019/03/06(水) 21:31:48.30

表示形式が混在したセルを見た目通りのまま表示形式が文字列の別のセルにコピーするいい方法ありませんか
例えばA1～A3を、この見た目のまま表示形式が文字列のB1～B3にコピーしたい場合
　　A
1　$1,000
2　2019/1/1
3　9:30

①
　Dim buf() As Variant
　buf = Range("A1:A3").Value
　Range("B1:B3").Formula = buf　'←そのまま入れてみる
B1～B3は 3/6/2019、0.836111111、\10,000.00 になる

②
　Dim buf() As Variant
　Dim buf2(1 to 3, 1 to 1) As String
　buf = Range("A1:A3").Value
　For i = 1 to 3
　　buf2(i, 1) = buf(i, 1)　'←文字列型にして入れてみる
　Next
　Range("B1:B3").Formula = buf2
B1～B3は 2019/03/06、0.836111111111111、10000 になる

①②どちらも書式が勝手に変わります
ちなみにセル範囲を配列に直接つっこまず、文字列型の配列に1セルずつ buf = Range(i, j).Text みたいな感じで入れてからセルに貼り付けると希望の結果になりますがめちゃくちゃ時間がかかります（大量データに使いたいので）
他にやり方があれば教えてください

**675** (ﾜｯﾁｮｲ 377c-u5eM) · 2019/03/06(水) 21:34:55.08

例のA列は↓の間違いです、すいません
1　2019/3/6
2　20:04
3　\10,000

2019/03/06(水) 21:37:03.61

画面表示を止めても遅い？

2019/03/06(水) 21:54:06.80

>>673
列インデックスで加算ループをしている途中で列削除をしてるから、削除した回数分だけ参照参照列の位置がおかしくなるのでは
例えば2列目を削除した後にループ処理の最初の処理に戻ると、次にi=3として.Cells(1,3)とセルアクセスしたときに見ることになるのは本来D1に位置していたセルになる
列削除した分インデックスがずれるので

こうやって減算ループにするのが正解の一つかと

Sub 不要列削除()
Dim i As Long
For i = 5 To 1 Step -1
If Cells(1, i).Value <> "あ" Or Cells(1, i).Value <> "う" Or Cells(1, i).Value <> "お" Then
Columns(i).Delete
End If
Next
End Sub

あるいは、列削除した場合にはEnd Ifの手前でiから1を引いてインデックス調整するようにしたりか

2019/03/06(水) 21:56:07.90

>>673
改めて見たらIfの条件もおかしいね
And条件にしないと

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

馬鹿過ぎるのが問題

2019/03/06(水) 22:23:00.33

>>675
textプロパティ

Sub foo()
Range("c1").Value = Range("a1").Text
End Sub

範囲で代入は出来なかったので、適当にforeachで回してくれ
Range("c1:d2").Value = Range("a1:b2").Text

2019/03/06(水) 22:36:44.70

>>677
止めた状態で遅いです

>>681
セルに直接入力していくと更に遅くなると思います

2019/03/06(水) 22:56:49.85

CSVで書き出せばTEXTが得られるぞ

2019/03/06(水) 23:08:59.57

>>682
あぁtext使ったって書いてたね、ゴメン
配列でも無理なら高速化はどうだろうか
https://15g.jp/post-314/

高速化でも配列でも重いなら、
もうアクセス辺りに移行した方がいいかも

2019/03/06(水) 23:09:20.80

>>683
csvは勝手に変換されるのが鬼門すぎる

**675** (ﾜｯﾁｮｲ 377c-2g28) · 2019/03/06(水) 23:48:56.32

やっぱりTextプロパティ使う以外無理そうですかね…

2019/03/07(木) 00:01:59.48

後は>>683かな
住所の"1-1-1"みたいなものがなければ行けると思う。
後はダメ元でgoogleスプレッドシートに移植してマクロ組んで見るとか

2019/03/07(木) 00:20:00.91

>>686
textの取得だけループでまわして、
二次元配列に直して代入するとかは？

2019/03/07(木) 00:26:34.58

>>688
遅いって書いてあるだろ。もっと上をお望みなんだよ

2019/03/07(木) 00:44:39.67

>>689
直接入力が遅いって言ってるから、入力だけ一括でやれば？と思ったんだが

**675** (ﾜｯﾁｮｲ a37c-DZHv) · 2019/03/07(木) 00:46:43.69

>>687
他のファイルは使えない状況なんです…

>>688
その処理がすごく時間がかかるので他の手段を探していました

諦めます
今回の事でRangeのValueやFormulaみたいにTextも配列に入れられるようにしてくれと心から思いました
色々考えてくれたみなさんありがとうございました
もしいい方法思い付いたらいつでも待ってます

2019/03/07(木) 01:15:09.98

描写と計算止めてCopyメソッドで10万行×30列を列ごとにコピペしたら一瞬で終わったが・・・
っていうかこれ以上増やそうとするとリソース不足でエクセルが悲鳴を上げるし、どういうデータをコピーしたいんだろ

2019/03/07(木) 01:18:31.96

あ、書式変わるのか・・・上のレスは忘れてください

2019/03/07(木) 02:33:57.63

コピーしてテキストとして貼り付けじゃダメなのか？

2019/03/07(木) 02:58:39.40

早い遅いは個人の感覚でしかないとおもう

2019/03/07(木) 07:14:54.02

>>691
>>684 の Application.ScreenUpdating = False
は効かないのか。　画面描画が止められるよ。やってないから適当なこと言ってるかもしらんが
画面描画以外で時間がかかるってどんなだよ。　ちょっと想像できんがなあ
なんでだろう

2019/03/07(木) 07:24:21.94

>>691
valueで配列コピペした後に、セルに対してそれぞれ書式設定を手動もしくはマクロですれば？
書式は列ごとくらいは揃ってると思うし。
それもバラバラってことだと、そもそも集計の仕方が悪過ぎる。

2019/03/07(木) 07:28:16.03

>>674 >>678
ありがとうございます！
意図するようになりました。
-1で減らしていく発想は本当になかったのでいい勉強になりました。
このたびは本当にありかございました。

2019/03/07(木) 07:44:13.43

>>696
684じゃないけど、複雑に関数組んだりとか遅い関数が大量に入っててかつデータ数がエクセル上限近くで再計算に時間がかかるとか、あとセル上で反復計算やソルバー使ってたりするとある。
ただ684は書式がバラバラなセルを扱ってる時点で、複雑な計算をしてるとは思えないけど。

2019/03/07(木) 07:52:53.03

>>699
>>675をもう一度見て来いよ。　これだぞ。そんなややこしいことしている話ではないと思うが

>表示形式が混在したセルを見た目通りのまま表示形式が文字列の別のセルにコピーするいい方法ありませんか

**675** (ｽﾌｯ Sd32-tsl5) · 2019/03/07(木) 08:38:04.86

再描画等定番の部分は止めています
セル数は100万ぐらい、列数、行数分回してTextプロパティを1セルずつ配列に入れる処理だけです
シートへの貼り付け処理ではなくこの取得部分に時間がかかります
同じ回数回す②や、Format関数、worksheetfunction.textを使った場合その半分以下の時間ですが望みの結果を得る方法はわかりませんでした…

2019/03/07(木) 10:57:17.39

コピーした後NumberFormatで整えればええんちゃうん？
Dim buf() As Variant
buf = Range("A1:A3").Value
Range("B1:B3").Formula = buf
Range("b1").NumberFormat = "yyyy/m/d"
Range("b2").NumberFormat = "h:mm"
Range("b3").NumberFormat = "[$\-ja-JP]#,##0;-[$\-ja-JP]#,##0"

データが多すぎて無理？

2019/03/07(木) 10:58:23.63

アー、見た目が同じまま、「文字列」なのか・・・
すんません無視してください

2019/03/07(木) 11:39:04.19

>>675
A1:A3をコピーしてメモ帳とかに貼り付け
全選択してコピーしてB1:B3に貼り付けでできた
VBAだけで完結させる方法は知らん

2019/03/07(木) 12:41:45.56

勘違いしてる奴が多いが、自動計算を止めたり描画を止めるのは初心者が速度アップのためにやる方法で、>>675のように速度アップの為に考えて組まれている場合には効果が無い。

上級者はこういう場合に描画止めたりしないよ。
考えずに組んで面倒くさい場合にやる奴はいるかもしれんけど。

**675** (ｽﾌｯ Sd32-tsl5) · 2019/03/07(木) 12:42:12.66

>>702
あとからNumberformatを適用させるんでなく配列に入れる時点で適用させる手を考えたんですが、
適用させるのにworksheetfunction.textを使うと書式に「マイナス値は[赤]」みたいな色設定があるとなぜか1004エラーが出るし、Format関数使うと表示形式が標準の時なぜか値が「標準」という文字列になってしまいます
速度的にはtextプロパティ使うよりずっと速いんですが…もう訳がわからない

2019/03/07(木) 12:46:54.74

>>675
他に方法があるか探る時間が無いので何とも言えんが、DB処理にするか、マルチプロセスで上手いことすれば早くなると思う。

2019/03/07(木) 12:50:28.91

よく考えたらDB処理は駄目な気がする。
表示部分を扱え無さそうだ。

2019/03/07(木) 18:02:24.90

メモ帳にコピペすると書式が全部消えて見た目通りの文字列になる
メモ帳アプリの実体はエディットコントロールそのものだから、ユーザーフォームにエディットコントロールを貼っただけの作業場所を作って、そこ経由でコピペしてみたら？

2019/03/07(木) 18:03:18.10

UserForm1にTextBox1とCommandButton1、TextBox2とCommandButton2の計4つが配置され
UserForm2には入力フォームが配置されています。

CommandButton1からUserForm2を呼び出した時は、TextBox1に記入
CommandButton2からUserForm2を呼び出した時は、TextBox2に記入

させるには、どうすれば良いですか？

2019/03/07(木) 18:34:04.34

>>709
これ、本当?
だったらクリップボード弄って高速化できるかもしれない。

2019/03/07(木) 18:34:43.55

複雑な処理でデータ量が多いなら遅くても仕方ないんじゃね？
どうせエクセルだし、専門職じゃないし、大した会社じゃないし、給料安いし

2019/03/07(木) 19:03:35.19

>>710
やり方はいくつもある
一番簡単な方法を書くと、

Userform1側の各ボタンのクリックイベント内に、任意のセルや標準モジュール上のPublic変数等にコマンドボタンのオブジェクト名をコピーする処理を書く(Me.ActiveControl.Nameでフォーム上でフォーカスされているコントロールオブジェクトの名前を取れるのでこれを使う)

Userform2側のイベントコードではそのコピーされたボタン名を読み取って書き込み先のテキストボックスを判定するようにする

2019/03/07(木) 19:11:06.28

>>710
コマンドボタンのクリックイベントプロシージャで、グローバル変数に呼び出したボタン番号を格納→ユーザーフォーム2のコンストラクタでグローバル変数で分岐させる方法

もしくはコマンドボタンのクリックイベントプロシージャでユーザーフォーム2のマルチページコントロールのプロパティを変更する（value=1,2）方法もある

**675** (ﾜｯﾁｮｲ a37c-DZHv) · 2019/03/07(木) 21:34:32.55

セル範囲をコピーしてクリップボードから直接二次元配列に入れるようにしたら何十秒もかかってたのが0.5秒になりました
ただ、クリップボードから切り出す時にうっかりセルの値に手を加えてしまわないかが心配です
単純にVbCrLfで行を区切って、VbTabで列を区切ってるんですがそれで問題ないんでしょうか？

2019/03/07(木) 22:48:37.99

>>715
セル内改行を使ってなければそれで大丈夫

2019/03/08(金) 00:03:05.83

>>716
セル内改行入れてみたら…またこんな落とし穴が…

2019/03/08(金) 02:12:12.40

>>713
>>714
プロジェクトが大きくなるにつれグローバル変数は神経を使うので
極力さけたかったのですが
Form1.ActiveControl.Name、でいけそうですね

2019/03/08(金) 03:08:04.26

システムの大規模化が想定されるならユーザーフォームでユーザーフォームを呼び出すのは危険w
ユーザーフォーム間の依存性を高めてしまうw
ここはユーザーフォームを呼び出すクラスを作った方がいいw
クラスを介して情報を渡せば安全だし再利用性も高まるw

2019/03/08(金) 08:00:14.88

やっぱりuserformからuserform呼ぶのってよくないのか
こういうのってクラスモジュール使えるんだろうなって思ってたんだけど
余り使ったことなかたんで、ちゃんと勉強してみよう
参考書ぽちってきたわ
JavaとかC#を多少触ってきたからオブジェクト指向の概念は分かってるんだけど
VBAでの効果的な使い方をマスターしたいわ

2019/03/08(金) 08:01:46.46

引数付きコンストラクタすら簡単に実装できない言語で呼び出し用クラス作る方がきついと感じてしまうね、個人的には

2019/03/08(金) 08:08:56.71

普通のコンストラクタには引数渡せないんだ
まぁその辺の限界も含めて一度勉強しておいたほうがいいとは思ってる
できることと出来ないことを知った上で、クラスを使う選択肢を持つことができればいいかなって

2019/03/08(金) 08:15:50.67

VBAでクラスとか言ってるのはたいてい知ったかだからスルーしていい
VB.NETとかC#知ってたらストレスしかたまらん

2019/03/08(金) 08:23:55.15

そもそもVBAにクラスが欲しくなるような規模の要件を押し付けている時点で業務設計が破綻してる

2019/03/08(金) 08:24:18.53

こういう場合にクラス使うの?
何か違うと思うんだが。

VBAでも見た瞬間にクラス使おうと思う場合もあるんだが、そういうことは少ないな。>>323のような話なら見た瞬間にクラスだなと思うんだけどね。

2019/03/08(金) 08:43:43.11

>>725
ワークシート上でデータが常に単なる二次元の表として見えているために、
その行をエンティティとして見るような発想になりにくいんだろうね
別にそれが悪いとは思わないが

2019/03/08(金) 08:55:03.19

逆にいうと、ワークシート上で人間が無理なく扱えるようなデータ構造にならざるを得ないから、
生え抜きのVBerが作りがちな酷いDB設計になりにくい
そういう良い意味でデータ構造に無理が利かないので、クラスでデータ構造を抽象化したくなるケースが少ないのだと思われる

2019/03/08(金) 12:25:06.22

DBとして扱うならクラス使うことも考えるよ。
でも、DBとして扱うことも多くない。
DBならAccessにしちまうんで。

2019/03/08(金) 16:46:49.91

いまAccessを使う理由って何？
無料版のSQL Server のスペックの方が上だろう。
移行が大変だから？

2019/03/08(金) 16:53:49.99

エンジンだけで比べてる頭の不自由な人かな？

2019/03/08(金) 16:53:54.67

フォーム中心のまともなデータベースアプリが比較的簡単に作れるという点では意味がある
まあ今ならPowerApps使えばいいと思うけど

2019/03/08(金) 17:59:29.39

PowerApps使うならPowerShellかJScript使わないとな
まぁVBAは要らんわ

2019/03/08(金) 18:55:16.76

???
会社のPCに既にAccessがインストールされているのにもかかわらず、
Excelで扱うような程度のものをDBとして扱いたい時に、SQL Serverインストールはじめちゃう人?

全てにおいてセンスねーわ。

2019/03/08(金) 18:56:25.33

まだSQLiteの方が理解出来るわ。

2019/03/08(金) 19:18:32.09

>>733
正直君の意見はよくわからん
Management Studioすごく便利だよ
それにExcel からADOやODBCでつつく際のIFは大して変わらないんだからSQL Serverの方がパフォーマンスいいじゃん

2019/03/08(金) 19:24:33.31

うちの会社そういうの入れる時はそれを使う理由説明して了解得るのに手間がかかる

2019/03/08(金) 19:46:50.70

まあ普通はセキュリティ部門にリクエストして審査通らんと入れるのは無理だわな
VBAも原則禁止だから毎回例外申請してるわ

2019/03/08(金) 21:38:31.98

環境申請が必要な業務を未経験者にさせるほどうちの会社はブラックじゃない

2019/03/08(金) 21:38:33.69

>>735
馬鹿はエンジンしか見ていない

2019/03/08(金) 22:23:14.58

具体的な事を言えよ

2019/03/08(金) 23:50:58.19

>>740
馬鹿には理解できんのか？

2019/03/09(土) 02:13:50.44

会社がoffice365に切り替わってAccess使えるようになった俺歓喜

2019/03/09(土) 02:15:54.88

accessはスレチ

2019/03/09(土) 04:02:18.85

accessって開発続いてるの？
Excelを強化してAccessを見放すように思えてしょうがない

2019/03/09(土) 04:12:29.83

accessはウンコです

2019/03/09(土) 07:26:22.26

Acccessとか。　いつの時代の遺品だよって感じだな
使いにくかったしなあ

2019/03/09(土) 07:48:05.33

>>744
>>731も書いてるけどExcelとAccessって用途が違うから当面なくならないと思うよ

2019/03/09(土) 08:34:58.65

SQLとOracleとAccess使うと書き方混乱する

2019/03/09(土) 09:22:46.51

シート上に複数のテキストボックスを配置して、
KeyDownイベント + vbKeyRightとかvbKeyTabで、
テキストボックス間を移動出来るようにすると、
カーソルがたまに見えなくなるんだけど、回避策ない?
見えないだけで、存在はしているので、
文字は打てるんだけど、文字まで透明になっちゃう。
テキストボックスを何回か適当にクリックすると、
突然見えるようになる。

2019/03/09(土) 09:24:00.36

↑あと、わざわざ画面更新を止めたりはしていない。

2019/03/09(土) 09:29:10.33

↑あ、別のPCで開いたら再現しなくなった。
もしかして、グラフィックボードの問題?

2019/03/09(土) 10:19:05.53

>>751
ボードと言ってるのはたぶんドライバのことだと思うけど、その可能性はあるよね。
いくつかバージョン違いを落としてきて試してみればいい。
とりあえずは、各メジャーバージョンが上がったばかりのは除いて、それぞれ最終リビジョンのを集めてきてかな

2019/03/09(土) 21:56:21.30

excel vbaとaccess vba
どちらの方が実用的ですか？

2019/03/09(土) 22:00:31.82

>>753
目糞鼻糞
今時ローカルで完結する業務要件なんかほとんどない

2019/03/09(土) 22:03:03.09

両方できて当たり前ですか？