Complex Worksheet Formula

mamdani99 · #1 (**permalink**) 12-11-08, 11:13

Hi All,

I need a complex formula in VBA Excel. I mean that I have the given formula in my Excel sheet. Now I want to use it in VBA Excel in Text Box 3.

=TEXT(SUM(ROUNDDOWN(A1,0)*6,MOD(A1,1)*10) - SUM(ROUNDDOWN(B1,0)*6,MOD(B1,1)*10),"0")

The said formula is woking fine in Excel Sheet. How can I use the same in VBA Excel? Please help me.

Thanks

Mamdani

MikeTheBike · #2 (**permalink**) 12-12-08, 08:52

Quote:

Originally Posted by mamdani99

Hi All,

I need a complex formula in VBA Excel. I mean that I have the given formula in my Excel sheet. Now I want to use it in VBA Excel in Text Box 3.

=TEXT(SUM(ROUNDDOWN(A1,0)*6,MOD(A1,1)*10) - SUM(ROUNDDOWN(B1,0)*6,MOD(B1,1)*10),"0")

The said formula is woking fine in Excel Sheet. How can I use the same in VBA Excel? Please help me.

Thanks

Mamdani

Hi

This might do it for you

Code:

Function YourCalc(ByVal Val1 As Single, ByVal Val2 As Single) As String
    Dim Value As Single

    Value = Int(Val1) * 6 + (Val1 - Int(Val1)) * 10
    Value = Value - (Int(Val2) * 6 + (Val2 - Int(Val2)) * 10)
    
    YourCalc = Format(Value, "#0")
End Function

This should be pasted in a VB Code module.

MTB

mamdani99 · #3 (**permalink**) 12-12-08, 11:49

Thank you so much for your reply, MikeTheBike.

I copied your function in a VB Code Module. Then I used it in Sheet1 like this :
=YourCalc(a1,b2). But there is a error in the formula "NAME?.

Dear Whether I used it in a wrong way or there is some thing wrong in the Funtion.

Please help me.

Thanks

MikeTheBike · #4 (**permalink**) 12-15-08, 07:49

Hi

Not a lot do go on, but have you pasted the code in a Code Module NOT an Object module, ie. NOT IN the Sheet1 or ThisWorkbook Module?

Pasting it in either of these modules gives the #Name? error.

The only other thing I know of that would cause this error is if you have macros disabled !?

MTB

CasparV · #5 (**permalink**) 01-12-09, 17:25

How about this idea - record a macro and then look at what it has done and copy that?

First of all write down the formula that you want (which you have done in your post)

Now click on Tools and then Macro and then Record New Macro.

Then go to the sheet and cell that you want the result in and re-enter the formula (or easier still edit the existing one and make some insignificant change).

Now click on stop the macro (that's the little square button that appeared).

Now go to the macro script and have a look. You may find that you need to play with the cell referencing, but the formula structure should at least be there for you to copy.

I hope this is a simple suggestion.

Thanks,
Caspar

Internet Services

Web Development

Digital Marketing

Consumer Tech