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
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
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.
Originally Posted by shg
You could put this code in a normal module
And this code in the ThisWorkbook code modulePlease Login or Register to view this content.
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.
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
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks