Excel VBA 質問スレ Part66

レス数が900を超えています。1000を超えると表示できなくなるよ。
11000
垢版 |
2020/05/10(日) 13:43:45.00ID:Nkoph0aj
ExcelのVBAに関する質問スレです
コード書き込みや作成依頼もOK

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

※デフォルト設定
2020/06/14(日) 17:08:34.92ID:zv9EPLCI
ユーザー定義関数をさ、AutoFill使って連続した複数セルに「=MyFunction()」みたいな感じで
代入していくじゃん
数式は正確に代入されてるのになぜか#VALUE!のエラーがでるんだよなぁ

計算対象になってるセルの1つをダブルクリックして値を入力するモードにしてから
そのまま何もしないでエンター押すと、さっきまで#VALUE!だったところが
再計算されて正常にセルに表示される

オートフィルに備えて連続セルの一番先頭にVBAから個別に代入しておいたセルがあるんだけど
ここはちゃんと計算結果がでてる
んで、このセルを始点にして手動でオートフィルかけて数式いれるとちゃんと計算される
手動オートフィルの結果代入された数式とVBAのAutoFill使って代入した数式を比較しても全く同じ
でもVBAのほうだけは#VALUE!ですわ

これ一体なんなのさ

一通り調べてわからんかったからAutoFillやめてForで回していれたった
やっぱ俺にはこういう原始人的なコードがあってるわ
2020/06/14(日) 17:36:38.72ID:0qngbhsm
>>805
計算対象のセルが更新された時自動で関数が処理される設定になってないから
2020/06/14(日) 17:49:03.80ID:AaZP5Hye
>>804
ワシのやり方を参考にするなら
Youtubeのチャンネル登録してな。
2020/06/14(日) 18:50:39.41ID:zv9EPLCI
>>806
手動・自動のやつでしょ
もちろん自動になってるわ
Forで回して数式代入したときは普通に計算されるから理由としてはいまいちじゃね
あと手動でオートフィルしてもいけるしね

表の右端に何列か並んでる数式は全部計算されるんだけど
下端にあるやつがだめなんだよね
どちらも全く同じパターンで代入してんだよね

まぁForでできるからいいんだけどな
2020/06/14(日) 19:19:38.26ID:0qngbhsm
>>808
シートじゃなくて関数の設定
volatire
2020/06/14(日) 20:00:28.24ID:Nip9LUw0
>>809
volatile (揮発性) じゃね？
2020/06/14(日) 20:18:20.66ID:95POjxnT
>>805
> =MyFunction()
みたいに引数がないと>>809が言うようにvolatile指定しないと再計算されない
引数にセルとか範囲を指定してあると指定されたセルや範囲の値が変更されたら再計算される
2020/06/14(日) 21:59:15.66ID:NTnc+gBz
>807
本人?。GW終わって更新控えめな気がする
2020/06/15(月) 00:54:28.92ID:otdT67Yk
フォームを開いている際にセルをクリックしようとしても阻止されるんですが、
この行動を行った際にフォームを閉じるという処理をすることはできますか？
2020/06/15(月) 00:59:25.05ID:AC0AKbql
ユーザーフォームのイベントで制御できなきゃ無理じゃね
一覧くらい見たんだろ
2020/06/15(月) 02:14:12.45ID:VIqxNPAl
>>812
この人はダメだね。
コメントでちょっと突っつかれただけで
コメント欄オフにするしプライドが高すぎ。
知識や経験が豊富で教え方も上手いとは思うけど
ユーチューバ―には向いてないと思うわ。
2020/06/15(月) 05:44:07.25ID:YCbdWLsV
>>809
>>811
Application.Volatile
入れてみた

結果だけど、やっぱボタン押して数式代入した直後はダメだった
変らず#VALUE!
変化があったのは計算対象になっているセルを1つ変更すると、
MyFunctionが入っているセルの全てが再計算されて正常値が表示されるっていう点ね
Volatile入れる前は、変更セルを計算対象としてるセル１つだけが再計算された

ちなみにMyFunction(rng As Range)です。すんません
あと、これ重くなりそうでちと考えてしまう
2020/06/15(月) 10:48:29.20ID:zn0xvlk+
>>816
ボタンのコードの後ろに、Application.Calculate とかを入れる
2020/06/15(月) 11:02:05.93ID:zn0xvlk+
>>813
フォームを閉じるにはUnload ステートメント、非表示にするだけなら hide メソッドを使う

セルクリックが目的なら、ShowModal=False か、フォームのShowメソッドで vbModeless を指定すれば、閉じなくてもいいよ
2020/06/15(月) 22:32:32.01ID:qQkMiS3I
>>816
よくわかってなくてすまぬが数式入ってるセル全部選択して
Selection.Value = Selection.Value
とか「=」を「=」に置換するとかして内容同じだけど編集したことにすると
全部計算される、ってのは根本的な解決方法じゃないけどそれは別の問題？
2020/06/15(月) 22:40:50.61ID:eWIek9um
>>819
代入で再計算させるなら formula = formula だぞ
821デフォルトの名無しさん
垢版 |
2020/06/15(月) 22:42:03.32ID:GtKitEiM
このスレの住民ってVBAエキスパートとか余裕な感じ？
2020/06/15(月) 22:58:28.87ID:MWB6z4Ac
余裕でダメだと思う。
運転歴20年のやつが、教習所の学科試験に通るかどうか。
2020/06/16(火) 01:37:43.85ID:vbVHH0Hs
練習問題を見た感じ、満点は無理だけど合格はできると思う
2020/06/16(火) 02:23:10.96ID:4czdKWOA
人によるだろう
2020/06/16(火) 08:13:35.35ID:4RAL3CE7
>>817
それ以前試してだめでしたわ
>>819
>>820
解決方法なのかどうかは置いておいて、面白い視点だなって思いました
Selection.Formula = Selection.Formula　で、問題のあった2行のうち1行は行けました
この2行の違いを分析すると何か見えてくるかもしれないなぁ

似たような方法なんだけど、これはいけた

Dim ary
ary = Sheet1.Range("G11:AH11")
Sheet1.Range("G11:AH11") = ary

数式は表の最下部の行　G50:AH50　の各セルに入っていて、
例えばG50の数式はG11を引数の一部として使っている
計算の対象となっている値の一部を代入し直してみたってこと

Forで回して数式入れていくのは見た目ごちゃごちゃするから
わかりやすさっていう点ではこっちの方がいいのかな
2020/06/16(火) 12:20:48.09ID:6wBq1IET
>>822
運転歴20年の奴は余裕で通る。
というか、はっきり言ってVBAエキスパートは初心者と変わらんよ。
某掲示板で質問者にもっと勉強してね的なこと言ったら持ってたようだった。
2020/06/16(火) 19:13:50.26ID:Kui7C9E3
リストビューなるものを触ってみましたが2点分からないです。
①データを転記する際にwith文を使うとうまく出来て、使わないと一行ずつズレるのは？
Me.ListItems.Add
.Text = 111
.SubItems(1) = 222
.SubItems(2) = 333
End With
Addして追加だからズレるのだろうけど、withって単なる省略だから同じ意味じゃないの？

②転記したリストビュー全項目ループするにはどうしたいいですか？
2020/06/16(火) 20:14:01.04ID:v7FumBBx
Doしたらいい
2020/06/16(火) 21:46:13.61ID:aBdT/SFF
いや呼び方の問題とか、FormatだのStrComp関数だのあんまり使わんの出るから模試はやったほうがいいよ
2020/06/16(火) 21:52:16.10ID:aBdT/SFF
>>827
withはコード省略じゃなくて参照。
（増やす）ってメソッド処理は一回だけで、（増えた項目）って出力インスタンスを参照できる形になる
with 使わないと毎回（増やす＋編集）の処理になる
2020/06/16(火) 21:57:13.61ID:aBdT/SFF
項目じゃなくてリストの間違いだった
2020/06/16(火) 22:40:42.45ID:woSalLPH
>>825
Selection.Calculate でもダメなんですよね？
2020/06/16(火) 23:47:36.73ID:cErksS8E
>>820
そうそう間違えました
表示形式が反映されないときに.Value使ったんでついそれ書いちゃいました
2020/06/17(水) 00:48:13.69ID:VnGmvv1H
>>833
表示形式反映させるなら.Formula = .Formula では
2020/06/17(水) 13:19:05.23ID:vz74rE0X
.Shimura = .Ushiro
2020/06/17(水) 15:24:21.32ID:3v2Pocgc
そのコマンド打ったら参照先が見つかりませんってエラーが出たんだが
2020/06/17(水) 15:55:47.59ID:F6Gn58oB
だめだこりゃ！
838デフォルトの名無しさん
垢版 |
2020/06/17(水) 17:19:39.75ID:AebhaO3Q
前スレの人達のくれた今の職場にいる限り永久に使える心臓部になるプロシージャ3つ
これのおかげで救われた
後はひたすら勉強しないといけない
ただそのせいで毎日夜中の2時とかまで強迫観念に捕らわれたように本やサイト見て体壊しかけてる
前スレの人達ありがとう
2020/06/17(水) 17:52:05.46ID:VnGmvv1H
独り言板へどうぞ
2020/06/17(水) 18:14:13.33ID:LyT08W7k
そこまで初心者の人がやらなきゃいけないのか…
専門の派遣でも雇えば…
2020/06/17(水) 18:25:49.09ID:1+VGIPZ0
>838
前スレのどれ？
2020/06/17(水) 19:41:41.88ID:3u8uTgOs
まあ嘘だろうけどな、そんなん
843デフォルトの名無しさん
垢版 |
2020/06/17(水) 20:29:26.87ID:Pc4G9CL1
前スレ見えないけど
フォルダ内のテキストすべての特定の部位から文字を全部抽出するコード
これです
844デフォルトの名無しさん
垢版 |
2020/06/17(水) 20:47:48.75ID:6I8rhd2p
車輪の再発明どころか劣化真似事をありがたがるような馬鹿は消えろよ
2020/06/17(水) 21:02:20.52ID:UkLP/htk
>>834
数式を値で張り付けたときに表示形式が反映されなかったんでValueだったんですよ
2020/06/17(水) 21:30:10.13ID:VnGmvv1H
>>845
値貼り付けでは反映されんよ
2020/06/18(木) 00:14:55.64ID:+xg8PxB0
下記のような2次元配列があります。
1,りんご
2,ばなな
3.みかん

これの果物名の列だけをセルに代入したいですが、簡単な方法はありますか？
現状、果物の列を一端列数0の配列に入れなおしてそのままセルに代入する案しか思いつきませんが、
もっとスマートな方法あれば教えてください。
2020/06/18(木) 00:29:49.38ID:b73sLWwm
行列入れ替えは考慮外なの？

参考は沢山出てくる
EXCELのVBAで２次元配列の特定の列または行を１次元配列に変換する方法
qiita.com/takahasinaoki/items/1295c3df5ddfb13bdf8e
2020/06/18(木) 01:18:10.70ID:AEhoYLDr
>>847
3だけピリオド
2020/06/18(木) 08:45:41.41ID:CbKzbr3Z
>>847
貼り付け先の果物の前の列が消されても良いなら配列から1、2、3消してそのまま貼り付ける。
駄目なら貼り付け先の値を配列に入れてから貼り付ける。

貼り付け先の値を入れるのは本末転倒だからそういうデータが少ない場合以外は勧めない。
2020/06/18(木) 09:42:50.65ID:1Z+MjfLn
>>847
地道に入れ直すしかない
2020/06/18(木) 15:10:31.12ID:JIQPCGLI
てか配列から直接セルに代入しないといけないってのは
データ数が多いとか速度重視したいとか？
そうでないなら基本通りにFor使って入れりゃいいだけなんだけど
2020/06/18(木) 15:51:42.38ID:AEhoYLDr
セルに入れるのがそもそも簡単なのに何ができないんだかわからん
2020/06/18(木) 16:29:20.31ID:DGNmlHkR
おまいら質問をちゃんと読んでるか？
「簡単な方法はありますか？」なんだから、Forで回すのは嫌ってことだろ
つまり回答は「そんな方法はない」
2020/06/18(木) 16:32:36.54ID:DGNmlHkR
ステップ数を減らしたいだけなら、一旦そのまま全部ワークシートに入れて2列目だけコピペか1行目を削除すれば数行で完結する
たぶんForより速い
2020/06/18(木) 16:33:11.78ID:DGNmlHkR
書き間違い→1列目を削除
2020/06/18(木) 17:30:14.12ID:AEhoYLDr
質問がどっかの問題の引用っぽく感じたので答えはかかないけどね
2020/06/18(木) 17:38:49.86ID:Kimfxpnn
同じ社員同士なのにエクセルの便利機能とかを
情報共有しないのってなんなんだろう
2020/06/18(木) 17:57:51.44ID:1Z+MjfLn
ループからの抜け方、①と②どっちが推奨？

①
do while
　for
　　if
　　　脱出フラグ = true
　　　exit for
　　end if
　next
　if 脱出フラグ then exit do
loop

②
do while
　for
　　if 脱出条件 then exit do
　next
loop
2020/06/18(木) 17:57:55.15ID:AEhoYLDr
嫌われているんだろ
2020/06/18(木) 17:58:44.91ID:1Z+MjfLn
>>858
今まで関わった職場わりとしてるが
862859
垢版 |
2020/06/18(木) 18:01:15.31ID:1Z+MjfLn
①の3行目のifは脱出条件な
2020/06/18(木) 18:12:03.89ID:AEhoYLDr
>>859
処理の抜けや他の処理に影響がないならという条件付きで②、
場合によっては exit sub 、exit function でも書く
2020/06/18(木) 19:24:08.22ID:LKxMxESP
do whileの条件とifの条件ををandで繋いでdo while
loop行の前でi=i+1(forのカウンタ代わり)
これでよいのでは
2020/06/18(木) 19:39:21.23ID:ck4WAMd+
GoToの使用を躊躇しない。
2020/06/18(木) 19:42:12.81ID:1Z+MjfLn
>>864
二重ループの抜け方の作法を聞いてるんだが
2020/06/18(木) 19:48:21.18ID:KBH9L3ob
do whileとforで２重ループするのが確定で、外側まで一気に抜けるのが確定なら２を選ばない理由がない
2020/06/18(木) 19:53:02.72ID:DGNmlHkR
>>866
二重ループの内側から一気に抜けたい時は、俺なら内側のループを別プロシージャにするか、Do Loopを二重にする
コード量によってはフラグ変数やExitは見通しが悪くなる、別の言い方をすれば終了条件がわかりづらくなるからあんまり使いたくない
2020/06/18(木) 20:17:54.42ID:1Z+MjfLn
>>864,867,868
とりあえず処理に問題なければ一気に外に抜けること自体は問題ないってことか
確かにDoに変えるのも手だな
適当に使い分けることにするわサンキュー
2020/06/18(木) 20:18:23.62ID:1Z+MjfLn
間違えたわ>>863だ
2020/06/18(木) 20:21:15.52ID:ao9b7VBW
>>859
囲碁なら上、オセロなら下
2020/06/18(木) 20:25:42.22ID:eqFwPDXL
すごく個人的には、Do Untilで書いた方が「抜ける！」って読みやすい
2020/06/18(木) 20:26:39.51ID:eqFwPDXL
Do whileよりって話ね

そう書けば抜ける複数条件をDoの後ろに書いて違和感が無い
2020/06/18(木) 20:28:18.94ID:XkUbux+a
これ…　>>871に『その心は？』って聞いてあげた方がいいの？
2020/06/18(木) 20:33:29.27ID:ao9b7VBW
>>874
おじいちゃん出しっぱなしで片付けないんだもん
2020/06/18(木) 20:34:24.21ID:vZk8Xm54
Gotoはもちろんのこと、余程のことがない限りはExit For(Do)使うよりはExit Function使った方がいい
2020/06/18(木) 21:01:16.76ID:ob2ctoKl
>>876
そんなこたあね―な。
2020/06/18(木) 22:48:55.15ID:Swy0cO/s
Do While
　何で
Loop

While
　2つあるの?
Wend
2020/06/18(木) 23:00:26.73ID:6Vkb8BK5
vbaだから
2020/06/18(木) 23:21:26.57ID:1Z+MjfLn
>>878
while～wendは過去の名残
今はdo whileを使うのが正しい
2020/06/18(木) 23:40:35.48ID:Cax/ofky
一応、while wendは最後までループ抜けさせないぞって読ませ方ができる
知ってる人が少なくなってるからむしろ逆効果のお節介となってきているが
2020/06/19(金) 00:17:23.49ID:NwluTwH5
行番号とか代入のLetとかも使ってるやつはいない
過去の名残があちこちにあるのがVBA
2020/06/19(金) 08:29:01.10ID:VZXzW3Q1
do while～loopって構文が気に食わんわ

do [処理] while [脱出判定] loop
もしくは
while [脱出判定] do [処理] loop
のほうが思考に沿う
ってかdo必要？
2020/06/19(金) 09:57:59.84ID:UXZn7YtP
VBA必要？
2020/06/19(金) 09:58:43.65ID:wZIpnWDZ
Excelが必要としてるんだ
2020/06/19(金) 16:55:48.19ID:8jNiNgQB
だってVBAだから
2020/06/19(金) 18:51:31.92ID:c4aN/6Uq
エクセルマクロとして代替えがないから必要だな
2020/06/19(金) 21:17:20.11ID:RlTsj/W2
職場の人が書くマクロ、わかりやすいんだけど無駄改行多くてスクロールしないと全体像掴めないでつらい
2020/06/19(金) 21:26:36.78ID:8GB492Jb
>>883
While [脱出判定]
　[処理]
Wend
2020/06/19(金) 23:55:48.34ID:+/P4LWyU
>>888
実は強制終了対策で改行してるとか?
2020/06/20(土) 00:04:01.39ID:6cdt+s4Y
>>883
構文解析でトークンの先読みが少なくて済むための配慮じゃないかな、知らんけど
2020/06/20(土) 00:41:32.09ID:YZMPfrl8
>>883
英語として

Do while a is b. If a is bigger than b, then break..

というのと

While if a is bigger than a, do something.

どちらが自然かという問題だったようだ。どっちも変だけど
2020/06/20(土) 02:09:29.21ID:qvmNBmpu
>>888
職場での問題なら職場内で解決しなされ
2020/06/20(土) 04:41:54.35ID:heakYJWv
ちょ、VBAのCollectionのAddメソッドってvalue/keyの順番だったのかよぉ
C#もJavaもPythonもkey/valueの順だから勘違いしてたわ！
なんかおかしいとおもった
2020/06/20(土) 08:01:08.69ID:ygihVcRE
>>894
Add時にKeyを省略できるからね
省略するとKeyがどうなるのかは知らんけど
2020/06/20(土) 09:40:22.78ID:heakYJWv
>>895
重複排除できてねぇぢゃねぇかクソ言語がぁぁぁ！って思ってましたごめんなさい
2020/06/20(土) 10:42:49.25ID:m9vPjHEn
>>896
重複排除したいならdictionaryの方がいいんじゃないか
2020/06/20(土) 12:12:13.86ID:RmKRgXjX
>>888
それは1プロシージャの行数が多いということかな?
それはダメダメだ。

そういうことになることはあるけど、本人が書ける奴なら自分でも分かってるかもね。
時間があれば直すんだけどってのは時々ある。
2020/06/20(土) 12:16:26.19ID:RmKRgXjX
>>894
Dictionaryと逆だから注意。
2020/06/20(土) 12:24:38.59ID:CytyTodc
excelからwordのoleなのですが、印刷プレビュー画面が表示されている
あいだは処理を停止させておきたのですが、どうすればできますか？
また、excelのほうのPrintPreviewメソッドではそれが実現できるのですが、
その違いはなんなのでしょうか。

Dim WORD As Object
Set WORD = CreateObject("Word.application")
WORD.Documents.Open(filePath)
WORD.PrintPreview = True
Do Until WORD.PrintPreview = False
Loop
2020/06/20(土) 12:44:36.40ID:ygihVcRE
>>896
重複排除したきゃKey指定すればいいだけだろ
てか、そういう使い方しかしたことないわ
2020/06/20(土) 12:57:07.60ID:m9vPjHEn
keyの位置間違えててできなかったって話だろ…
2020/06/20(土) 15:27:33.55ID:ygihVcRE
ああすまん、よく読んだらそうだな
904デフォルトの名無しさん
垢版 |
2020/06/20(土) 15:53:54.10ID:wuXUOHn9
PCに疎い人達の集まる職場

週に1回の頻度で1200個の数値を4時間かけて手打ちしてたのを3秒で終わらせるマクロ組んで改善提案→何かわからんけど組んだんだろ　で300円

他のシートやブックに行き来しやすいように
図形に文字入れてハイパーリンクをセットしたのを2つ作った
→これはいいぞ　1000円

もう嫌だ
レス数が900を超えています。1000を超えると表示できなくなるよ。
5ちゃんねるの広告が気に入らない場合は、こちらをクリックしてください。
ニューススポーツなんでも実況