CODEWITHSUNDEEP
vbaexcel
Michael Sliva
Michael Sliva

Reputation: 33

Wrong Data Type in Function

I'm getting the ever so annoying "A value used in this formula is the wrong data type." error and I can't for the life of me figure out what is causing it.

Public Function Cc(q As Double, ID As Double) As Double

Dim YP As Double            'lb/100ft^2
Dim Fan600 As Single
Dim Fan300 As Single
Dim n As Double             'Flow behavior index
Dim K As Double             'Consistency index

Dim Form As Worksheet
Set Form = ThisWorkbook.Worksheets("ECD Generator")

'=========================================================================================================

YP = Form.Cells(5, 10).Value
Fan600 = Form.Cells(6, 10).Value
Fan300 = Form.Cells(7, 10).Value

'=========================================================================================================

n = 3.32 * WorksheetFunction.Log10(Fan600 / Fan300)
K = 5.1 * Fan600 / (1022 ^ n)

Cc = 1 - (1 / (2 * n + 1)) * (YP / (YP + K * ((3 * n + 1) * q / (n * WorksheetFunction.Pi * (ID / 2) ^ 3)) ^ n))

End Function

Upvotes: 0

Views: 365

Answers (2)

Gary Evans
Gary Evans

Reputation: 1890

With the values you supplied in the comments I got a different error of

16: Expression too complex

Information on this error can be found here. Basically there is too much math happening in a single computation.

To get round this I broke Cc it into two parts and this got past the error. I realise this is not the error you displayed in the question but there is a good chance they are related.

Try the below replacement of the Cc line of code

Cc = n * WorksheetFunction.Pi * (ID / 2) ^ 3
Cc = 1 - (1 / (2 * n + 1)) * (YP / (YP + K * ((3 * n + 1) * q / Cc) ^ n))

To confirm, this is replacing the single line beginning Cc = with two lines.

Upvotes: 3

Chris R. Timmons
Chris R. Timmons

Reputation: 2197

Running this code in Excel 2013, the Cc = ... expression throws an "Expression too complex" error. Simplify the expression by using temporary variables:

n = 3.32 * WorksheetFunction.Log10(Fan600 / Fan300)
K = 5.1 * Fan600 / (1022 ^ n)

'Add these temp variables to make the "Cc = ..." expression less complex.

Dim t1 As Double
t1 = 1 / (2 * n + 1)

Dim t2 As Double
t2 = n * WorksheetFunction.Pi * (ID / 2) ^ 3

Cc = 1 - t1 * (YP / (YP + K * ((3 * n + 1) * q / t2) ^ n))
'Cc = 1 - (1 / (2 * n + 1)) * (YP / (YP + K * ((3 * n + 1) * q / (n * WorksheetFunction.Pi * (ID / 2) ^ 3)) ^ n))

Upvotes: 2

Related Questions