マクロで｢並べ替え｣をする

Excel2007 で｢並べ替え｣をするには、以下のようなVBAのコードになります。
これは自動記録したマクロにちょこっと手を加えただけのものです。

Sub Macro1R()
    Dim shtA As Worksheet
    Dim sortA As Sort
    Dim rngA As Range

    Set shtA = Worksheets("Sheet1")
    Set sortA = shtA.Sort
    Set rngA = shtA.Range("A1").CurrentRegion

    sortA.SortFields.Clear
    sortA.SortFields.Add Key:=rngA.Item(1, 1) _
                       , SortOn:=xlSortOnValues _
                       , Order:=xlAscending _
                       , DataOption:=xlSortNormal
    sortA.SetRange rngA
    sortA.Header = xlYes
    sortA.MatchCase = False
    sortA.Orientation = xlTopToBottom
    sortA.SortMethod = xlPinYin
    sortA.Apply
End Sub

Excel2003 までの｢並べ替え｣のコードとずいぶん変わっています。
Excel2003 まではキーの数が３つまでという制限があったのですが、Excel2007 ではその制限をなくしたためにコードの方にも変更が生じたのでしょう。
　
まぁそれはいいとして、気になる点が２つあります。
　
１つ目は、一般操作のときとエラーチェックの基準が異なることです。
うっかり

    sortA.SortFields.Clear

を忘れると、｢並べ替え｣の設定がえらいことになりますが、そのときにエラーメッセージが出ないことです。
一般機能で｢並べ替え｣ダイアログで、重複した並べ替えの条件を指定すると、それを実行しようとしたときにエラーメッセージが出ます。
しかし、VBA で重複した並べ替えの条件を指定してもエラーにはなりません。

    sortA.SortFields.Add Key:=rngA.Item(1, 1) _
                       , SortOn:=xlSortOnValues _
                       , Order:=xlAscending _
                       , DataOption:=xlSortNormal
    sortA.SortFields.Add Key:=rngA.Item(1, 1) _
                       , SortOn:=xlSortOnValues _
                       , Order:=xlAscending _
                       , DataOption:=xlSortNormal

として、Apply メソッドを実行してもエラーにならないのです。
実際に、それを実行した後に｢並べ替え｣ダイアログを出すと、重複した並べ替えの条件が設定されたままになっています。
　
しかし、それを実行した後に保存して、再度そのブックを開くときにエラーメッセージが出ます。
｢並べ替え｣の条件はシートの属性として保存されます。
シートの属性に不具合があるかどうか開くときにチェックされるので、そこで初めてエラーメッセージが出るということになるようです。
　
２つ目は、ヘルプが適切じゃないことです。
一番最後の Apply メソッドについてヘルプで調べてみました。
sortA.Apply より前の部分が｢並べ替え｣の設定に関するコードで、このメソッドは｢並べ替え｣そのものを実行するものです。
しかし、ヘルプの記述では・・・

Excel 開発者用リファレンス 
Sort.Apply メソッド 
コピーした並べ替え書式を適用します。

となっています。
これではなんのことやら分かりませんね。

OnAction と引数

MOUG（Excel VBA）2008/05/27 22:42:47
『OnActionで実行するプロシージャに引数を渡すと２度実行される？』
http://www.moug.net/faq/viewtopic.php?t=28150
で、なかなかおもしろいことがあったので書いておきます。
だいたいタイトルのとおりなんですが、もう少し正確に書くと、「OnActionで実行するプロシージャに引数を渡すときに、ある書き方をすると、そのプロシージャは２度実行される」という話です。
ま、コードの書き方にちょっと問題があるだけのことなんですが…
質問者は Excel2003 で冒頭の現象が起こっているらしいのですが、Excel2002 でも再現しました。
以下は実験用のサンプルコードです。

Public Sub Sample()
　　Dim MenuA As Office.CommandBar

　　Set MenuA = Application.CommandBars("Cell")
　　MenuA.Reset
　　With MenuA.Controls.Add
　　　　.Caption = "追加Menu１"
　　　　.OnAction = "Macro1(""Test1"")"
　　End With
　　With MenuA.Controls.Add
　　　　.Caption = "追加Menu２"
　　　　.OnAction = "'Macro1(""Test2"")'"
　　End With
　　With MenuA.Controls.Add
　　　　.Caption = "追加Menu３"
　　　　.OnAction = "'Macro1 ""Test3""'"
　　End With
End Sub
　
Public Sub Macro1(ArgText As String)
　　MsgBox ArgText
End Sub

コードを見てのとおり、Sample プロシージャを実行すると、セルの右クリックメニューにリストが３つ追加されます。
そして、そのうちの"追加Menu１"をクリックした場合だけ、メッセージボックスが２度表示されます。
質問者は、１つ目の書き方をしていました。
正しい書き方は３つ目です。
ヘルプの使用例では ' は書かれていないとか、引数なしの場合は ' なしでも動くとか、↑のとおり意図したとおりにならないけどエラーにもならないとか…　ちょっとばかしややこしいです。
　
実験が終わったら、イミディエイトウィンドウででも
Application.CommandBars("Cell").Reset
を走らせて、右クリックメニューを戻しておくことをお忘れなく。
　
余談ですが、シートの書き方にもちょっと似たところがありますね。

MsgBox デバッグに御用心

Excel VBA でプログラムを作っている途中で、｢この場合、この変数に何が入るのかな？｣と確かめたくなった場合に、一時的に MsgBox を入れておいて実行し、その変数の中身を確認するということをしばしば私はやります。
　
しかし、API等から取得した文字列の場合は、注意が必要です。
文字列の途中に vbNullChar が存在すると、そこまでしか MsgBox は表示してくれないからです。
Debug.Print なら、全部見ることができます。

Sub Sample()
　　Dim X As String
　　X = "AB" & vbNullChar & "CD"

　　MsgBox X & vbCrLf & Len(X)
　　Debug.Print X
End Sub

　
　
10年ほど前に、日立の CommuniNet Extension を使ってオンライン画面の自動制御のプログラムを VB4 で作ったときに、このことを知らなくて「あっれ〜。なんで画面の文字列が取得できているはずなのに見れないんだ？」とずいぶん悩んだもんです。
画面上にフィールドが定義されていない部分がヌルキャラだったのが原因でした。

DUMMY

線が乗るセル

Excel2007 で、枠線に合わせて縦線と横線を引いてから、↓のマクロを実行してみました。

Sub Macro1()
　　Dim lineV As Shape ' 縦線
　　Dim lineH As Shape ' 横線

　　Set lineV = ActiveSheet.Shapes(1)
　　Set lineH = ActiveSheet.Shapes(2)
　　Debug.Print "◆◇◆　Macro1　◆◇◆"
　　Debug.Print lineV.Name
　　Debug.Print lineV.TopLeftCell.Address _
　　　　　　　, lineV.BottomRightCell.Address
　　Debug.Print lineH.Name
　　Debug.Print lineH.TopLeftCell.Address _
　　　　　　　, lineH.BottomRightCell.Address
End Sub

　
実行結果は以下のとおりです。
一見、不思議なところは何もないように思えます。
いや、よくよく考えると、縦線と横線の考え方の整合性がとれていません。

◆◇◆　Macro1　◆◇◆
直線コネクタ 2
$B$3　　　　　$C$6
直線コネクタ 4
$D$3　　　　　$F$3

　
念のため、同じことを Excel2002 でやってみると…

◆◇◆　Macro1　◆◇◆
Line 1
$C$3　　　　　$C$6
Line 2
$D$3　　　　　$F$3

　
Name プロパティで取得されるものが違うのはどうでもいいのですが、やはり Excel2007 での縦線がややおかしいようです。
前回、前々回にとりあげた Top,Left や Height,Width に比べれば、かわいい違いですけど。
　
P.S.
枠線に合わせて図形を配置するには Alt キーを押しながらやるのが楽チンです。

同じ場所なのに、違う位置？

Excel2007 で、開始位置が同じである縦線と横線を引いてから、↓のマクロを実行してみました。

Sub Macro1()
　　Dim lineA(1 To 2) As Shape

　　Set lineA(1) = ActiveSheet.Shapes(1)
　　Set lineA(2) = ActiveSheet.Shapes(2)
　　Debug.Print "◆◇◆　Macro1　◆◇◆"
　　Debug.Print "上：" & Range("C3").Top, "左：" & Range("C3").Left
　　Debug.Print lineA(1).Name
　　Debug.Print "上：" & lineA(1).Top, "左：" & lineA(1).Left
　　Debug.Print lineA(2).Name
　　Debug.Print "上：" & lineA(2).Top, "左：" & lineA(2).Left
End Sub

　
実行結果は以下のとおりです。
縦線の方がトンでもない位置を示しています。

◆◇◆　Macro1　◆◇◆
上：24　　　　左：96
直線コネクタ 12
上：42　　　　左：78
直線コネクタ 14
上：24　　　　左：96

　
Shape オブジェクトではなく、Line オブジェクトにしても同じ結果になります。
　
どうも、縦線ってのはダメダメのようです。

高さが幅で、幅が高さ

Excel2007 で縦の直線を引いて、↓のマクロを実行してみました。

Sub Macro1()
　　Dim lineA As Shape

　　Set lineA = ActiveSheet.Shapes(1)
　　Debug.Print "◆◇◆　Macro1　◆◇◆"
　　Debug.Print "高さ：" & lineA.Height, "幅：" & lineA.Width
End Sub

Sub Macro2()
　　Dim lineA As Line

　　Set lineA = ActiveSheet.Lines(1)
　　Debug.Print "◆◇◆　Macro2　◆◇◆"
　　Debug.Print "高さ：" & lineA.Height, "幅：" & lineA.Width
End Sub

　
実行結果は以下のとおりです。
どう見ても、Height と Width が逆なんですけど…

◆◇◆　Macro1　◆◇◆
高さ：0.1250394　　　　　　 幅：36
◆◇◆　Macro2　◆◇◆
高さ：0.125039368867874　　 幅：36

　
横線だと大丈夫なんですけどね。