Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
「セル書式の範囲指定で1004アプリケーション定義またはオブジェクト定義のエラー 」(その後)
投稿日時: 18/01/03 15:00:16
投稿者: ビタミンVBA

先日、上記件名で質問させていただいた者です。とりあえず対応ができたのですが、どうもすっきりしないので、改めて自分のマクロで過去にうまく動いているものと比べてようやく理解できました(少し)。
WinArrowさんからまたコメントいただいていましたが、ありがとうございました。
 
過去、うまく動いていた例は標準マクロに記述したルーチンを使っていましたが、今回はSheet上のボタンに記述するPrivate Ssuだったことがエラーの原因(セル位置を特定できない)と理解しました。
 
マクロの目的は「2つのシートでセルに表示した項目名をセルの中央に配置し、折り返してセル内に表示する設定を行う」ことでした。このためSheet1にボタン(ActiveX)を2個置き、第1ボタンにはSheet1、第2ボタンにはSheet2を表示するように、次のようなマクロを記述しました(説明のため簡略化)。
 
(1)失敗した例(ボタン1.2はシート名が違うのみで同じ他は同じ)

Private Sub CommandButton1_Click()     'ボタン1マクロ
    Dim ShtName As String
   
    ShtName = "Sheet1"
    
    With Worksheets(ShtName)
        .Activate

        .Cells(1, "A").Value = "項目1"
        .Cells(1, "B").Value = "項目2"
        .Cells(1, "C").Value = "項目3"
        .Cells(1, "D").Value = "項目4"
        .Cells(1, "E").Value = "項目5"

    '項目名を中央にし、折り返し表示設定
        With .Range(Cells(1, "A"), Cells(1, "E"))
            .WrapText = True
            .HorizontalAlignment = xlCenter
        End With
    End With
End Sub

 
Private Sub CommandButton2_Click()      'ボタン2マクロ
    Dim ShtName As String
    Dim p As Integer, q As Integer
    
    ShtName = "Sheet2"
    
    With Worksheets(ShtName)
        .Activate

        .Cells(1, "A").Value = "項目1"
        .Cells(1, "B").Value = "項目2"
        .Cells(1, "C").Value = "項目3"
        .Cells(1, "D").Value = "項目4"
        .Cells(1, "E").Value = "項目5"

        '項目名を中央にし、折り返し表示設定
        With .Range(Cells(1, "A"), Cells(1, "E"))   '←ここでエラー1004
            .WrapText = True
            .HorizontalAlignment = xlCenter
        End With
    End With
End Sub

 
ボタン2のエラー部分を With .Range(.Cells(1, "A"), .Cells(1, "E")) とCellsの前にピリオドを
いれることで正常に動作しました(simpleさんのアドバイス)
 
(2)下記のように項目設定部分を標準モジュールに記述することで共通マクロでいけました。
Private Sub CommandButton1_Click()   'ボタン1
    Dim ShtName As String
    
    ShtName = "Sheet1"
    
    Call Koumoku_Settei(ShtName)
End Sub

 
Private Sub CommandButton2_Click()    'ボタン2
    Dim ShtName As String
    Dim p As Integer, q As Integer
    
    ShtName = "Sheet2"
    
    Call Koumoku_Settei(ShtName)
End Sub

 
'共通マクロ(標準モジュール)
Sub Koumoku_Settei(ByVal ShtName As String)
    With Worksheets(ShtName)
        .Activate
        
        .Cells(1, "A").Value = "項目1"
        .Cells(1, "B").Value = "項目2"
        .Cells(1, "C").Value = "項目3"
        .Cells(1, "D").Value = "項目4"
        .Cells(1, "E").Value = "項目5"
        
        With .Range(Cells(1, "A"), Cells(1, "E"))  'Cellsの前にピリオドなしでもOK
            .WrapText = True
            .HorizontalAlignment = xlCenter
        End With
    End With
End Sub

 
今回の結果から
(1)Private Subで特定できるセル位置はその記述されたシートのみで、別シートで特定するためには
.Range(.Cells(X,X),.Cells(Y,Y))のようにCellsのまえにピリオドを入れる。
本来はボタン1の方にもピリオドを入れるべきかと思いますが、同じシートなら特定できるから省略可能なのかと思います。
 
(2)標準モジュールに記述した場合は、変数でシートが特定できるから Cellsの前にピリオドがなくてもいける。(ピリオドを入れても動作するので、本当は入れるべき?)
 
と理解しました。過去うまくいっていたのはたまたま標準モジュールに記述したものを使っていたためと思います。セルの指定のついてはよく理解できないこともありますが、今回の件では、あやふやだったものが自分なりにすっきりしました。
 
あらためてWinArrowさん、simpleさん、ありがとうございました。
 
 

投稿日時: 18/01/03 15:01:25
投稿者: ビタミンVBA

本件は解決とさせていただきます。またよろしくお願いします。