Excel VBA 質問スレ Part77

**デフォルトの名無しさん** (TW 0H42-j7SA) · 2022/07/17(日) 07:07:35.59

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

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

次スレは>>980が立てること
無理なら細かく安価指定

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

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

2022/10/02(日) 15:47:28.98

>>787
おいおいいつだよって思ったら昨日かよ

>>786
それでいいと思うんだよな
それが社会の基本だし、暇な元事務のNEETはそれで快楽を得つつ勉強になり、
転職への自信になるだろう

2022/10/02(日) 20:54:31.26

VBAは作業を登録して自動化するくらいで覚えておいた方がいいですよね

2022/10/03(月) 06:16:19.93

>>788
800kの画像でやってみたけど15Mぐらいまで巨大化してしまう
やはりAddPictureで代用してみる

2022/10/03(月) 07:27:24.16

VBEに書いたコードの整列っていうかコードを整えるっていうか自動インデントっていうかそんなショートカットキーがあったと思うけどなんだっけ？

2022/10/03(月) 17:56:19.40

タブ

2022/10/03(月) 20:58:11.99

>>791
変な枷付けること考えんと
好きに作っていいんやで

2022/10/04(火) 15:00:55.06

イベントで質問

余所で作ったエクセルにVBAで改造加えてるんだけど
1マス入力だけするSheetにActiveXのコマンドボタン付けたら
Worksheet_SelectionChange
が発生しなくなった

コマンドボタン押したイベントは拾ってるので
EnableEventsがFalseってわけじゃない（イミディエイトで確認済み）

デザインモードを解除してもWorksheet_SelectionChangeイベントが起きないので
しょうが無いからまた一から作り直したんだが
この現象って頻発するもの？
ググってもOfficeの修復かEnableEventsしか解決策が出てこない

2022/10/04(火) 15:54:59.32

>>792
Sub Macro1()
ActiveSheet.Pictures.Insert "C:\Work\Sample1.jpg"
End Sub

これで400KBのpngで試してみたけど
なぜかxlsmファイルのサイズは15KB
何か別の要因とか

2022/10/04(火) 19:28:07.31

>>796
そりゃエクセルを改造したら駄目だろ

2022/10/04(火) 19:47:41.77

>>792
オプションの詳細設定-イメージのサイズと画質　ってどうなってる？

2022/10/04(火) 21:08:36.46

Dim ar(2) As Variant
ar(0) = 1
ar(1) = 2
ar(2) = Split("1,2,3,4,5", ",")
Dim i
For i = 0 To UBound(ar(2))
ar(2)(i) = CLng(ar(2)(i))
Next i

splitで作ったジャグ配列の中身をlong型に変換したいです。
variant変数に一度入ったらもう無理ですか？
一応他の変数に入れる事で型変換には成功するのですが。
余計な変数を二つも用意してstringで作成されたのを一時的なvariant型ノーマル配列に
おさめてｍそのあとで本当に使いたい配列のジャグ配列に入れていくというのが
どうも汚い感じで腑におちません。
アドバイスもらえると助かります。

2022/10/04(火) 22:26:54.77

>>800
出来ません
諦めてください

**800** (ﾜｯﾁｮｲ 6f66-vqPj) · 2022/10/04(火) 22:43:48.69

>>801
ありがとうございます。
ジャグ配列はRedim出来ないのがデメリットですね。
動的な配列の中にジャグ配列なのですが、なんとか先に不確定な親配列の中にある
不確定な数のジャグ配列を一時的なLong型配列に作って
そこからさらにsplitなしでなんとか作ろうとあれこれしてたら
よっぽど>>800の方法の方が処理がわかりやすくなってしまい徒労に終わりました。

諦めます。踏ん切りつきました。

2022/10/04(火) 23:35:06.72

>splitで作ったジャグ配列
>動的な配列の中にジャグ配列
ジャグ配列って用語の使い方が微妙に間違ってる気がするが...

そもそも文字列を分割して配列作ってるんだから文字列の配列になるのが当然で
Variant上等でやるなら、それを使う側がVariantで受ければいいだけでは？

まあ俺ならどうしても文字列をSplitしてLongにしたいなら
Longの配列返すSplitのラップ関数作るけど

2022/10/05(水) 04:48:52.00

sqliteで作ったジャグ配列。

2022/10/05(水) 07:10:06.74

>>797
それでいくとうまくいくんだけどね、
通常のPictures.Insertだとリンク貼付けなのよ。他人に送付したら見れない。

だから一旦クリップボード経由でPaste使ってやってるんだがそれだと容量がヤバくなる。

2022/10/05(水) 08:07:32.65

そもそも何でもかんでも配列で処理しようとするのがちょっとねぇ。
例えば列に当たる要素毎に違う型を使いたいなら
ちょっと仕組みは大きくなるけどADODBのRecordsetを使うことや
1行の列データ単位の値を格納するクラスを作ってそれを行単位としてディクショナリやコレクションに追加して行くことも検討することを視野に入れると
結局後で読みやすく汎用性の高いロジックが組めたりする。

処理速度優先なら配列一択だからそのときのシュチュエーションに合わせて使い分けだね。

2022/10/05(水) 08:19:21.40

構造体ではできんの？
ジャグ使う機会無いからなんとも言えんが

2022/10/05(水) 10:50:57.71

すぐDBとか持ち出す奴いるけどコスト考えたらそう簡単にDBなんか使わんと思うけどね
普通に配列処理用の関数のみのモジュールでコード管理すればいいだけと思うわ

2022/10/05(水) 13:20:52.03

>>808
何のコスト？

2022/10/05(水) 14:41:00.50

やっぱ好きなように作るのが１番ね

2022/10/05(水) 15:47:13.70

ワークシートがDBみたいなもんだしね

2022/10/05(水) 15:53:15.41

学習コスト

2022/10/05(水) 21:19:11.66

コストコ

2022/10/05(水) 23:35:05.88

コスパ良い

2022/10/06(木) 16:49:22.22

例えば、誕生石を取得するGetBirthStone関数を作ろうとすれば引数は「日付 as Variant」と「月 as Integer(Long)」のどちらがいいでしょうか？

2022/10/06(木) 16:54:16.04

Dateではあかんのか？

2022/10/06(木) 16:55:37.45

そんなもんVLOOKUPで誕生石シートを検索すれば十分
VBAを正しく使うコツはVBAを可能な限り書かないことだよ

2022/10/06(木) 17:19:15.50

>>816
Date型でも大丈夫ですか？

一応、最初の部分はこう書いてみました
If Not IsDate(日付) then
GetBirthStone ＝ CVErr(xlErrValue)
Exit Function

2022/10/06(木) 17:48:34.71

>>817
いや、むしろVBAだけの方がメンテしやすい
データとロジックは分ける方がいい

2022/10/06(木) 19:10:02.45

>>815
元のデータが何によるかで違うけど「月 as Integer(Long)」でいいのではと思う
仮に元のデータが日付でもGetBirthStone(Month(日付))で済む
月を渡すようなケースがありえないなら日付を渡して関数内でMonthを使うでもいい

2022/10/06(木) 19:39:53.74

月だけでいい
特定の日付に依らずある月の誕生石を知りたい場合に
意味のないDate値用意しなきゃいけなくなるから可読性が落ちる

>>817
誕生石みたいに改訂されたことがニュースになるくらい固定されてる内容ならコードにベタ打ちでいいと思う
祝日みたいに毎年変動するものとか、組合せの数が多すぎる要素ならシート管理の方が扱いやすいと思うけど

2022/10/06(木) 19:46:56.80

>>815
その程度のこと自分で決められんようでは時間かかってしょうがないだろ

2022/10/06(木) 19:49:13.70

>>815
Function getBirthStone(日付 As Date)
Application.Volatile True
getBirthStone = WorksheetFunction.Choose(Month(日付), "ガーネット", "アメシスト", "アクアマリン", "ダイヤモンド", "エメラルド", "ムーンストーン、真珠", "ルビー", "ペリドット", "サファイア", "オパール、トルマリン", "トパーズ", "トルコ石、タンザナイト")
End Function

こんなかんじ？

2022/10/06(木) 22:32:10.00

>>823
ありがとうございます。

2022/10/06(木) 22:47:25.99

>>823
わざわざワークシート関数を呼ばなくてもVBAにもChoose関数あるよ
https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/choose-function

2022/10/06(木) 23:06:53.10

ほんとだｗ　てへ

**800** (ﾜｯﾁｮｲ 0666-Phf8) · 2022/10/09(日) 17:09:18.30

>>803
ラップ関数作る解決方法最高です。
頭固かったです。
他にも導入できそうなところが見つかり助かりました。
感謝です。

2022/10/10(月) 21:48:05.32

>誕生石みたいに改訂されたことがニュースになるくらい固定されてる内容ならコードにベタ打ちでいいと思う
>>823を見ての通りこの手は表記ゆれが問題になる事が多いから
可能な限り外部データにした方がいい

2022/10/11(火) 13:53:54.44

インプットボックスで入力される値が数字かどうか判断するコードってどう書けばいい？

2022/10/11(火) 14:15:03.61

・符号(+-)を許すか
・ドット(小数点)を許すか
・カンマ(桁区切り)
を踏まえて文字の妥当性チェックすればいいんじゃないの

2022/10/11(火) 14:17:40.55

あと自分はWorksheetFunction使うの好きじゃないけど
WorksheetFunction.IsNumber
でもいいかもね

2022/10/11(火) 14:26:37.29

全角は？漢数字は？ローマ数字は？欧米式の桁区切りは？
入力検査は考え始めるとキリがない分野の一つだぜ

2022/10/11(火) 15:13:10.17

>>829
IsNumericじゃダメなの？

2022/10/11(火) 17:02:04.45

>>833
さんきゅーそれだ
てかVariant型が数値かどうか調べられるんだな～
書き方解ったらvartypeとかtypenameでも行けそうな気がする
ちょい試してみよ

2022/10/11(火) 18:13:13.59

>>834
語弊があるといけないから言っておくと、
IsNumericはあくまで数値として扱える値(数値に変換出来る値)かどうかを判定するよ。
だからString型でも”123”みたいのはTrueを返すからそこは注意してね。

2022/10/11(火) 20:16:52.08

考慮点はまだあるぞ
IsNumericって
"\3"とか"3e3"とか"&habc"とかもTrueだぜ

2022/10/12(水) 08:33:18.55

じゃ足し算する関数を別に作ってﾉｰｴﾗｰで戻ってきたら数値

Sub Main()
　'全角数字も数字と見なす
　If Suujikana(StrConv(InputBox("入力せぇ"), vbNarrow)) Then
　　MsgBox "数字だよ"
　Else
　　MsgBox "数字じゃないよ"
　End If
End Sub

Function Suujikana(moji As Long) As Boolean
　On Error GoTo er
　moji = moji + moji
　Suujikana = True
　Exit Function
er:
　Suujikana = False
End Function

2022/10/12(水) 08:35:48.26

あ、これじゃだめかえらーでるあ

2022/10/12(水) 08:38:00.09

このやろ、variantにしたら＋で文字列つくりやがって
こんのやろぉ

2022/10/12(水) 08:39:59.19

これでいい？らめ？

Sub Main()
　'全角数字も数字と見なす
　If Suujikana(StrConv(InputBox("入力せぇ"), vbNarrow)) Then
　　MsgBox "数字だよ"
　Else
　　MsgBox "数字じゃないよ"
　End If
End Sub

Function Suujikana(moji As Variant) As Boolean
　On Error GoTo er
　moji = moji * moji
　Suujikana = True
　Exit Function
er:
　Suujikana = False
End Function

2022/10/12(水) 09:33:08.65

>>836
まぁその辺は数値として受け取れるとも言えるからね。
その辺除外したいなら最初に“¥”や“e”や“&h”が含まれていたら数値ではないって分岐入れとけばいいだけじゃないの？

2022/10/12(水) 09:45:17.75

>>840
自分も同じこと考えたけど型の制約が緩いと面倒くさそうなので思考を放棄した、よく頑張ったな

2022/10/12(水) 10:05:45.44

そういえば昔型はあるのとないのではどっちが良いのかって記事を見たことあるな
結局型がないと論理エラーが出ないように型をちゃんと理解する必要があるって書いてた

2022/10/12(水) 10:15:23.77

kgとkmは単純に足し算しても意味ないからな

2022/10/13(木) 04:03:11.23

別にVBA使わなくてもワークシート関数でできるじゃんって意見どう思う？

2022/10/13(木) 04:08:37.62

やりたいようにやれ

2022/10/13(木) 05:43:31.20

>>845
内容によるけどまあワークシート関数でできるならその方がいいだろうな

2022/10/13(木) 06:51:28.60

A1に入力されているものが消えたらB1以降の入力セルデータが右から左に自動で繰り上がってくるようにしたいのですがこれで大丈夫ですか？

これをA20くらいまで適用したいんですけど教えて貰えませんか...？

Private Sub Worksheet_Change(ByVal
Target As Range)
If Target.Address = Range("A1").Address
And IsEmpty(Target) Then
Application.EnableEvents = False
lastrow = Cells (Rows.Count,
"A").End(xIUp).Row
Range("B1:F1"&lastrow).Copy
Range("A1").PasteSpecial xIPasteValues
Range ("A"&lastrow).ClearContents
Application.EnableEvents = True
End If
End Sub

2022/10/13(木) 11:53:03.67

キーボード操作でマクロ作って呼べばいいんじゃね

2022/10/13(木) 13:03:11.83

"B1:F"&lastrow
下から4行目は何の処理かな
g列以降にデータがないなら、a列削除でもいけそう

2022/10/13(木) 13:57:54.49

xIUp >> xlUp >> xlLeft
xIPaste >> xlPaste

excel だから　xl だよ
本から手入力したのかな

2022/10/13(木) 15:04:44.40

文字列にしてTrimして
右から一文字づつAscでコード調べて数字判別してる

2022/10/13(木) 18:35:01.73

>>845
関数なんか使ったことない
全部マクロでできるし

2022/10/13(木) 19:55:20.65

ワークシート関数も便利なものは便利で、
パフォーマンスについてVBAより良い場合があるものもあった記憶がある
まあ使い分けだな

2022/10/13(木) 20:16:02.23

あたりまえだかパフォーマンスはワークシート関数の方が高いよ
そもそもマルチスレッドで動くし

2022/10/13(木) 21:52:09.43

問題はたくさんのセルに関数を入れまくると超遅くなることだ

2022/10/13(木) 22:26:40.06

>>845
できるだけ関数に寄せる方が良いと思う
あまりに複雑になりすぎるなら、vbaでも良いって感じ

2022/10/13(木) 22:36:14.16

>>856
それVBAでやったらそのセルの分ループで回すことになるからますます遅くなるわけで...

2022/10/13(木) 23:31:54.00

カスタム関数は使い捨て

2022/10/14(金) 02:09:11.91

ワークシート関数は自動計算で実行する
マクロ(vba)はイベントで実行する

2022/10/14(金) 12:43:43.74

>>845
ワークシート関数で済むならそれに越したことはない
複雑になりすぎてVBA使うのと変わらんレベルになるくらいなら、状況次第でVBA使うこともある
よく検討してみてExcel仲介する必要ないならVBSなんかも視野に入ってくる
他のより良い手段があるならそっちにする

2022/10/14(金) 13:06:50.25

ケースバイケースという当たり前の結論
だがそれが大事

2022/10/15(土) 01:10:39.87

そういや何気にVBAの中でCallByName使うとJavaScript 使えるらしいね。
使ったことないけど。

2022/10/15(土) 01:25:23.69

>>863
どういうこと？　どうやるの？

2022/10/15(土) 07:21:27.16

>>863
文字列で言語書いて実行するのってどうも好きじゃないな
そもそも有効な場面ってあるんだろうか
http://bougyuusonnin.seesaa.net/article/191171013.html

>>864
調べなよ・・・

2022/10/15(土) 09:05:24.46

>>865
さあ？
メソッドチェーンが使えるくらい？
まぁ無理して使うもんでもないような。
現にJavascript知っててもVBAに混ぜて
使うような状況なんてなったことないし、
大体記述がめんどいし。

2022/10/15(土) 09:07:30.39

>>866
ありがとう
やっぱりほとんど無いよね。

2022/10/16(日) 03:31:09.39

メリットはネイティブの手段でJSONがパースできるようになる事なんだけどVBAで必要になることはほとんどないか
ちなみに似たような方法でHtmlFileからIEのjavascriptを呼ぶ方法もある

2022/10/16(日) 05:54:19.77

ネイティブ？

2022/10/16(日) 06:00:43.85

予め言語側で用意されてるってことじゃない

2022/10/16(日) 06:58:42.73

正規表現をRegExpオブジェクトで利用することはネイティブですか？

2022/10/16(日) 08:27:49.05

正規表現をRegExpオブジェクトで利用したらハゲますよ

2022/10/16(日) 23:46:46.75

性器のことしか考えてないから禿げるんじゃね

2022/10/17(月) 02:28:59.55

正規表現はexcelapiを使うのが楽

2022/10/17(月) 17:47:22.14

すみません、質問させてください
range内の空白セルを除いた部分を一気に配列に入れる方法ってありますか？
例えばA1、B2、A3、B3に1が入っていて、Range(Cells(1,1) ,Cells(2,2))を選んでいる状態で配列ArrにArr(1,1)＝1、Arr(1,2)＝1のみを入れる。ということです
「Range(Cells(1,1) ,Cells(2,2))を選んでいる状態」というのはFunctionで関数を作ったときにこの範囲を選んでいるという意味です
CurrentRegionを使ってもA2、B2の空白まで配列に入ってきてしまって困ってます

2022/10/17(月) 17:53:55.90

selectionでforeach回せばできる気がするけどな

2022/10/17(月) 18:05:29.40

一気にやるなら判定文をスピルで一気に適用するか

2022/10/17(月) 18:22:32.67

すみません、先程の質問の意図はvba 100本ノックの以下の問題を解いていましたhttps://excel-ubara.com/vba100/VBA100_062.html
最初に範囲内を配列にすべて入れてdictionaryで出す方法を考えたんですけど、範囲のところがA1:C5とかならいいのですが、A:Cだと、すべての行まで配列に入ってしまって、それの行すべて処理するようになってしまったので、数字が範囲内の最終行までを配列に入れることができれば処理速度も変わるので、そういう方法があればという質問でした

2022/10/17(月) 18:56:02.19

答えも解説も書いてあるんだからそれ見ろよ

2022/10/17(月) 20:25:02.38

>>874
へーそんなのあるのか

2022/10/17(月) 21:15:18.56

作成依頼です。

G列に空白セルがあった場合、その空白セルの隣のセルに入力されている値をクリアにする。

というコードを作成いただけないでしょうか。

よろしくお願いします。

2022/10/17(月) 22:12:03.14

いやです

2022/10/17(月) 22:18:07.47

待て、これは孔明の罠だ

2022/10/17(月) 22:23:49.67

F、G、H列を無条件で消せばいいのね

2022/10/17(月) 22:24:49.52

隣とはどっちだ
あまりにも指示が不明瞭でバカが書いたとしか思えない文章
スルーで決定

2022/10/17(月) 22:28:51.88

>>881
マジレスするとここは質問スレであり
作成要請スレではありません。

作る方法等を聞けば教えてくれる人もいるかも知れませんが、作ってくれと丸投げにした場合、
その見返りを要求されるか一蹴されるかして終わるのが関の山です。

自分でそう言った趣旨のスレを探すか立てるかした方が賢明と思われます。

**デフォルトの名無しさん** (US 0Hbf-459s) · 2022/10/17(月) 22:46:52.95

>>881
welcome to https://mevius.5ch.net/test/read.cgi/tech/1434079972/

2022/10/17(月) 23:47:38.34

>>884
消す方だったか、"クリア"って書くのかと思ったぜ

2022/10/17(月) 23:49:43.92

>>888
甘い
空(そら)と白(しろ)で合わせて空白まで読み取らないと

2022/10/18(火) 05:15:15.98

4000件くらいのデータのExcelファイルから
A. 特定の数十件の社員データを除いたデータファイル
B. Aで除いた数十件の社員のデータのみのファイル
を作成しなければなりません

元のファイルを2つFileCopyして
・Aから対象社員のデータをUINONメソッドでRangeに格納して一括削除
・Bから非対象社員のデータをUINONメソッドでRangeに格納して一括削除

もしくは

・Aから対象社員データをAutoFillterしてBに貼り付け
→Aから対象社員データ削除

を考えてますがどちらが速いでしょう
元の書式を活かしたExcelファイルが必要なので
CSV出力やExcelファイルにデータのみ転記することはできません