Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Posts
    2

    Unanswered: Complex Worksheet Formula

    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. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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
    Last edited by MikeTheBike; 12-12-08 at 10:17.

  3. #3
    Join Date
    Dec 2008
    Posts
    2

    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.

    Thanks

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #5
    Join Date
    Jul 2007
    Posts
    54

    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
  •