Specific VBA Calc.

Hi Forum,

Maybe a simple question but I still need some help with a formula clarification:

Could somebody help me with this:

TempCalc = CInt(TextBox3.Value) * (16*POWER(SQRT(CInt(TextBox1.Value)*(1-CInt(Textbox1.Value)))/(CInt(TextBox1.Value)*CInt(TextBox2.Value);2))

what is wrong?

Thanks in advance

TempCalc = 
CInt(TextBox3.Value) * 
(16*
POWER(SQRT(CInt(TextBox1.Value)*
(1-CInt(Textbox1.Value)))/
(CInt(TextBox1.Value)*
CInt(TextBox2.Value);
2))

Judging by the ;2 at the end, I suspect you are missing a Round function somewhere.

Oh, and I think VBA would expect a comma rather than a semi-colon.

TMS
2, in my opinion, applies POWER.

Kaizan
You have used the sheet functions (POWER and SQRT) here as if they were VBA functions. But that's just one of the mistakes. Note that some VBA functions exclude the use of a sheet function in VBA-level calculations. This is the case with the VBA - Sqr function. You cannot use the sheet function SQRT in calculations. It is a bit different with the POWER function. You can use it in calculations, but you have to indicate that it is a sheet function (you'll see in a moment). Equivalent in VBA is sign (^), e.g.

 MsgBox Application.WorksheetFunction.Power(10, 2) & vbLf & _
         10 ^ 2 & String(2, vbLf) & _
         (Application.WorksheetFunction.Power(10, 2) = 10 ^ 2)

However, the use of the SQRT sheet function will fail, e.g.

 MsgBox Application.WorksheetFunction.SQRT(100)

you must use the VBA function

 MsgBox Sqr(100)

With this knowledge, now try to write your calculations correctly.

Artik

@Artik: ah, thanks for that. POWER is not a function that I use so I couldn't see the relevance. Nor SQRT for that matter. Had worked out that it needed to be Application.WorksheetFunction.Power but was struggling with the bracketing.

OK, this compiles:

TempCalc = CInt(TextBox3.Value) * _
            16 * _
            Application.WorksheetFunction.Power( _
                Sqr(CInt(TextBox1.Value) * _
                    (1 - CInt(TextBox1.Value)) / _
                    CInt(TextBox1.Value) * _
                    CInt(TextBox2.Value)), _
                2)

And this compiles:

TempCalc = CInt(TextBox3.Value) * _
            16 * _
            (Sqr(CInt(TextBox1.Value) * _
                (1 - CInt(TextBox1.Value)) / _
                CInt(TextBox1.Value) * _
                CInt(TextBox2.Value)) _
            ^ 2)

Yes, but he asked for help and I wanted to see it corrected … even then, it might not be the calculation Kaizan wants

I'd create a Calculation Command Button and put your code in there.

Private Sub CommandButton1_Click()

Dim TempCalc As Double

Debug.Print "TextBox1: ", TextBox1.Value
Debug.Print "TextBox2: ", TextBox2.Value
Debug.Print "TextBox3: ", TextBox3.Value
Debug.Print "TextBox4: ", TextBox4.Value

Dim SqrtVal As Double
SqrtVal = Sqr(CLng(TextBox1.Value) * 1 - CLng(TextBox1.Value))
Debug.Print "SqrtVal: ", SqrtVal

Dim DivVal As Double
DivVal = (CLng(TextBox1.Value) * CLng(TextBox2.Value))
Debug.Print "DivVal: ", DivVal

Dim PowerVal As Double
PowerVal = (SqrtVal / DivVal) ^ 2
Debug.Print "PowerVal: ", PowerVal

TempCalc = CLng(TextBox3.Value) * 16 * PowerVal
Debug.Print "TempCalc: ", TempCalc
    
TextBox5.Value = TempCalc

End Sub

But, as you can see, the results are zero.

Results:

TextBox1:     4
TextBox2:     5
TextBox3:     3
TextBox4:     20
SqrtVal:       0 
DivVal:        20 
PowerVal:      0 
TempCalc:      0

Unless my maths is off, this will give you a zero value:

CLng(TextBox1.Value) * 1 - CLng(TextBox1.Value)

You can't bracket it like this:

CLng(TextBox1.Value) * (1 - CLng(TextBox1.Value))

because the multiplication would give you a minus number and Sqr won't like that.