using Excel Worksheet functions in VBscript

gtrager

unread,

Dec 13, 2001, 8:27:44 PM12/13/01

to

having gotten past problems with getting to and manipulating objects on my
Excel worksheets in the first place (thanks to Jason, Michael Harris, and
Mike Musterd), I seem to have hit another bump.

I'm trying to use the VBA code: Application.WorksheetFunction.Average(Range)

'In VBScript, I've declared;

Dim i 'just some counter index for a loop

set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbooks = xlApp.Workbooks
Set xlWorkbook = xlWorkbooks.Open(ExcelFileToOpen)
Set xlSheet = xlWorkbook.Worksheets(1)

'assuming there is data in a range and I have NumDataColumns of data:

For i = 0 To NumDataColumns - 1
'calc mean value, add to data sheet, add to data array,
'check against limit,
'if exceeds limit, set Notification flag to true and modify
'alarm message

''''''''''''''''''''
'problem here'
''''''''''''''''''''
xlSheet.Cells(lastrow + 1, 4 + i).Value =
xlApp.WorksheetFunction.Average(Range(Cells(11, 4 + i), Cells(lastrow, 4 +
i)))

'this is giving me fits, the interpreter keeps spitting back the error
message:

'Error: Type mismatch: 'Cells'
'Type: 800A000D

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'this is what I'll ultimately do with the data

CalculatedData(i, 1) = xlSheet.Cells(lastrow + 1, 4 + i).Value

If CalculatedData(i, 1) >= AlarmLimits(i, 1) Then
TrippedAlarms(i, 1) = True
Notification = True
AlarmMessage = AlarmMessage & "Average temperature of " &
AlarmLimits(i, 1) & " was exceeded (" & CalculatedData(i, 1) & ")" & Chr(10)
End If

'<snip>

Of course, I've already verified that everything works fine, when I run the
code as VBA from within Excel.

What I'm looking for help on is how in the heck do you access the Excel (or
any Office Application for that matter) methods from VBScript?

Michael Harris (MVP)

unread,

Dec 13, 2001, 9:39:03 PM12/13/01

to

You never qualify the Range and Cells properties (which happen to behave like functions)...

xlSheet.Cells(lastrow + 1, 4 + i).Value = _
xlApp.WorksheetFunction.Average(_
xlSheet.Range(xlSheet.Cells(11, 4 + i),_
xlSheet.Cells(lastrow, 4 + i)))

The problem with porting working VBA code to VBScript is that Excel (and other VBA enabled applications) exposes a lot of the app's object model implicitly such that explicit object qualifications aren't strictly needed in VBA code. You don't have that luxury in external automation clients like VBScript or even a VB app that does Office automation.

--
Michael Harris
Microsoft.MVP.Scripting

"gtrager" <gtr...@aviron.com> wrote in message news:#L4ne6DhBHA.2256@tkmsftngp04...

gtrager

unread,

Dec 14, 2001, 12:00:30 PM12/14/01

to

That was exactly it! This information has opened up a greater understanding
of the process for me. VBA makes things "almost too easy" and it's easy to
get complacent. Just knowing this, I was able to fix about sixty lines of
"problem code." I've pretty much got my script running now. Thanks again.

-George

"Michael Harris (MVP)" <mik...@mvps.org> wrote in message
news:#JhIUiEhBHA.2384@tkmsftngp03...