Excel VBA 質問スレ Part74

2021/12/20(月) 01:40:59.22

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

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

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

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

2022/01/28(金) 03:12:47.26

>>712
まじですか、、、ありがとうございました。

2022/01/28(金) 12:32:58.97

すんません、膨大なデータ量の集計を任されて困ってます。
例えばA1セルに
「1.5G」とあるものは
Gを取った上で1024を3乗し、B1に表示
「256M」とあるものは
Mを取った上で1024を2乗し、B1に表示
「504K」とあるものは
Kを取った上で1024を掛け、B1に表示
みたいな便利なことができる関数があれば教えて頂きたく。
SUBSTITUTEで文字を取ったりVALUEで数字だけ抜き出したりしてみても、そこに条件判定を組み込むことが出来なくて…。

2022/01/28(金) 13:00:06.93

>>714
Excelのバージョン書いて

2022/01/28(金) 13:09:05.83

>>714
あんまりスッキリした方法じゃないけど、単位ごとに処理を分けて並べるぐらいしかないのでは
式はもうちょっと整理できると思うけど、とりあえず動くやつ
=IF(RIGHT(A1,1)="G",LEFT(A1,LEN(A1)-1)*1024^3,IF(RIGHT(A1,1)="M",LEFT(A1,LEN(A1)-1)*1024^2,IF(RIGHT(A1,1)="K",LEFT(A1,LEN(A1)-1)*1024,A1)))

2022/01/28(金) 13:43:11.42

>>715
Microsoft 365 MSO(16.0.13801.21004)
>>716
本当にありがとう、助かります。

2022/01/28(金) 13:54:48.79

powerとfindで考えてたけど、あんまり変わらんかった

2022/01/28(金) 14:06:54.61

>>717
365 なら
=SWITCH(RIGHT(A1, 1), "G", 1024^3, "M", 1024^2, "K", 1024) * LEFT(A1, LEN(A1) - 1)
ただし、最後が GMK でなかった時はエラーになるのでその場合はIFERROR()で回避かな

2022/01/28(金) 14:10:37.46

集計なら式使わずに素直にVBAで処理すれば？

2022/01/28(金) 15:01:21.24

>>719
ありがとう、それも問題なくいけました。

2022/01/28(金) 15:16:30.86

>>721
1K未満の単位の付かない数値に対応したやつ
=LET(u,RIGHT(A1,1),p,SEARCH(u,"kmg"),IFERROR(LEFT(A1,LEN(A1)-1)*1024^p,A1))

2022/01/28(金) 15:34:49.10

色々ありがとう。使わせていただきます。

2022/01/28(金) 16:00:55.15

>>723
スピードを求めるならVBAでやった方がいい。
基本、VBAよりCELLに入れた数式の方が計算早いけど、
値を設定するたび再計算が走るのでは当然遅くなる。

単位をRightの1文字切り取って使うかInStrRevで1文字切り取って使うかは自由だし、
数値部分も最後のひと文字を抜いて数値変換する方法はどうにでもなるはずだから、
後は切り取った単位で冪乗計算の乗数を変えてあげれば簡単に行けるはず。

2022/01/28(金) 16:07:42.05

膨大な行数になるようなら
関数よりマクロの方が断然軽い。

マクロの記録で瞬間でできるし。

2022/01/28(金) 16:08:26.59

記述すら必要なし。

2022/01/28(金) 17:30:28.61

マクロの記録で瞬間て、どうやるの？

2022/01/28(金) 17:31:33.84

一瞬で

2022/01/28(金) 17:35:47.36

この程度なら関数の方が速いと思うぞ
マルチスレッドで動くし
再計算も変化したセルに関連するところしかやらないから問題にはならないと思う

2022/01/28(金) 17:36:25.47

ここはVBAスレだから質問はスレチだけどみんな優しいね

2022/01/28(金) 17:49:38.97

>>730
はて？

「Excel VBA 質問スレ Part74」
とあるが？

2022/01/28(金) 17:57:01.02

> ここはVBAスレだから（計算式の）質問はスレチだけどみんな優しいね

2022/01/28(金) 18:14:34.37

>>714
こんな奴に作業を任せるとかそもそも間違えている

2022/01/28(金) 18:21:53.88

今のエクセルの計算式は基本マルチスレッドでやるから、VBAでちまちまやるよりは早いだろうけど
このスレ的にはVBAで関数作るんが正解じゃないかと思った

で、ふと思ったが、VBAの関数をワークシートの計算式で使ったら、シングルスレッドで処理されるのかな

2022/01/28(金) 18:58:00.84

>>711
どうしても「そのテーブルのフィルタソート機能を使用した」時点を起点に処理を始めなければならないとすれば、
Worksheet_SelectionChangeで、その特定のテーブルの特定の列を走査して昇順または降順にデータが並んでいれば「フィルタソート機能を使った」と判定する、ぐらいしか思いつかない
たまたまソートする前にデータが整列してしまっていた場合や処理が完了した後に再度処理が走ってしまうので、どこかにフラグを作って管理する必要もあると思うけど
あと、ソートする列に数値と文字列やブール値などが混在している場合も判定に注意が必要かと思う

2022/01/28(金) 19:12:04.41

>>730>>732
vbaより数式や標準機能が簡単なら、そっちで回答するだけだよ
「excel&vba」なのか「eccel|vba」かは解釈次第だが、俺は後者だと思う

2022/01/28(金) 19:24:01.89

７時３０分から３０分ずつ加算した時刻をコンボボックスに入れるやり方をおしえてください

For i = 1 To 19
UserForm1.ComboBox5.AddItem Format(DateAdd("n", 30, "7:30"), "h:mm")
Next i

2022/01/28(金) 19:35:31.95

>>734
> で、ふと思ったが、VBAの関数をワークシートの計算式で使ったら、シングルスレッドで処理されるのかな
ドキュメントは見たことないけどVBA部分はシングルだろうと思う
マルチで走るとグローバル変数で競合したとき困るし

2022/01/28(金) 19:40:10.55

VBA専用だけどExcel全般もOK
Excel板と仲悪い理由だけは分かった。

2022/01/28(金) 19:41:52.43

>>737
Dim T As Date
T = TimeSerial(7, 30, 0)
For i = 1 To 19
UserForm1.ComboBox5.AddItem Format(T, "h:mm")
T = DateAdd("n", 30, T)
Next i

**デフォルトの名無しさん** (ｽﾌｯ Sd0a-bU36) · 2022/01/28(金) 19:55:14.07

power queryと変数定義関数で、VBA使わずともかなりのことができるようになったよね
ローコードツールみたいだなと思う

2022/01/28(金) 20:01:26.98

>>735
> Worksheet_SelectionChangeで、その特定のテーブルの特定の列を走査して昇順または降順にデータが並んでいれば「フィルタソート機能を使った」と判定する、ぐらいしか思いつかない
そんなことしなくてもWorkSheet.AutoFilterMode見て、WorkSheet.AutoFilter見たらいいだけ

**737** (ﾜｯﾁｮｲ 11cc-21ac) · 2022/01/28(金) 20:13:02.61

>>740
ありがとうございます

2022/01/28(金) 21:49:05.63

>>742
テーブルらしいので、WorksheetじゃなくてListObjectを見ないと判別できない
あと少なくとも自分には、AutoFilterにはフィルタソートをかけたかどうかを判別できるものが見つけられなかった
（フィルタソートしてもAutoFilter.FilterModeはFalse）
もし環境で動作が異なるのならごめんなさい

2022/01/28(金) 22:21:03.74

>>744
> テーブルらしいので、WorksheetじゃなくてListObjectを見ないと判別できない
ああ、そりゃすまん

> あと少なくとも自分には、AutoFilterにはフィルタソートをかけたかどうかを判別できるものが見つけられなかった
手元に環境ないから試せないけど
ListObjects(...).AutoFilter.Filters(...).On
じゃねーの？

2022/01/29(土) 00:40:00.86

図書館に「はじめてのPower Automate Desktop」ていう本があったので借りてみたんだけどよ。
VBAでは10-20行ぐらいのエクセルの処理を、40ページぐらいかけて説明してたわ。
やってることは、VBAでやるコマンドをGUI操作でやってるような感じ。
どう考えても、VBAより簡単とは思えんしむしろVBAのほうが簡単じゃねーの？

2022/01/29(土) 02:38:30.78

>>746
馬鹿には違いがわからんだけだろ

2022/01/29(土) 06:07:15.34

AWS など、Linux は逆。
GUIを排除して、コード化する。IaC(Infrastructure as Code)

AWSのGUIで設定したら、人によって間違うから、
どのボタンを押すなど、ソースコードで書いて、Terraform で実行する

これなら、Terraformのコードをサーバーへ転送して、実行するだけで環境構築できる

2022/01/29(土) 06:12:15.60

いや…VBA (VB6)を作った人は、相当優秀やな

昔、VB6 でフリーウェア作ったり、楽しかったわ…

2022/01/29(土) 07:43:57.70

>>746
まあ、コードがわからん人（わからん人が使えるとは言ってない）向けだからな。
会社で色々ガチガチに制限されてるけど、そこだけはオッケーとかだったら使うしかないじゃない？そういう人向けだと思うよ。

Power Automateはまだサーバーが勝手に動いてくれるからマシだけど、Desktopはホント用途がわからん。

2022/01/29(土) 07:56:58.17

他言語のプログラマーからは下に見られるVBA
なぜ差別される？

2022/01/29(土) 08:31:49.58

中途半端に自分の知識を誇示して周囲を見下すやつが多いから
職場や上司を叩いて自己満足に浸るレスがこのスレでも目立つ

2022/01/29(土) 08:56:48.05

起動しているedgeやIEで開いているページから文字を取り出したいのですがどうしたらいいでしょうか？

もしくは起動しているページからワンクリックで文字を取り出しExcelに貼り付ける様な方法はありますか？

2022/01/29(土) 09:01:58.47

RPAみたいに世間の認知も高いわけじゃない、
プログラムほど難しいことやってるように見えない、
そのくせ仕事は手抜きしてるように見える、
聞いてもよく分からないことしか言わない、

知らない人からみたらこんな感じだからじゃない？

2022/01/29(土) 09:41:15.24

>>751
理由は色々あると思う。
名前がVisual Basic for Applications というくらいEXCELとかACCESSとかのMicrosoft Officeに特化していて
実際のシステムを構築するのには不向きであること。

間口が広いので初心者向きでもあり、それが逆にどんな人でもちょっと勉強すれば組めてしまうイメージがあること。

フレームワークの知識が皆無でも普通に組めてしまうこと。

VB.Netですら消えかかってるこの時代にあまりに旧態依然とした言語であること。

ちょろっと何かを組むには非常に良い言語なんだけどね。俺もVBAでシステム組めって言われたら「え・・・」ってなると思う。

2022/01/29(土) 09:47:43.70

そもそもVB自体が下に見られているからじゃないかな
知らんけど

2022/01/29(土) 09:54:07.85

>751
覚える事が信じられないぐらい少なく、誰でも使えるから
変数とif、for辺り知ってればほとんどの場合に対応できる
若干とっつきにくいのがset程度
更に相当テキトーに書いても他へ影響し辛い

どれも実務上は長所だけど、
他の言語を知ってると「なにこれ？」ってなるのはわかる。なんというかおもちゃっぽい

2022/01/29(土) 10:17:37.26

>>745
自分の環境だと、普通のフィルタをかけた状態ならそれで拾えるけど、フィルタのソートを行った状態は拾えないので、やりたくないけどどうしてもやるなら列のデータをなめるしかないのかなと

>>751
元々、Visual Basic自体がBASICという、学校でプログラミングの導入として教えるような簡易なプログラミング言語から派生したものだから、他の言語より低く見られていた
更にそこから派生したVBA（マクロ）やVBS（スクリプト）なんかはより低く見られている
まぁ実際に、図体の重たいプログラムでは手が届きにくい部分を補完する孫の手的な使い方をするのが主流なので、穴埋めや間に合わせ的な位置付けに見られているからだと思う

2022/01/29(土) 11:04:25.26

>>758
ソートは一時的な状態じゃないから完全に拾えるわけじゃないけど
ListObjects(...).AutoFilter.Filters(...).Sort
見たらわかるんじゃないの？

2022/01/29(土) 11:05:21.11

>>751
差別しているのは Ruby厨だから無視していいんじゃね
そもそも言語に上下なんかないし

2022/01/29(土) 11:13:37.04

>>757
> 他の言語を知ってると「なにこれ？」ってなるのはわかる。なんというかおもちゃっぽい

なんとなく納得できる表現だな。
C・C++・C#・JAVA・Rustとかの、かちっとしたのが当然だと思ってると面食らう。

特に Variant。まあ、最近はコンパイラ系に逆輸入されてるけど。

2022/01/29(土) 11:15:31.30

あ。馬鹿にしてるわけではないよ。便利だと思う。

ただ、コンストラクタ引数と継承は入れてほしかった。

2022/01/29(土) 11:28:56.70

VBAが言語として下に見られてるのかは知らんが書いてるやつの水準は他言語より圧倒的に下だよ
変数の型指定しない、withつかわない、まとめられる処理をまとめずにコピペし大量のモジュールを作る、配列とか使わないでセルを一回一回読み書き
こんなコードが会社のエクセルにはたくさんある
そしてそれが神マクロと崇め奉られてるのホントゴミ

2022/01/29(土) 11:44:30.01

>>761
> 特に Variant。まあ、最近はコンパイラ系に逆輸入されてるけど。
void * とか昔からあるけど？

2022/01/29(土) 12:02:30.08

結果がすべて
仕事を終わらせたやつが評価されるんであって、どんな方法を使ったか、きれいに書いたかどうかなんて普通は問われない

2022/01/29(土) 12:10:51.21

>>765
俺もそっちだわ
仕様として許容されているなら使う人が目的達成できればどうでもいいんよ
まぁリファクタリングしろと言われたら発狂するが

2022/01/29(土) 12:12:26.42

何かを見下したい、マウントを取りたいのは、
愚か者の本能であって、理由は後付け。

VBA がハイコストパフォーマンスで
ある程度のシステムを構築できてしまう
便利なツールであることには変わりない。

頭の悪い人間ほど、意味のない格付けをしたがる。

2022/01/29(土) 12:22:38.03

Windows環境で、
なんらかの要件を実現する方法の選定として
導入コストを考えたら、DOSバッチでも
PowerShellでも、JavaScriptでも、WSHでも
既存のリソースのみで実現できる方が良いに決まってる。

特に日本人の思考は、合理性を軽視して、見てくれや
くだらない感情で判断することが多いので、
開発のプロジェクトでも時間とカネのコスト管理ができてない
愚かな企業が多い。

2022/01/29(土) 12:23:55.26

>>765
そういう書き捨ての仕事もあるし、連綿と続く仕事もある

2022/01/29(土) 12:27:08.84

結果として、他国からバカにされてる
日本の生産性の低さに繋がっている

あらゆる分野で、開発スピードは、
圧倒的に米欧・中華が優っている

2022/01/29(土) 12:29:20.03

>>768
例の馬鹿か

2022/01/29(土) 12:34:16.18

>>765
書き捨てではなく日々の定型業務につかわれ
それらが前提になって業務フローが組まれてるから
保守が必要なんだよなあ…
なお制作者と仕様書、コメントはない

2022/01/29(土) 12:43:35.90

まあ正直、要件定義～設計までの上流工程の人間からすると
言語は何でも良いんだけどね。

こだわるのはコーディングフェーズ以下の下流エンジニアであって。

2022/01/29(土) 14:49:20.34

関数が得意な人はなんでも関数で片付ける、マクロが得意な人はなんでもマクロでやろうとする傾向があると田中氏が言っていたな講演会で。
バランス良く使える人こそ真のエキスパートだとも。

2022/01/29(土) 15:14:16.94

>>774
そうなんか
確かに関数使うのって電卓がわりにするか文字の置き換えくらいかも

2022/01/29(土) 15:41:11.14

指定した範囲にそれぞれ外枠線を引きたいのですが、量が多くなると時間がかかるのでＵｎｉｏｎを使いたいのですが、

Dim rng As Range, i As Long

Set rng = Range("B2:B3")
Set rng = Union(rng, Range("C2:C3"))

For i = 4 To 50 Step 2
Set rng = Union(rng, Range("B" & i & ":B" & i + 1))
Set rng = Union(rng, Range("C" & i & ":C" & i + 1))
Next i

rng.BorderAround Weight:=xlThin

だと、Range("B2:C51").BorderAround Weight:=xlThin　と同じになってしまいます。
短い時間で、それぞれの範囲に外枠線を引く方法を教えてください

2022/01/29(土) 15:45:53.96

>>753
https://www.google.co.jp/search?q=vba+%E3%82%BD%E3%83%BC%E3%82%B9%E5%8F%96%E5%BE%97

2022/01/29(土) 15:52:44.84

>>761
JavaでもObjectになんでも突っ込めるじゃん

2022/01/29(土) 16:14:17.19

マクロ書くより手作業でやった方が早いのにわざわざマクロでやる人ばかりとも言っていたな。
なるほどなと納得する講演会だったね。

2022/01/29(土) 16:34:34.13

今知ったんだけどFalseってフォルスって読むんだな
考えてみれば失敗じゃなくて偽りだもんな
本職じゃなくて良かったわ～

2022/01/29(土) 16:35:06.88

なんで Cells を使わず、わざわざ
Range を使うのが多数派なんだろ。
メリットないじゃん。意味わからん。

2022/01/29(土) 16:40:41.52

>>774
田中さんそんなこと言ってたのか
俺ならバランスより効率重視だな
数式にマクロの関数仕込むこともあるし
その逆もある。

2022/01/29(土) 16:41:42.31

だってマクロ書きたいんだもん

2022/01/29(土) 16:49:23.58

>>781
a1とかa1:f5とかを暗黙定義された名前と考えて
名前で指定するイメージ

2022/01/29(土) 17:04:38.36

>>780
失敗は Fail だもんな

2022/01/29(土) 17:04:52.88

俺はcellsだよ、座標として捉えてるから数値との親和性が高く感じる

2022/01/29(土) 17:06:06.65

>>785
そやねん、フェイルセーフっていうからその延長で思い込んでしまってたみたい

2022/01/29(土) 17:06:09.58

>>781
極端な例だけど Cells(100, 100) がどのセルを指してるのかすぐにわかるなら…

2022/01/29(土) 17:09:18.89

>>788
なるほど
でもSAの倍の座標がどこかが解りづらいんじゃない？

2022/01/29(土) 17:16:34.10

日本はA1形式なのでどっちにしても分かりづらい
歴史的経緯で今更どうしようもないとは言え、残念すぎる

2022/01/29(土) 17:29:33.23

>>789
倍の座標とかの計算結果で指定するならそりゃCells()で指定するよ
Dim c As Range
Set c = Range("SA100")
としてCells(2 * c.Row, 2 * c.Column)
>>781は例えば最初のセルの指定とかでアドレスが分かってる場合でしょ

2022/01/29(土) 17:59:18.61

>>782
うん。自分の耳が腐っていなければ確かにそれを強調していたように聞こえたよ。。
とにかく後任者の事を第一に考えようと。
あと、速度改善するマクロを書いても後任者が理解できなければそのマクロは結果遅いマクロとも。
一理あるなと思った。
かなり前の話だからうろ覚えごめんなさい。

2022/01/29(土) 18:07:28.04

何が正解かはケースバイケースだしね
LETとかLAMBDAのサポートで正解変わったりするし

2022/01/29(土) 18:58:08.38

CellsとRangeなんて一長一短なんだから
状況に合わせて使い易い方、分かり易い方を使えばいい。
どうせ両方とも型はRangeだ。

2022/01/29(土) 19:05:46.63

例えば、数万行の処理をループで回す場合とか
Range と Cells とでは、処理速度が全然違うのよ。

実際にやってみればわかる。

2022/01/29(土) 19:07:36.86

Range(A & i)みたいなコードをたまに見るけど、あまり良い書き方ではないと聞きましたよ

2022/01/29(土) 19:10:29.95

>>796
そりゃ普通そんな書き方しないもの。
最初の位置から移動させて使う場合は
RangeならOffset使うでしょ。

2022/01/29(土) 19:13:25.82

いわゆる「マクロ入門書」とかにもそのような変な書き方がある。
教える側の立場にある人がそんな書き方してるのはちょっと

2022/01/29(土) 19:49:18.54

>>797
Offset(), Resize() はセル結合を「考慮してくれる」からイマイチ使い辛い

2022/01/29(土) 19:57:24.50

可読性と処理速度要件で変えるだけ

2022/01/29(土) 20:51:43.39

>>795
100万ループさせてやっと数秒か
この程度の差なら速度は誤差では？
速度抜きにしてもrange("a1")って書き方はせんけど

Sub foo()
GetLocalTimeTest
For i = 1 To 1000000
'5秒
'bar = Range(Cells(1, 1), Cells(1, 1))
'2.8秒
'bar = Cells(1, 1)
'6.7秒
'bar = Range("a1")
Next
GetLocalTimeTest
End Sub

2022/01/29(土) 20:55:44.71

setは？

2022/01/29(土) 21:16:18.07

ねーこれかなり凄くない？
https://rkb.jp/article/61225/
実務を経験していない女子高生がExcel世界一だって。

2022/01/29(土) 21:18:02.94

>>790
いろは順が良かったってか？

2022/01/29(土) 21:19:19.33

学生大会ね

2022/01/29(土) 21:24:22.79

>>803
素直にすごいと思う
まあExcelに対する知識もさることながらプレゼン力やセンスも問われるから俺には全然無理

**デフォルトの名無しさん** (ｽﾌｯ Sd0a-IClH) · 2022/01/29(土) 22:21:59.94

>>803
マスゴミの手に掛かればExcelそっちのけで好きな食べ物は？とか好きな男性のタイプは？とかどうでも良い情報ばかり取り上げられるだけ
紹介動画でExcel触ってんの1分も無いじゃん

2022/01/29(土) 22:38:28.21

>>781
マクロの記録で自動生成されるとRangeが使われるからじゃない
自分もCells派だけどいろんなツールで使ってる共通的な関数群はRangeの方が作りやすいからRangeも使ってる

2022/01/29(土) 22:41:28.05

ループ回すにはCellsのが書きやすい

2022/01/29(土) 22:44:29.32

>>808
> いろんなツールで使ってる共通的な関数群はRangeの方が作りやすいからRangeも使ってる
？？？
どっちを使っても型はRangeだよ…

2022/01/29(土) 23:35:26.26

書き方が良くなかったね
自分が作ってる関数だと引数の使い方がRangeの方が汎用性が高い

2022/01/29(土) 23:51:38.36

引数は文字列と言うことかな？