一些错误:

1. WorksheetFunction是Excel.Application对象的方法，而不是Worksheet.
2. Range不能单独使用，它是Worksheet对象的方法.

1. WorksheetFunction is a method of the Excel.Application object, not Worksheet.
2. Range can't be used by itself, it's a method of a Worksheet object.

下面的代码将起作用:

Dim objXl
Dim objWorkbook
Dim objSheet
Dim iActiveCells

Set objXl = CreateObject("Excel.Application")
Set objWorkbook = objXl.Workbooks.open("C:\Temp\test2.xlsx") 'change filename
Set objSheet = objWorkbook.Worksheets(1)

objXl.Visible = True

With objSheet
    .Cells(1, 1).Select
    iActiveCells = objXl.WorksheetFunction.CountA(.Range("A:A"))
End With

MsgBox iActiveCells