ExcelVBA この書き方では長すぎるのでは？

質問

今作っているマクロが難しく書きすぎているような気がします
違うシート同士の（片方はアクティブ）セルを参照するIF文をアクティブなシートのほうに書き出すマクロなのですが、どうにか短く出来ないでしょうか？

kt = Sheets("dt").Cells(t, 13).Address(0, 0)
ki = ActiveSheet.Cells(ActiveCell.Row, 11).Address(0, 0)
ActiveSheet.Cells(ActiveCell.Row, 12).Value = "=IF(" & ki & "=dt!" & kt & ",dt!D1," & ki & "-dt!" & kt & ")"

回答No.3

KenKen_SP · ベストアンサー

こんばんは。KenKen_SP です。

Excel VBA のコードとしては、むしろ良い書き方だと思いますけど...
コードを簡潔にしたいなら VB なら、With または Set ステートメントを
活用すると良いです。

ご質問の件ですが、数式が複雑になる場合はメンテナンス性を考慮し、
こんな方法を使うこともあります。

Dim A As Range
Dim B As Range
Dim C As String

Set A = Worksheets("Sheet1").Cells(1, 1)
Set B = Worksheets("Sheet2").Cells(2, 2)

C = "=IF(AND($A=1,$B=1),True,False)" '<---- 数式が見やすくなる

C = Replace$(C, "$A", A.Parent.Name & "!" & A.Address(0))
C = Replace$(C, "$B", B.Parent.Name & "!" & B.Address(0))

Debug.Print C

それから、Value プロパティーはあくまで値を設定するプロパティーなので
数式を設定するなら Formula プロパティーを使うべきです。

Value でも結果は一緒ですが。

回答No.10

KenKen_SP

こんにちは。

> もっと基本的な問題に話を戻したかっただけなのです。

そうだったのですか。私の早とちりでとんでもない暴言を吐いてしまいま
いました。こちらこそ、お詫びします。
いつもご回答は拝見し、参考にさせていただいてます。願わくば、今後とも
よろしくお願いします。

jobvba さん、あまり参考になる回答ができず、申し訳ありませんでした。

お礼

すいません・・・数日間見放していたら大変なことになっていたようですね
とりあえず、今回の質問に関しては締め切りにさせていただきます
ありがとうございました！

回答No.9

Wendy02

KenKen_SP　さん。
すみません。そういうつもりではなかったのです。私は、高度なテクニックよりも、もっと基本的な問題に話を戻したかっただけなのです。こちらの言葉が過ぎたようで、たいへん申し訳ありません。

お礼

すいません・・・数日間見放していたら大変なことになっていたようですね
とりあえず、今回の質問に関しては締め切りにさせていただきます
ありがとうございました！

回答No.8

KenKen_SP

jobvba さん、スレを荒らして本当に申し訳なく思います。最後にします。

Wendy02 さんへ

何故そんなに絡んでくるのでしょうか？

#3 はたまたま Set ステートメントを使っただけで、そのあとに、

> C = Replace$(C, "$A", A.Parent.Name & "!" & A.Address(0))

ときてるのがご不満なのでしょうが、何度もいいますけど、これはあくまで
例示であって、解説のためのサンプルコードです。この方法は、

> 変数と＆ばかりで分からない状態でした

というご質問のニーズに応えたものです。

また、２次的なメリットとして文字列の操作なら Replace （または固定長
文字列をMid）で置換する方法は、＆演算子で連結していくよりも高速だと
いう点を述べました。

しかし、＆演算子を否定するつもりもありません。これはケースバイケース
で単純な文字列の連結ではそこまでするメリットがないからです。

> 最初に、文字列の変数として確保すべきものは確保することが大事だと思います。

#3 のコードはサンプル。先にセルのアドレスを変数にしまっておけば、
Wendy02 さんのコードと「何ら変わらない」と思いますが？
私の回答と Wendy02 さんのご回答にある違いとは文字列を ＆ で結ぶか、
Replace で置換するかという文字列操作法の違いでしか無いのですけど？

Wendy02 さんのご回答をちゃんと理解してるし、至極妥当なコメントだと
思います。最初から異論はありませんと言ってるでしょう？

> 私のお話が、KenKen_SPさんに通じなければ、私の書いている内容は、
> jobvbaさんには通じないかもしれませんね。

> 理解されないかもしれませんが。

私はともかくご質問者の名前まで引っ張り出して、何が言いたいのです？
「回答を理解する力がないので言っても意味がないけども」とでも言い
たいのでしょうか？

回答No.7

Wendy02

jobvbaさん、KenKen_SPさん。

こんばんは。Wendy02です。

一応、理解していただけるか分かりませんが、私の#1 で書いていたことを、サンプルとして提示しておいたほうがよいかと思います。理解されないかもしれませんが。

この二つのコードは、ほぼ同じです。結果は同じです。
オブジェクトのプロパティをから、アドレスを取得していることです。
最初に、文字列の変数として確保すべきものは確保することが大事だと思います。

'--------------------------------------------------
Sub Case1()
Dim t As Integer
Dim kt As String
Dim ki As String

For t = 1 To 100

kt = Sheets("dt").Cells(t, 13).Address(0, 0)　'M1
ki = ActiveSheet.Cells(ActiveCell.Row, 11).Address(0, 0) 'K1

ActiveSheet.Cells(ActiveCell.Row, 12).Value = _
　 "=IF(" & ki & "=dt!" & kt & ",dt!D1," & ki & "-dt!" & kt & ")"
ActiveCell.Offset(1).Select

Next
End Sub
'--------------------------------------------------
Sub Case2()
Dim i As Long
Dim t As Integer
Dim kt As String
Dim ki As String

i = ActiveCell.Row
t = 1　　'←この値のとる方法は様々だと思います。
kt = "M" & t
ki = "K" & i

With ActiveSheet
　 .Cells(i, 12).Resize(100).FormulaLocal = _
　 "=IF(" & ki & "=dt!" & kt & ",dt!$D$1," & ki & "-dt!" & kt & ")"
End With

End Sub

'---------------------------------------------------

回答No.6

Wendy02

KenKey_SPさん、こんばんは。
Wendy02です。

私のお話が、KenKen_SPさんに通じなければ、私の書いている内容は、jobvbaさんには通じないかもしれませんね。

>> オブジェクトにアクセスしていたら、どうみても、プロシージャとしての効率が良くないように思います。

>コードの生産性については異論がないようなので処理効率とは処理速度のことだとして、本当にそう思いますか？ 処理速度というものはコード全体を見渡さないとわからないものです。

書法のスタイルが守れているかどうかだと思います。私は、「オブジェクトにアクセスする」と書いております。あくまでも、Excelのワークシートに対するものです。

ここでは、オブジェクトとは、ワークシート上の、Range オブジェクトやシートオブジェクトです。VBAの最適化ルールの一つに、「ワークシートへのアクセス回数を減らす」という原則はご存知ですよね。

文字列処理の場合に、そこにワークシート上のオブジェクトからプロパティを取り出し、それをループして、そこから、アドレスを一回一回取っていたら、どんなに、コードとしては問題がなくても、そこに無駄が生じてしまいます。よくするミスとしては、Select と並んで、ループ内で、オブジェクトの取得です。

不必要な、ワークシートのオブジェクトのアクセスは必要ないはずで、文字列やインクリメントの数値で処理できるものは、可能な限り、プロシージャの中だけで済ませればよいわけです。それ以上のテクニックに関しては、必要性を感じないのは、慣れの問題だと思っているからです。

全体をみないで何が分かるか、というお叱りはごもっともかもしれませんが、例えば、元のご質問のコードの場合、ktと、ki の取得の部分について、シートから取得する必要は、ActiveCell.Row を変数に入れさえすれば、おそらくないはずだと思うのです。おまけに、これは、相対参照式なので、ループを想定していたとしたら、そのループさえ必要ないかもしれません。

以下のような場合は、どちらがいいのかは、言うまでもありませんよね。

Dim buf As String
For i = 1 To 65535
　　buf = buf & CStr(ActiveSheet.Cells(i, 1).Row)
Next　i

Dim buf As String
For i = 1 To 65535
　　buf = buf & CStr(i)
Next　i

あくまで、これは極端な例で、これ自体を有効性がないと否定はしないでくださいね。ただ、こういうことで、ある人は、エラーがないのに、なぜそのようなことを言うのか、と激怒しました。ただ、オブジェクトのプロバティの文字列情報などを取り出すのは、必要最小限にしなければならない、そう私は思うのです。

もし、余計なことを書いているようでしたら、すみません。

回答No.5

KenKen_SP

こんにちは。jobvba さん、Wendy02 さん。ご質問から少し脇道に入りますが
お許し下さい。

> それは、本当ですか？

本当ですよ。しかし、#3 はあくまでサンプルで、あくまで私の意見を述べた
だけであって、それが一般的です...とは述べてません。

実際には #3 コード程度の文字列ならそのまま & 演算子でつなげちゃいます。

しかし、コードの生産性という点では Wendy02 さんも異論は無いようですし、
それがご質問主の期待するところだと思いますので、一つの方法としてコメント
させていただきました。

> オブジェクトにアクセスしていたら、どうみても、プロシージャとしての
> 効率が良くないように思います。

コードの生産性については異論がないようなので処理効率とは処理速度のこと
だとして、本当にそう思いますか？ 処理速度というものはコード全体を見渡
さないとわからないものです。

#3 はあくまで「コードの生産性」重視のコードですが、それだけでは処理速度
が犠牲になりすぎるので、それをカバーする意味で ＆演算子を排除したコード
が必要になってます。

速度重視で #3 のコードをちゃんと書けば、Wendy02 さんのコードと比較して
もオブジェクトへのアクセスが増えても 10万回の文字列生成テストで 1 秒内外
の差しかありません。誤差の範囲ですよね...

コードを書いた目的が違うのですから、短絡的に #3 は効率的とは言えません
が「非効率」とも言えません。

処理速度という点なら、ご存知のとおり、VB の ＆ 演算子による文字列連結
は非常に低速です。例えば（例示なので大げさですが）10万回ループさせて
「教えて！Goo」という文字列を連結する処理。

’＆ 演算子を使ったコード
For i = 1 To 100000
　　Buf = Buf & "教えて！Goo"
Next i

これだと遅すぎて環境により数十秒～数十分ぐらいかかると思いますが、

' Replace を使ったコード
Buf = String$(100000,"a")
Buf = Replace$(Buf,"a","教えて！Goo")

とすると 1 秒もかかりません。Replace は結果に必要なメモリを先に確保す
るから高速ですが、対して ＆演算子による文字列の連結はループの都度メモリ
の再確保を行うので、どうしたって速度がでません。

低速な ＆演算子を可能な限り排除したコードを書く...これはむしろ VB だか
らこそ覚えておきたい良く知られたテクニックです。...が、面倒なら ＆
演算子でちょちょっと連結してしまって OK。必要がなければわざわざ面倒な
書き方をしなくて良いのです。Wendy02 さんのご回答に異論は全くありません。

いかがでしょうか？

回答No.4

Wendy02

KenKen_SPさんへ
こんにちは。Wendy02です。

>ご質問の件ですが、数式が複雑になる場合はメンテナンス性を考慮し、
>こんな方法を使うこともあります。

それは、本当ですか？
私は、どちらが正しいとかいう議論をするつもりはありませんが、私は数式の文字列生成で、そのようなことはしたことがありません。SQL を作る時などは、結構、苦労しますが、その場合でも、Debug.Print に出して確認しながら、コツコツと作っています。

誰しも、最初は、ワークシートの数式の生成などは、なんとかならないのかなって思ったと思いますが、その内に慣れて出来るようになるものだと思っています。

複雑な数式とは言っても、そのマクロの結果でしかないわけで、デバッグをせずに一回きりで成功せよっていうわけではないでしょうから、プロシージャ内の作業効率を求めても、それに大した意味があるとは思えないのです。ループして、その都度、オブジェクトにアクセスしていたら、どうみても、プロシージャとしての効率が良くないように思います。一部分では、制作上の効率があがっても、結果としては、役に立っていないか、もしくはマイナスになったとしたら、その部分は、我慢するしかありません。

私は、他の言語は置いておいて、その辺りは、慣れてもらうしかないと思っています。いつも、こういう意見は、質問者さんに対して、不愉快にさせるだけに終わるのですが。

回答No.2

Wendy02

こんばんは。

書法のスタイルについて気にされるのは、プログラミングに、かなり自信を持っておられるからだと思いますが、そういうレベルを気にされる前に、

kt = Sheets("dt").Cells(t, 13).Address(0, 0)
ki = ActiveSheet.Cells(ActiveCell.Row, 11).Address(0, 0)

こういう内容は、初歩的な問題だと思いますが……。もう少し、基礎的な部分を固めたほうがよいと思います。

格好が気になるのなら、ユーザー定義関数でも作ったらどうでしょうか？

ただ、私個人は、VBAで、こういうところに手を掛けてもしょうがないと思います。このような、変数を使ったテキスト生成にスマートも汚いもないと思います。VBA(VB)から、他のステージに書き出すものは、みんな同じようなものです。

お礼

いやいやお恥ずかしい限りです！
独学でやっていたもので、基礎部分が他の言語に頼ってしまいがちになっています
回答ありがとうございました

回答No.1

Wendy02

こんにちは。

>違うシート同士の（片方はアクティブ）セルを参照するIF文をアクティブなシートのほうに書き出すマクロなのですが、どうにか短く出来ないでしょうか？

別に、長くもありませんし、短くする必要性を、なぜ考えるのか私には理解できないのですが、格好良さだったら、せいぜい、「_ （アンダーバー)」で改行するぐらいですね。

ActiveSheet.Cells(ActiveCell.Row, 12).Value _
　　　 = "=IF(" & ki & "=dt!" & kt & ",dt!D1," & ki & "-dt!" & kt & ")"

Value プロパティを、FormulaLocal にしようが、それ自体は、別にかまいません。

ただ、全体が見えていないのではっきり言えませんが、おそらく、t をインクリメンタル（増加していく)して、ループしているようにみえるのですが、短く書くよりも何よりも、.Address(0, 0)　と、相対参照ですから、それぞれの式の中のセル間の距離が同じなら、ループする必要はありません。式を書き込むところの範囲を参照して、同じ式を貼り付ければよいです。ただし、",dt!D1," →　",dt!$D$1," となります。

それは、コード全体をみないと、はっきり言えませんが。

それに、以下は、シート名はいりませんね。どこで書こうが、一定のセルを参照しているわけではありませんから、文字列を作り出してしまえばよいです。

i = ActiveCell.Row
kt = "M" & Cstr(t)
ki = "K" & Cstr(i)

それから、ActiveCell.Row　は、変数に入れます。そして、以下は、このようになります。

ActiveSheet.Cells(i, 12).Value _

ループ内では、ActiveCell は、使わないほうがよいです。その分、オブジェクトを取得するので遅くなってしまいます。

補足

回答ありがとうございます！
単に今回は、IF文が、
>= "=IF(" & ki & "=dt!" & kt & ",dt!D1," & ki & "-dt!" & kt & ")"
パッと見て、変数と＆ばかりで分からない状態でした
確かC言語には、printf("%s",変数);（うろ覚えですが）のようなスマートな書き方で記述できたと思います
スマートに書けないものでしょうか？