1. Registered User
Join Date
Dec 2008
Posts
2

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

2. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
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
Last edited by MikeTheBike; 12-12-08 at 10:17.

3. Registered User
Join Date
Dec 2008
Posts
2

## Complex Worksheet Formula

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.

Thanks

4. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
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

5. Registered User
Join Date
Jul 2007
Posts
61

## How to put a formula into a macro

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•