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:

[vba]Sub t()
Range("c1").Value = Application.Vara(Range("a1:a5"))
End Sub[/VBA]

doesnt work. where, this code:

[VBA]Sub t()
Range("c1").Value = Application.Var(Range("a1:a5"))
End Sub
[/vba] 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
[vba]Range("C1").Value = Evaluate("=vara(A1:A5)")

[/vba]

[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:

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

this ofcourse doesn't work...

[mdmackillop]

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

You could also use a sub to write the required VARA function into a cell
[vba]
Sub GetVara2()
Dim r As Range
Set r = Range("a1:a5")
Range("C2").Formula = "=VARA(" & r.Address & ")"
End Sub
[/vba]

[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.