STDEV function in VB scritp for excel
 

I am writing a VB script in excel and I am trying to use the STDEV function.
I have worked through several errors but I am stumped on this last one:

"Run time error 1004
Unable to get the StDev property of the Worksheetfunction class."

I am a novice at VB so any help would be appreciated.

STDEV function in VB scritp for excel
 

VB (standalone language) or VBA (supplied with Excel)?
What version of Excel?
Post your code.

In very old versions of Excel (pre Excel97, & possibly Excel97 as well), you
would need to use Application.StDev() instead of WorksheetFunction.StDev()

Jerry

"dbwells" wrote:

I am writing a VB script in excel and I am trying to use the STDEV function.
I have worked through several errors but I am stumped on this last one:

"Run time error 1004
Unable to get the StDev property of the Worksheetfunction class."

I am a novice at VB so any help would be appreciated.

STDEV function in VB scritp for excel
 

Yes VBA Supplied with Excel
Excel 2002 sp3
application.stdev() does work.
Sheets("Sheet1").Cells(Nrow, 3).Value = Application.StDev("D" & Srow & ":D"
& Erow - 1)

Nrow is the row number of the summary worksheet.
Srow is the starting row of the number group.
Erow - 1 is the end row of the number group.
Erow is the row number of the next number group.

I now get a #VALUE! on the summary worksheet but this is a lot further than
I was getting before.

thanks for your help.

"Jerry W. Lewis" wrote:

VB (standalone language) or VBA (supplied with Excel)?
What version of Excel?
Post your code.

In very old versions of Excel (pre Excel97, & possibly Excel97 as well), you
would need to use Application.StDev() instead of WorksheetFunction.StDev()

Jerry

"dbwells" wrote:

I am writing a VB script in excel and I am trying to use the STDEV function.
I have worked through several errors but I am stumped on this last one:

"Run time error 1004
Unable to get the StDev property of the Worksheetfunction class."

I am a novice at VB so any help would be appreciated.

STDEV function in VB scritp for excel
 

Your code produces errors because it does the equivalent of using
=STDEV("D5:D6")
instead of
=STDEV(D5:D6)
The arguments to STDEV must be numbers, arrays, or ranges, not strings. Try
StDev(Range("D" & Srow & ":D" & Erow - 1))
with either Application or WorksheetFunction as the prefix. Excel 2002
supports both prefixes, but WorksheetFunction has advantages so long as you
do not need the code to run in old versions of Excel.

Jerry

"dbwells" wrote:


Yes VBA Supplied with Excel
Excel 2002 sp3
application.stdev() does work.
Sheets("Sheet1").Cells(Nrow, 3).Value = Application.StDev("D" & Srow & ":D"
& Erow - 1)

Nrow is the row number of the summary worksheet.
Srow is the starting row of the number group.
Erow - 1 is the end row of the number group.
Erow is the row number of the next number group.

I now get a #VALUE! on the summary worksheet but this is a lot further than
I was getting before.

thanks for your help.

STDEV function in VB scritp for excel
 

What if all I want to do is copy or use an existing VBA 6.5 file (with old
data) in Excel 2003 into a new file to be able to enter new current data into
the new file?

In a new file which I copied from the old Excel 2003 VBA 6.5 file I used
Clear Contents to delete all data but the first two lines as sample data but
now get "Run-time error '1004': "Unable to get the Match property of the
WorksheetFunction class".

I double-checked the VBA code on the prior file - all is the same - is it
the range? I updated the range but that didn't work either.

"Jerry W. Lewis" wrote:

Your code produces errors because it does the equivalent of using
=STDEV("D5:D6")
instead of
=STDEV(D5:D6)
The arguments to STDEV must be numbers, arrays, or ranges, not strings. Try
StDev(Range("D" & Srow & ":D" & Erow - 1))
with either Application or WorksheetFunction as the prefix. Excel 2002
supports both prefixes, but WorksheetFunction has advantages so long as you
do not need the code to run in old versions of Excel.

Jerry

"dbwells" wrote:


Yes VBA Supplied with Excel
Excel 2002 sp3
application.stdev() does work.
Sheets("Sheet1").Cells(Nrow, 3).Value = Application.StDev("D" & Srow & ":D"
& Erow - 1)

Nrow is the row number of the summary worksheet.
Srow is the starting row of the number group.
Erow - 1 is the end row of the number group.
Erow is the row number of the next number group.

I now get a #VALUE! on the summary worksheet but this is a lot further than
I was getting before.

thanks for your help.