+ Reply to Thread
Results 1 to 6 of 6

# ask UDF with Formula Result #

  1. #1
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,185

    # ask UDF with Formula Result #

    Function Triple1(W, H) As Double
    Triple1 = W * H
    End Function
    wend i use UDF in activecell i want i activecell.offset (-1,0) result text "Formula is = W x H "
    how i can to do in UDF

    ask UDF.png

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,273

    Re: # ask UDF with Formula Result #

    A function cannot alter the spreadsheet environment, except to return a value to the cell (or cells) calling it. The way I would probably approach this is to have my UDF return an array (see example discussion here: http://www.excelforum.com/showthread.php?t=1082420 ). One element of the array is the desired text string, and the other element of the array is the result of the computation. The UDF would be array entered in this case.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,193

    Re: # ask UDF with Formula Result #

    You could put this code in a normal module
    Please Login or Register  to view this content.
    And this code in the ThisWorkbook code module
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,185

    Re: # ask UDF with Formula Result #

    mikerickson : how if i have many function array ("triple1","Rectang","Poligon","etc")
    in code bellow what must i add to function
    Public FormulaCells As Collection

    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim oneCell As Range

    For Each oneCell In FormulaCells
    With oneCell.Cells(1, 1)
    If LCase(.Formula) Like "*tripple1*" Then
    If 1 < oneCell.Row Then .Offset(-1, 0).Value = "Formula = W x H"
    Else
    Me.FormulaCells.Remove oneCell.Address(, , , True)
    If 1 < oneCell.Row Then .Offset(-1, 0).Value = vbNullString
    End If
    End With
    Next oneCell
    End Sub

  5. #5
    Forum Contributor IonutC's Avatar
    Join Date
    01-28-2016
    Location
    Bucharest, Romania
    MS-Off Ver
    Office 2019
    Posts
    486

    Re: # ask UDF with Formula Result #

    Hey Daboho,

    Please use code tags to your post #4.
    Last edited by IonutC; 12-22-2016 at 09:32 AM.
    Please consider:
    Be polite. Thank those who have helped you.
    Click the star icon in the lower left part of the contributor's post and add Reputation. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,193

    Re: # ask UDF with Formula Result #

    I just posted my code as a "how dare you say its impossible, look what I can do" challenge type thing.

    From a practical point of view, I wouldn't make this part of the UDF. It would be a far cleaner, easier and a better spreadsheet for the user to enter the header cell manually at the time that they enter the formula.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 11-27-2015, 11:12 AM
  2. Replies: 3
    Last Post: 09-25-2015, 11:34 AM
  3. [SOLVED] Code result wrong but formula correct result
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-11-2014, 12:26 PM
  4. Convert a formula result to text so another formula can find that result
    By rwoollams in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-12-2014, 01:49 AM
  5. [SOLVED] Help with Formula using variable as Date and result as integer, error in my result
    By Elomaldo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2014, 04:20 PM
  6. isblank & vlookup formula returns #n/a result and incorrect result
    By helpmeplease333 in forum Excel General
    Replies: 5
    Last Post: 05-06-2012, 11:41 PM
  7. [SOLVED] Advanced formula - Return result & Show Cell Reference of result
    By Irv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-05-2006, 10:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1