How to use Excel worksheet functions in VBScript?

Question

I need to count the number of active cells in column A of Excel.
I can achieve this easily using 'worksheetfunction.countA' in Excel VBA, but unable to get the same in VBScript.

I have tried the following code:

Dim objXl , objWorkbook, objSheet ,numofactivecells

Set objXl = createobject("Excel.Application")

set objWorkbook= objXl.Workbooks.open("C:\Users\Username\Desktop\filename.xlsm")
'change filename

set objSheet = objWorkbook.Worksheets(1)

objXl.visible = true

objsheet.cells(1,1).select

numofactivecells = objsheet.WorksheetFunction.CountA(Range("A:A"))

msgbox numofactivecells

I need count of cells containing data in column A stored in variable.

I get the following error messages when I execute the code:

Microsoft VBScript compilation error: Expected identifier

Microsoft VBScript compilation error: Expected ')'

Étienne Laneville Étienne Laneville · Accepted Answer · 2019-10-19T16:41:41

A few mistakes:

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

Here's code that will work:

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

How to use Excel worksheet functions in VBScript?

1 Answers