【VBA】定数にできる内容は決まっていますか？

Question

以下に、一文が長い記述があります。

Sheets(ReslutSheet).Cells(gyo, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

これは、指定したシートの指定したセルに、コピーした文字列を貼り付けるコードです。
これに似た記述が複数個所出てきます。同じような記述はなるべく排除し、
同じ文字列は定数で宣言して、コードを短くしたいと考えています。

Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
↑例えば、この一文を定数にして
Const Paste1 As String = "Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False"
Sheets(ReslutSheet).Cells(gyo, 5).PasteSpecial Paste1
と書くと、
「インデックスが有効範囲にありません」や、
「RangeクラスのPasteSpecialメソッドが失敗しました。」と出てしまいます。

このような定数の指定はできないのでしょうか。
アドバイスを頂けたら幸いです。

WindFaller · Accepted Answer

#3の回答者ですが、VBAを嗜む人でしたら、常識なことですが、わかりやすく書いてあげたほうがよいかもしれません。

Sheets(ReslutSheet).Cells(gyo, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
　　
　　↓

Sheets(ReslutSheet).Cells(gyo, 5).PasteSpecial 12, xlNone　'数式と数値の書式＊

'／／
*必ず、コメントを入れておきます。そうしないと、コードが読めなくなってしまいます。名前付き引数と組み込み定数は、省略しようと思えば、上記のようにできるということです。

これは、VBAの問題集などには出た覚えがあるのですが、メリット・デメリットがあります。

Excelは、スクリプト言語だから、文字列は少なくすべきだとして、極端な書き方をする人がいます。しかし、スクリプト言語だから文字列は少なくするという考え方は間違いです。Excel VBAでも、れっきとしたコンパイルをしています。

次に、パラメータは、省略できる場合とそうでない場合があります。
名前付き引数で　Paste:=,Operation:= などを使うと、順序を考えなくても使えますが、それを使わないと、順序は決まっていることと、Transpose:=True とする場合などは、

.PasteSpecial 12,xlNone,,True

と順序を崩さずに、省略したところでも、カンマをなくさずに書かなければなりません。
しかし、省略して、思わぬ弊害をもたらすことがありますから、省略した時は十分に気をつけたほうがよいです。省略がデフォルトではなく、前回使った時に設定ということがあります。

こうした書き方は、可読性(Readability)を落とすものですから、昨今、あまり書く人は少なくなってきているようです。ただ、入門や初級レベルの人は、つとめて、そのような書き方は避けたほうがよろしいのではないかと思います。

WindFaller · Answer

こんばんは。後、残るツールは、私のような者では使いこなせない部分もありますが、でも、Office VBAのプログラムを書く人にとって、今のスタイルが続く限りは、避けて通れないものがあります。それが、この2つです。 API Viewer　(いまさら、Win32 APIというのは古臭くて問題かもしれません。) http://www.activevb.de/rubriken/apiviewer/index-apiviewereng.html#anchor3 もうひとつは、リボンエディターです。 Office Ribbon Editor http://www.ka-net.org/ribbon/ri63.html　(解説サイト) (ダウンロードはここから：↓ http://download.cnet.com/Office-Ribbon-Editor/3000-7241_4-75628723.html >知らないことだらけで挫けそうですが最近は、みんな、こういうのは教えなくなった気がします。教わったものは、いずれまた、誰かに教えていくという巡り合わせです。 >インデント、すぐに崩れてしまってこれが綺麗に揃えば、VBAも、格段に読めるようになって、すぐに上手するようになります。 (ネットに投稿する時には、全角スペースを入れて、インデントを入れたようにしてあります。) '*****Start Text Export*******(これもテンプレート) add_date 2015/12/25 Dim fNo as Integer Dim fn as String 　fNo = FreeFile() 　　fn = "22-56.txt" '<-filename 　　Open fn For Output As #fNo 　　Print #fNo, strLine '<--rewrite valuable 　　Close #fNo 　　Beep 　　Stop '*****End Text Export******* ↑ 全角スペースが2つ、後でいれます。いままで、私の知っている中では、新しいものを求める人は、必ず進歩しています。常に飽きずに新鮮な気持ちでいられる人は上達も早いです。そういう私は、覚えが悪かったです。なかなか思ったようにはいかなかっです。VBAは分かりにくいからです。

WindFaller · Answer

#5のWindFallerです。

返事を頂きましたので、ありがとうございます。

>思わぬ弊害をもたらしまくりそうなので、

すみません、私は、ちょっと言い過ぎました。Excelはほとんど気にしなくて良いです。
Excel以外にも、MS-Word、Outlook、Access, PowerPoint　など、似ていて仕様の違う部分があります。
この中で、Wordが一番やっかいかもしれません。

それと、インテリセンス(VBE入力中に、ヒントや組み込み定数など出してくれる機能)は、Microsoftプログラミングだけに近いです。だから、発展的に考えれば(＝Microsoft 製品以外のプログラミングを考えるなら)、パラメータで数字だけの方法も考えなくてはなりませんね。さしずめ、Wscript などは、ふつはテキストエディタで書かなくてはなりません。

>すらすら書けるようになりたいです。

そのためには、暗記ですね。でも、私などは、覚えたつもりでも、すぐに忘れてしまいます。そこで、私の入力の秘密を教えてしまいます。それは、よく使うものはテンプレートに登録して使ってしまうのです。

Ctrl + Shit + F と押すと、以下のコードが一瞬に出てきます。
中身を整えたら、コメントアウト(')を取り去ります。
このようなショートカットをいくつも用意しています。

'Dim c As Range
'Dim FirstAddress As String
'With Selection
'Set c =.Find ( _
' What　　　　　　 := 検索値, _
' After　　　　　　:= 指定したセルの次から検索(省可), _
' LookIn　　　　 :=　 xlFormulas　　/ xlValues/ xlComments , _
' LookAt　　　　 :=　　xlPart(2)　　/ xlWhole(1), _
' SearchOrder:= xlByColumns /　　xlByRows , _
' SearchDirection:= xlNext / xlPrevious (規定 xlNext), _
' MatchCase　　:= True / False (既定値は False) '大文字・小文字の区別, _
' MatchByte　　:=　True　　/ False 半角と全角を区別 _
)
'　　　　 If Not c Is Nothing Then
'　　　　　　FirstAddress = c.Address
'　　　　　　　　Do
'　　　　　　　　Set c = .FindNext(c)
'　　　　　　　　If c.Address = FirstAddress Then Exit Sub
'　　　　　　Loop Until c Is Nothing
'　　　　 End If
'End With

アドインの名前は、VBA MZ-Tools 3.0 というものです。

http://zabi.0am.jp/?p=2676
本家は、8.0だけで有償になってしまいましたので、上記で手に入るはずです。

http://www.mztools.com/blog/category/mztools30/　　本家

それと、渡邉ひかるさんの、VBE Plus でコードのインデントなどをつける整形プログラムを使っています。

http://www.vector.co.jp/soft/win95/prog/se176543.html

これは、少なくともExcel 2010でも支障なく動きます。

googoo900 · Answer

本題からそれるかもしれませんが、コードを短くしたいということであれば。

【元の記述】
Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

【短縮した記述】
Paste:=xlPasteValuesAndNumberFormats

上記のようにできるのは、下記が省略可能だからです。
Operation:=xlNone　･･･　「演算」しない
SkipBlanks:=False　･･･　「空白セルを無視」しない
Transpose:=False　･･･　「行列入れ替え」しない

WindFaller · Answer

こんばんは。

>このような定数の指定はできないのでしょうか。
それは、できませんね。だって、String型ですから。パラメータの付け方は決まっています。意味をなさないではありませんか。

>同じ文字列は定数で宣言して、コードを短くしたいと考えています。

私が、VBAを習って１～2年目の頃に、某所で、そういう組み込み定数を使うべきかという全体を2分するような議論がありました。

今でこそ、私は、「組み込み定数は省略してはならない」というのが、コーディングルールだから、守ることだと言いますが、そもそも、「コーディングルール」など、VBAで守るべきものなどあるか、と開き直ってしまえばおしまいで、プロでもならないかぎりは、大目に見ていくしかないわけです。

Sheets(ReslutSheet).Cells(gyo, 5).PasteSpecial 12, xlNone  '←なぜかxlNoneはそのまま。数値は-4142

だから、プライベートでは、このように組み込み定数を使わずに書いてしまうわけです。それに、Microsoft 自身(Helpの中で)も、そんな書き方をしている時がありますね。

慣れている人は、代表的な組み込み定数を暗記しています。例えば、MsgBox のvbInformation (64), vbCritical (16), vbExclamation(48) などです。

他人からみたら、代表的なもの以外は、可読性が落ちるのと、バージョンアップした時に、果たして、その内容が維持できるか分からないとはいわれますが、まったくないという書き方ではありません。
気をつけるのは、Win32APIの戻り値がVB/VBAタイプとは違うことがありますから、要注意です。

それに、Microsoft のプログラミングには、VBEditorに、インテリセンスといって、入力候補のリストが出てきて選択できる機能が備わっていますから、暗記して使うほどにはないと思います。

なお、それら省略する以外の方法は、ご自由に（^^;

gao57830 · Answer

まずは、RangeオブジェクトのPasteSpecialメソッドを確認してみてください。

https://msdn.microsoft.com/ja-jp/library/office/ …

式 .PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
このように４つの引数を取ります。

サンプルのプログラムを見ると、
＞Const Paste1 As String = "Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False"
＞Sheets(ReslutSheet).Cells(gyo, 5).PasteSpecial Paste1

.PasteSpecial "文字列"
となっているので、これでは無理なのは理解してもらえると思います。

また、４つの引数は全て省略可能なので、（）で括らず
「Paste:=」「Operation:=」「SkipBlanks:=」「Transpose:=」
として、どの引数かを指定しています。

以上を踏まえてどこが定数に出来るか考えてみてください。
ちなみに、正式なPasteSpecialの宣言は以下になります。
Function PasteSpecial( _
<InAttribute()> Optional ByVal Paste As XlPasteType, _
<InAttribute()> Optional ByVal Operation As XlPasteSpecialOperation, _
<InAttribute()> Optional ByVal SkipBlanks As Object, _
<InAttribute()> Optional ByVal Transpose As Object _
) As Object

RandenSai · Answer

一番安直に短くする方法は、関数化することでは？以下みたいに。これはテストしてないのでエラーになるかも知れませんが、考え方は分かるでしょう。

Sub test(ByVal gyo As Long, ByVal retsu As Long)

Sheets(ReslutSheet).Cells(gyo, retsu).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

こんな感じにしておけば、使うときには test(5,10) などで済みます。実際にはtestなんて名前じゃなくてもっと気の利いた名前にすると、より解りやすくなるでしょう。

【VBA】定数にできる内容は決まっていますか？

#3の回答者ですが、VBAを嗜む人でしたら、常識なことですが、わかりやすく書いてあげたほうがよいかもしれません。

こんばんは。

#5のWindFallerです。

本題からそれるかもしれませんが、コードを短くしたいということであれば。

こんばんは。

まずは、RangeオブジェクトのPasteSpecialメソッドを確認してみてください。

一番安直に短くする方法は、関数化することでは？以下みたいに。

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング