How to show a VBA function's output in the same worksheet cell that calls it?

When used in a spreadsheet, I want the result from this VBA function to return the array value "WMKR(MinIndex)" to the same cell that it's called from, without overwriting the function call in the spreadsheet cell. For now I am using ActiveCell.Offset(0,1).Value to put the VBA function result in the adjacent column, but this is not a desireable solution. This workaround currently results in the correct answer in the adjacent cell, and also returns a "#VALUE!" error in the cell where I called "=WName()" (I guess since nothing is returned from the funtion call to that cell).

If this were a built-in Excel function, the answer to the =function() call would show in the cell, but the formula would remain in the formula box.
Can you show me how to get my =WName() VBA function to behave the same way? Thanks!

Please Login or Register  to view this content.

To return the results of the function, use

Please Login or Register  to view this content.

instead of: ActiveCell.Offset(0, 1).Value = WMKR(MinIndex)

Formulas/Functions that operate within a cell need parameters. Your function can be made to operate that way by moving the key DIMs up into the () so they can be passed in by the formula.

Here's a very basic example. I've written a UDF called COUNTPRIME that allows a range variable to fed in, this would be a range of cells. The UDF uses a SECOND function (that wasn't required, just wanted the example to be simple to read) that tests the number in each cell of the range to determine if it is a prime number or not.

Please Login or Register  to view this content.

(Source for IsPrime)

Once those two functions are dropped into a standard code module, you will be able to use them in a cell like a regular formula.


=IsPrime(A1) (only one cell allowed in this function)

=COUNTPRIME(A1:A20) (this one allows a range of cells to be fed in)

So your UDF will need to be rewritten to accept parameters the way I've shown, and the result would be fed back into the named function so that it appears in the cell from which it was called.

When done, it should look something like:

Please Login or Register  to view this content.

---

If your data presently in columns c, E & F were in adjacent columns, say, D, E & F, then X_Y_MKR would refer to a 2-d array such as D6:F21, and column one of the array would hold XMKR, COL-2 YMKR, COL 3 WMKR