配列は定数にはならない
配列を定数にすることはできるのか
配列は定数にできるのだろうか。
やってみた
標準モジュールの宣言セクションに、次のように書いてみた。
リスト１ 標準モジュールの宣言セクション
Option Explicit
Private Const CONST_ARRAY As Variant = Array("アホ", "バカ", "カス")
見てのとおり、Variant型の変数CONST_ARRAYに、Array関数を用いて配列を作成し、ぶち込むようにしてみた。
使ってみる
さっそく、次のコードで使用実験。
リスト２ 標準モジュール
Public Sub testConstantArray()
Dim i As Long
For i = LBound(CONST_ARRAY) To UBound(CONST_ARRAY)
Debug.Print CONST_ARRAY(i)
Next
End Sub
何のひねりもないコード。
定数CONST_ARRAYに配列がぶち込まれていたら、イミディエイトにその要素が順に表示されるはず。
実行
あえなくエラーｗｗｗ
おわりに
配列を定数にするのは無理っぽい。
次回は、Propertyと列挙体を併用して、配列定数っぽいことを実現してみる。
コチラもどうぞ
Workbook.LinkSourcesメソッド(Excel)
Workbook.LinkSourcesメソッド
何気なくggっていたら、WorkbookオブジェクトのLinkSourcesメソッドというものを見つけた。
MicrosoftのOffice VBA Reference：Workbook.LinkSources Method (Excel) のページによると、
Returns an array of links in the workbook. The names in the array are the names of the linked documents, editions, or DDE or OLE servers. Returns Empty if there are no links.
とのこと。
ブック内のリンクを配列で返してくれるメソッドらしい。
対象のWorkbookオブジェクト内にリンクがなければ、Empty値が返る模様。
使ってみる
このように、他ブック「test.xlsm」へのリンクを含んだブックを作る。
んで、次のコードで実験。
リスト１ 標準モジュール
Public Sub testLinkSourcesMethod()
Dim a As Variant
a = ActiveWorkbook.LinkSources(xlExcelLinks) '……(1)'
If IsEmpty(a) Then Exit Sub '……(2)'
Dim i As Long
For i = 1 To UBound(a) '……(3)'
Debug.Print a(i)
Next
End Sub
(1)の
a = ActiveWorkbook.LinkSources(xlExcelLinks)
で、Variant型の変数a（投げやりな変数名ですまん。）にLinkSourcesメソッドの返り値をぶち込む。引数のxlExcelLinksについては、コチラのXlLink Enumeration (Excel)のページをどうぞ。
対象のブックにリンクがなかった場合、LinkSourcesメソッドはEmpty値を返すので、(2)の
If IsEmpty(a) Then Exit Sub
で、配列aがEmptyだったら処理を抜けるようにしている。
あとは、(3)からの３行
For i = 1 To UBound(a) '……(3)' Debug.Print a(i) Next
で配列aの要素を取り出してイミディエイトに表示させる。
ちなみに、For i = 1としていることからもおわかりのように、実は配列のインデックスがなぜか1始まりになるので注意。
VBAでVariantの配列を返す関数・メソッドって、なぜかインデックスが1始まりになるやつがちょいちょいあるので困る。
実行
イミディエイトには、
このように表示された。
リンク元のフルパスが返された模様。
おわりに
で、何の役に立つのやら。
名前、定数、列挙体(Excel)
名前、定数、列挙体
ちょっと、PINK FLOYDっぽいタイトルだったのでつい……。
名前、定数、列挙体に同じ文字列を使ってみる
ワークシートのＡ１、Ａ２、Ａ３セルに、それぞれ「aho」、「baka」、「kasu」と名前をつけておく。
んで、次のように定数と列挙体を定義しておく。
リスト１ 標準モジュール宣言セクション
Option Explicit Private Const kasu As String = "カス" '……(1)' Private Enum Hoge aho = 1 End Enum Private Enum Hage aho baka kasu End Enum
そして、次のようなコードを用意する。
リスト２ 標準モジュール
Public Sub testNameConstEnum() Debug.Print aho '……(2)' Debug.Print Hoge.aho Debug.Print Hage.aho Debug.Print Sheet7.Range("aho").Value Debug.Print Sheet7.Range("baka").Value Debug.Print Sheet7.Range("kasu").Value Debug.Print baka Debug.Print kasu End Sub
これを実行してみる。
すると、いきなり
定数名が不適切である旨、エラーが出る。
仕方がないので、(1)の
Private Const kasu As String = "カス"
をコメントアウトして再度実行。すると、今度は
と、(2)の
Debug.Print aho
のところでエラー。
これは当り前。単に「aho」としたのでは、列挙体Hogeの方なのか、Hageの方なのかが解決できないのだから。
よって、(2)もコメントアウトして三度実行。
今度はエラーが出ずに完走し、
1
0
ち～んｗ
( ´,_ゝ｀)ﾌﾟｯ
(ﾟДﾟ)ﾊｧ?
1
2
と出力された。
結論
定数と列挙体が被るのはアウト
最初にいきなりエラーが出たことからも分かるように、定数名と列挙体の要素名が被るのはダメみたい。
実は、こないだこれでしばらくハマった。
名前と列挙体は被ってもよい
Debug.Print Sheet7.Range("baka").Value
の「baka」はセルに付けた名前、
Debug.Print baka
の「baka」は、列挙体の要素名として、ちゃんと区別されている。
「kasu」についても同じ。
定数と名前は被ってもよい
定数「kasu」のコメントアウトを解除し、列挙体「Hage」の要素「kasu」をコメントアウト、つまり、
Private Const kasu As String = "カス" Private Enum Hoge aho = 1 End Enum Private Enum Hage aho baka End Enum Public Sub testNameConstEnum() Debug.Print Hoge.aho Debug.Print Hage.aho Debug.Print Sheet7.Range("aho").Value Debug.Print Sheet7.Range("baka").Value Debug.Print Sheet7.Range("kasu").Value Debug.Print baka Debug.Print kasu End Sub
この状態で実行すると、
このとおり、意図どおりに出力された。
Debug.Print Sheet7.Range("kasu").Value
の「kasu」はセルの名前、
Debug.Print kasu
の「kasu」は定数として、ちゃんと区別されている。
おわりに
名前、定数、列挙体をうまく使い分けましょう。
Sheetオブジェクトを変数にぶち込むと自作Propertyにアクセスできない？
Sheetオブジェクトの自作Propertyが呼び出せない？
Sheetオブジェクトをぶち込んだ変数からアクセスできない
前回の
で作成したPropertyに、Sheetオブジェクトをぶち込んだ変数からアクセスしようとしたらできなかった。
リスト１ 標準モジュール
Public Sub testSheetProperty() Debug.Print Sheet3.LastRowNumber(4) '……(1)' Dim Sh As Worksheet '……(2)' Set Sh = Sheet3 Debug.Print Sh.LastRowNumber(4) End Sub
(1)の
Debug.Print Sheet3.LastRowNumber(4)
および(2)からの３行
Dim Sh As Worksheet Set Sh = Sheet3 Debug.Print Sh.LastRowNumber(4)
ともに、Sheet3オブジェクトの自作Propertyを参照して、返り値をイミディエイトに表示しようとしている。
ところが、(2)の方、すなわち、Sheet3を変数Shにぶち込むやり方の場合、そもそもコード入力の時点で、
このように、Intellisenseが働かない。
で、実行すると、
このようににべもなくエラーになる。
(2)の部分をコメントアウトして実行すると、
このように意図どおりの結果が出る。当たり前だけれど。
おわりに
なんで変数にぶち込んだらPropertyにアクセスできないんだろう？？？
追記
id:imihitoさん、thom (id:t-hom)さんからコメントをいただいて、ある程度意味が分かってきました。
Sheet3オブジェクトは、Worksheetクラスを継承した子クラス、ないしはWorksheetインターフェイスを実装したクラス、と考えたら良いっぽい。
Worksheetクラスの子クラスの場合
今回のLastRowNumberプロパティは、あくまで子クラスSheet３クラスのプロパティなので、親クラスWorksheet型の変数からは呼び出せないことになる。
Worksheetインターフェイスを実装したクラスの場合
同じく、Worksheetインターフェイスには存在しないプロパティなので、Worksheetインターフェイス型の変数からは呼び出せない。
コードの修正
したがって、上記のリスト１を次のように修正する。
リスト２ 標準モジュール
Public Sub testSheetProperty() Debug.Print Sheet3.LastRowNumber(4) Dim Sh As Sheet3 '……(*)' Set Sh = Sheet3 Debug.Print Sh.LastRowNumber(4) End Sub
変えたのは(*)のところのみ。変数ShをSheet3型にした。
確かに、
こんな風に、入力候補にSheet1、Sheet2……というのが出てくる。
実行してみると、
今度は、意図どおりの結果となった。
っていうか、これ、基本的には
これと同じことなんだよなあ。なんで気づかなかったんだろう。
SheetモジュールにPropertyを設置する(Excel)
ワークシートにPropertyを新設する
Propertyプロシージャを使う
Propertyプロシージャというと、クラスモジュールで使うという印象だが（私だけ？）、標準モジュールだろうが、フォームモジュールだろうが、シートモジュールだろうが、何ならThisWorkbookモジュールにも置くことができる。
列の最終行を返すProperty
シートモジュールに、引数で指定した列の最終行を返すPropertyを設定してみた。
今回は、Sheet3オブジェクトのシートモジュールにPropertyを新設する。あ、Sheet3ってのに深い意味はありません。実験用のブックのSheet3が今回の実験に都合が良かったというだけ。
リスト１ Sheet3モジュール
Public Property Get LastRowNumber( _
Optional ByVal columnNumber As Long = 1) As Long '……(1)'
LastRowNumber = getLastRowNumber(columnNumber) '……(2)'
End Property
Private Function getLastRowNumber( _
Optional ByVal columnNumber As Long = 1) As Long
Dim lastRow As Long
lastRow = Me.Cells(Rows.Count, columnNumber).End(xlUp).Row
getLastRowNumber = lastRow
End Function
カンタンなコードなので、特に説明はいらないと思うが、一応。
(1)の
Public Property Get LastRowNumber( _
Optional ByVal columnNumber As Long = 1) As Long
は、引数と返り値の設定。
Property Getというのは、Functionみたいなものなので、引数も指定できる。
ここでは、最終行を求める列の番号を引数としている。省略可にしており、省略されたときは「1」すなわちＡ列を指定することにした。
後は、(2)の
LastRowNumber = getLastRowNumber(columnNumber)
でFunctionを呼んで、返り値を返すだけ。
今回はメンドクサイので、最終行を求めるのに一番カンタンなEndプロパティを用いた方式を採用した。
当然、このやり方だとフィルター抽出されているようなときに意図しない結果になる。
気に入らなければFunctionの中身を変えたら良い。
実験
Sheet3は、
こんな感じ。
で、イミディエイトに
?sheet3.LastRowNumber(1) ?sheet3.LastRowNumber(3) ?sheet3.LastRowNumber(4)
と打ち込んで、それぞれ[Enter]してみる。
それぞれ、7、1、15が返るはず。
ほれ。この通り。
おわりに
シートのPropertyにしてしまうことで、コードの可読性が上がるかも知れない。
Rangeプロパティの引数に定数・変数を使う(Excel)
Rangeプロパティの引数
通常、Rangeプロパティの引数と言えば、
[親オブジェクト].Range("A1")
のように、セルの番地を指定する。
しかしながら、このやり方だと、マジックナンバー的になってしまって、不便だなあと思っていた。
セルに名前を付ける、という方法もあるのだが、あまり濫発すると、
増田さんネームド化問題
を引き起こしてしまう。
定数で指定する
そこで、まず、セルの番地を定数にぶち込んでやってみる。
リスト１ 標準モジュール
Private Const CELL_INDEX As String = "A1" Public Sub testConstant() Dim Sh As Worksheet Set Sh = ActiveSheet Debug.Print Sh.Range(CELL_INDEX).Value End Sub
ご覧のとおり、定数CELL_INDEXに、文字列A1をぶち込んでおき、
Debug.Print Sh.Range(CELL_INDEX).Value
で、A1セルの値（笑）をイミディエイトに表示させてみようという企て。
こんなふうに、A1セルに値（笑）を入力しておいて、実行してみる。
おお！ ちゃんと動いた！
変数で指定する
ならば、今度は、変数でやってみる。
リスト２ 標準モジュール
Private Const CELL_INDEX As String = "A1" Public Sub testConstant() Dim Sh As Worksheet Set Sh = ActiveSheet Debug.Print Sh.Range(CELL_INDEX).Value Dim cellIndex As String cellIndex = "A1" Debug.Print Sh.Range(cellIndex).Value End Sub
見てのとおり、今度は、変数cellIndexに、文字列A1をぶち込んで、
Debug.Print Sh.Range(cellIndex).Value
で、A1セルの値（笑）をイミディエイトに表示させようという企て。
先ほどと同じ状態で実行してみると、
なんと、あっさり動いた！
結論
Rangeプロパティのインデックスは、定数や変数でも指定することができます。
おわりに
なんで、こんな簡単なことを今までやってこなかったのだろう。
ブックを閉じて別フォルダに移動する(Excel)
Excelブックを移動する
この時期、あちこちから集めたデータ（笑）を集約するという作業が頻発する。
この手の業務は、VBAを使って瞬殺する私にとっては痛くも痒くもない。しかしながら、職場全体で見ると、この手のアホみたいな作業に膨大な時間を費やすというのが多数派。
別に人助けというわけではないけれど、最近は進んで他人のためにコードを書いている。本業の大半がクソつまらないものなので、却ってストレス解消にもなるし。
ブックを閉じて別のフォルダへ移す
近況報告はこのぐらいにして本題。
あちこちから集めたデータ（笑）を集約する、という作業に必ずつきまとうのが、
ブックを閉じる→別のフォルダに移動する
という操作。
集まってくるExcelブックが、ろくに下処理をしていない（データの入力規則を当てたり、ブックやシートの保護をかけたり、といった処理をろくにしていない）ものなので、フルオートにするよりも、
一つ一つのブックを開いて確認するところまでは手動、集約用シートへの転記以降は自動
にする方が安全なんである。
よって、「ブックを閉じる→別のフォルダへ移動する」という操作はやたら出てくるのであった。
別にその都度書いてもそれほどメンドウでもないんだけれど、メソッド化してみたというわけ。
ブックを閉じて別フォルダに移動するメソッド
リスト１ 標準モジュール
Public Function moveBook(ByVal targetBook As Workbook, _
ByVal oldFullPath As String, _
ByVal newFullPath As String, _
Optional ByVal canSaveChanges As Boolean = False) As Boolean '……(1)'
On Error GoTo errorHandler
Application.DisplayAlerts = False '……(2)'
Call targetBook.Close(SaveChanges:=canSaveChanges) '……(3)'
Name oldFullPath As newFullPath '……(4)'
moveBook = True '……(5)'
errorHandler: '……(6)'
Application.DisplayAlerts = True
If Not moveBook Then moveBook = False
End Function
まず(1)の
Public Function moveBook(ByVal targetBook As Workbook, _
ByVal oldFullPath As String, _
ByVal newFullPath As String, _
Optional ByVal canSaveChanges As Boolean = False) As Boolean
で引数と返り値を設定。
第１引数targetBookは、処理対象のWorkbookオブジェクト。
第２引数oldFullPathは、処理対象ブックの移動前のフルパス。
第３引数newFullPathは、処理対象ブックの移動後のフルパス。
第４引数canSaveChangesは、処理対象ブックを閉じるときに保存するかどうか。通常、転記処理後に上書き保存などする必要はないと思うので、省略可にして既定値をFalseにしてある。
返り値はBoolean型。無事ブックの移動が出来たらTrue、失敗したらFalseを返す。
コード自体はアホみたいに簡単なので、特に説明の必要はないと思うが、一応簡単に。
(2)の
Application.DisplayAlerts = False
で、一旦警告表示を止める。通常、転記処理では処理対象ブックに変更を加えることなどないと思うが、今回手伝った事案で処理対象ブックに加工しないと集約できない、というケースがあったのでｗ
次に(3)の
Call targetBook.Close(SaveChanges:=canSaveChanges)
で処理対象ブックを閉じる。
targetBook.Close SaveChanges:=canSaveChanges
でも良いと思うが、引数がハダカになるのはやっぱりキモチワルイので……。
「ママー、裸じゃイヤ！」みたいな感じです。
(4)の
Name oldFullPath As newFullPath
はおなじみNameステートメント。
「旧フルパス」と「新フルパス」の順序がよくごっちゃになる（いや、「As」の意味を考えたら割とすぐに分かるんですけどね！）ので、メソッドの中に閉じ込めてしまったわけですよ。
そもそも、なんで「As」なんているんでしょうねえ？ 「Name 目的語 補語」で「○○を××と名づける」なんだから、別にName [OldFullName], [NewFullName]でいいと思うんですけど。
(4)が無事実行できたら（エラーにならなければ）、無事フォルダ移動が終わったということなので、(5)の
moveBook = True
で返り値をTrueにする。
普通だったら、ここで即returnすりゃいいってもんだが、
Application.DisplayAlerts = True
を２回も書きたくないので、エラーキャッチ用のブロックである(6)からの３行を
errorHandler: Application.DisplayAlerts = True If Not moveBook Then moveBook = False
こんなふうにした。
途中でエラーが出た場合も、エラーが出なかった場合も、このブロック内を実行するようにした。
かといって、すでにブックの移動が無事に終わった場合にFalseを返されてはたまらないので、
If Not moveBook Then moveBook = False
この時点で返り値がTrueでない場合にのみFalseを返すようにした。
おわりに
まあ、メリットといえば、NameステートメントとApplication.DisplayAlertsまわりをラップしてメインのコードから隔離できることぐらいですけど。
