var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Complex Worksheet Formula
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.
Originally Posted by
This might do it for you
This should be pasted in a VB Code module.
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")
Last edited by MikeTheBike; 12-12-08 at
Complex Worksheet Formula
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.
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 !?
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.