how to use vara function in vba

[arielon]

hello
I am a begginer in vba but I think I got the hang of it, in my lab we are using windows 2000, excel form 2002...

I dont understand why this code:

Sub t()
Range("c1").Value = Application.Vara(Range("a1:a5"))
End Sub

doesnt work. where, this code:

Sub t()
Range("c1").Value = Application.Var(Range("a1:a5"))
End Sub
does work. after all vara is just another statistical function supplied by excel.
the error message is 438 - doens't know the method. but I can use it on excel sheets

thank you very much for whom ever tries to help

Arielon

[mdmackillop]

Try
Range("C1").Value = Evaluate("=vara(A1:A5)")

[arielon]

thank you very much

but I am looking for a solution that would allow me to use range that is a variable for example:

Dim r As Range
Set r = Range("a1:a5")
Range("c1").Value = Application.Vara(Range(r))

this ofcourse doesn't work...

[mdmackillop]

Sub GetVara()
Dim r As Range
Set r = Range("a1:a5")
Range("c1").Value = Evaluate("=VARA(" & r.Address & ")")
End Sub


You could also use a sub to write the required VARA function into a cell

Sub GetVara2()
Dim r As Range
Set r = Range("a1:a5")
Range("C2").Formula = "=VARA(" & r.Address & ")"
End Sub

[arielon]

hi, and again thanks

is there a more direct way to use the vara method?
why is it different from using the var method?

thanks

[Bob Phillips]

How more direct do you want?

It is that way because MS in their wisdom have not exposed VARA as a worksheet function, just as they have failed to expose MAXA.

[arielon]

thanks
I was not aware to that issue I was sure that every method in excel is use-able in vba...

[Bob Phillips]

Every function is, it just might be that you need a different approach.