 Search:

# Why math functions do not work?

Asked By: Brayden    Date: Nov 06    Category: MS Office    Views: 3445

I got weird problem and need help:(
I worte VBA as following;

(Sub xformula()
dim i,k, q as double
q = Worksheets("input").Range("e12").Value
For i = 1 To q
k = Worksheets("input").Cells(14 + i, 6).Value
Cells(11 + i, 7).Value = 1 + (MAx(Cells(11 + i, 5).Value, 0.2) - 1) / Sqrt(k)
Next i
End Sub)

But the MAX and Sqrt functions do not work here. the system gave me error,

Share:

MAX and SQRT are worksheet functions, not VBA functions. Some worksheet
functions can be used in your VBA code, if you specify them as
Application.WorksheetFunction.FUNCTION, where FUNCTION is one of the available
worksheet functions. With this change, your code looks like this:

Sub xformula()
Dim i As Double, k As Double, q As Double
q = Worksheets("input").Range("e12").Value
For i = 1 To q
k = Worksheets("input").Cells(14 + i, 6).Value
Cells(11 + i, 7).Value = 1 + (Application.WorksheetFunction.Max(Cells(11
+ i, 5).Value, 0.2) - 1) / Application.WorksheetFunction.Sqrt(k)
Next i
End Sub

I haven't tested this, but it compiles correctly. Also, SQRT() applied to to
negative number will generate an error.

I tried, the Max function did work. but it seems the SQRT doesn't belong to
worksheetfunction and it didn't work. Are there anyway to use SQRT function?

You can use the POWER function, which does belong to worksheetfunction, as
follows:

Application.WorksheetFunction.Power(k, 0.5)

This returns the square root of k.

Try k = val(Worksheets("input").Cells(14 + i, 6).Value)

I tried. but it gave me same error  :(

The vba  equivalent of the workshet function SQRT is SQR so
......./Sqr(k)
might work.

Didn't find what you were looking for? Find more on Why math functions do not work? Or get search suggestion and latest updates.