Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Any Other Approaches to a 'Complex UDF'

0

I have a standalone spreadsheet that computes a result from many (20+) different inputs.

I want to get the result from that spreadsheet after changing one (or more) of the inputs. My first approach was simply to create a User-Defined Function and be able to specify in the cell something like "=BottomLine(Input18=$A3)" and letting the UDF change the input called Input18) to the value in found in $A3 and then return the result from the standalone spreadsheet.

Of course, i quickly found that Excel doesn't allow a UDF to change any cell other than the one it is in. I am aware there is a 'hack' using an Evaluate statement that will let the UDF change the value of a cell, but when that hack is used, everything works as desired EXCEPT Excel reboots and re-opens with the result. Clearly Excel doesn't like the approach, so that's not really a solution. It may be what I have to end up coding around to make it work, but...I'd prefer a better approach.

I've also tried putting the UDF in an add-in, thinking maybe Excel would let the add-in change cells. Still no dice...apparently after the UDF is called, no cells can be changed, whether the UDF is in the main project or in an add-in project.

Basically, if UDFs could change other cells, all would be good. I could treat an entire spreadsheet essentially as one large user-defined function. I can't see a way to use a UDF in or out of an add-in that Excel doesn't resist/object to. Microsoft has a good explanation of why they don't - has to do with order of calculating cells dependent on other cells. Indeed the 'hack' solution has a tendency to put Excel in an infinite loop trying to calculate cells as other cells are changed while calculating cells that influence the cell's value of the cell's changed for the cell's ....blah blah.

Can you think of another approach I might use to get results from a spreadsheet after letting user provide one input (from a list of many) that the spreadsheet will use to derive the results.

Thanks. 

If you're not familiar with the issue, here are some links:

Best 'hack' solution I've seen...

https://stackoverflow.com/questions/12501759/vba-update-other-cells-via-user-defined-function

Some of the problems people have tried to make above 'hack' work...

https://web.archive.org/web/20170511201404/http://www.excel-answers.com:80/microsoft/Excel/31583594/how-to-fill-cells-from-user-defined-function.aspx

A response voted as an 'answer' that just doesn't work...

https://social.msdn.microsoft.com/Forums/en-US/f2608171-0dbb-4386-8db2-f4c9d263cfbb/cant-select-or-add-value-to-range-or-cell-in-excel-2010-vba?forum=isvvba

Answer
Discuss

Discussion

Hi DJones and welcome to the Forum. 

Bit confused by your question (especially since you don't give the UDF code).  Suggest you edit your question to attach a representative Excel file using the Add Files...button and I'll take look tomorrow if I can (or someone else might).
John_Ru (rep: 6172) May 4, '23 at 6:25 pm
Agreed. A sample file will greatly help us in finding a solution for you. Without a sample file there are just too many unknowns.

Cheers   :-)
WillieD24 (rep: 557) May 5, '23 at 2:06 pm
DJones. I got an Alert saying your post had been updated but - if so - I can't tell how it has. Certainly there isn't an Excel file with a sample UDF. Attach one (with the method mentioned above) and I think I know a simple fix. 
John_Ru (rep: 6172) May 6, '23 at 12:46 pm
DJones,
Ditto what John has posted. I too have an idea, but without a sample file I am not going to spend any time on it.
WillieD24 (rep: 557) May 6, '23 at 1:27 pm
Thanks for looking. I just uploaded a simple file to demonstate the problem.

As I said in the OP, this is a pretty well-known and well-discussed problem elsewhere, and I was just hoping maybe someone on this board had come up with a different APPROACH...a simple way to let a user specify one or more inputs, have VBA give those inputs to a spreadsheet, and VBA return one or more results from that spreadsheet to the user. Simplest approach would be to use a UDF, but UDF's don't allow VBA to change any cell other than the UDF's cell. So, just asking if anyone has used a different approach to accomplish what it appears a UDF cannot.
DJones (rep: 2) May 6, '23 at 7:10 pm
Thanks for the file and clarification but it didn't really help me (and I see Willie retracted an attempted answer earlier). The file didn't help since I couldn't see where the optional arguments of your UDF were supposed to come from. Furthermore I couldn't discern the intention of your sample file- do you want to vary one (or more) of several variables, see the result and - if favourable- write the inputs back to cells? If so, a UserForm could be used. Just need to understand what you're trying to do.
John_Ru (rep: 6172) May 7, '23 at 9:45 am
DJones,
Thanks for uploading a sample file so we can see the structure of your UDF's.
The first thing that catches my eye is you have specified Optional arguments but you haven't specified what those arguments are.
By declaring the arguments as "variant" it could be anything  - number(s), text/string, date . . . . .  Since the formula is mathematical the variable type should be Long, Double, etc
You then use "Not IsMissing" which is boolean (yes / no). When I removed the "Not" (check if argument is missing) and used Debug.Print" it gave Error 448" - named argument not defined.
The way you have it coded, each block checks to see if the "Input" is not missing. Which means if the "Input" is there (not missing) then do the following [Cells(1, 1) = Input1]. But the "Input" IS missing so do nothing. That is why the next line of code [Cells(1, 1) = Input1] does not execute.
Excel VBA interprets your last line of code >> Result1 = Cells(5, 1) <<   as   >> Call Result1 <<
It seems the code for your UDF's needs some work.
There is also something strange happening in your file. Your UDF's are Result1 and Result2. That is also how they appear in the formula intelli-sense menu list. If I type =Result1() and press Tab or Enter it gets changed to =result(). However, if I type =Result2() it is unchanged.
WillieD24 (rep: 557) May 7, '23 at 10:02 am
@Willie - agreed about not knowing where the input are sourced but, in general, using
=result1() 
means that the UDF (here expecting up to three variables) gets no variables. The cell's value then becomes that from whatever line starts
result 1=... 
I think we need to understand what the user is trying to do
John_Ru (rep: 6172) May 7, '23 at 10:53 am
Guys...I guess the original post isn't clear. I'll try again.

I have a very complex spreadsheet. It computes multiple values as RESULTs from more than 20 different cells - let's call them INPUTs.
A UDF approach to this problem would let me specify - in the call to the UDF - none, one, or more of the INPUTs for the spreadsheet. The UDF would ideally (and THIS is the problem to be solved) change the INPUT cell of the spreadsheet for - again: none, one, or more - of the inputs specified in the UDF call. The spreadsheet would do its thing, and then the UDF would grab whatever RESULT that UDF is linked to. For example, =Result1(5,,,3) would PASS 5 to the first INPUT cell and woud pass 3 to the fourth INPUT cell of the spreadshett. It would then fetch a specifc cell from the spreadsheet and put that value into the cell that contains the UDF. Sorry, but I can't make it any clearer than that. 
continued...
DJones (rep: 2) May 7, '23 at 5:53 pm
WIllie, the arguments of the UDF are optional because the user MAY specify none, one or more arguments. Any not specified, the spreadsheet will use whatever value for that INPUT is already in the sheet. They're Variants because I want Variants specifically: some are text, some are integer, some are long, and a few can be anything. The IsMissing code is to determine what exactly the user is specifying and what he is not.   Also Willie, yes one approach would be to recode the entire spreadsheet in VBA code. But the spreadsheet is over 500 rows itself, with a LOT of computations. You've suggested two solutions - the first, subsequently removed, was to remove the inputs from the UDF call: that just redefined the problem so there is no problem. (The problem is how to pass the inputs from the UDF call to the calculating spreadsheet.) The second was to have the UDF solve the spreadsheet's computations with VBA code...the simple Input1+Input2+Input3....but the UDF example has only three inputs...there are a ton more inputs and calculations much more complex that I'd like to pass arguments to.  
DJones (rep: 2) May 7, '23 at 5:53 pm
John, thanks for looking. I can tell you're tracking with the question/problem. You said, "I couldn't see where the optional arguments of your UDF were supposed to come from." They will come from the UDF call. The user will specify those inputs when he writes the UDF. For example, in cell A1 of Spreadsheet 2 he might put '=Result1(5,,,3)'. The USER (UDF writer) has specified those inputs that he wants Spreadsheet 1 to use. Spreadsheet 1 being the spreadsheet that does the complext calculations, Spreadsheet 2 being another spreadsheet that has the UDF.) The UserForm notion was a reasonable thought, though, with the info you had. You'll see that it doesn't make sense maybe now that I've explained more...I want the UDF writer to be able to specify none, one, or more inputs and get a single result from the calculating spreadsheet and pass that back as the value of the UDF (i.e. the value of the cell that HAS the UDF in it.)    
DJones (rep: 2) May 7, '23 at 5:53 pm
Hope this helps. I know it's complex. A few other advanced VBA coders have had this problem before, as reflected in the links at the end of the original post. I posted here just in the hope that someone here might have come up with an elegant solution: Can a UDF change a cell other than the cell it is in. Microsoft's UDF function is such that the answer appears to be 'no'. (Returns #VALUE when you try!) There's a hack using Evaluate that says yes - but it's sporadic...I may end up getting it stable, and if I do, I'll try and remember to post back here. (I've already simplified the 'hack' down to its essence, and it is stable in most cases, but still iffy in other specific cases.)   
DJones (rep: 2) May 7, '23 at 5:54 pm
@DJones
It's great that you have provided some additional explanation but unfortuanately, for me, the complexity is a bit "above my pay grade" and I won't be spending any more time on this. Sorry.
@John,
Hopefully you have more experience with UDF's , and the time, to work on this further and develop a solution for DJones.
WillieD24 (rep: 557) May 7, '23 at 9:06 pm
Willie - thanks for looking.
DJones (rep: 2) May 8, '23 at 9:47 am
I've provided an aswer too but it does not use UDFs
John_Ru (rep: 6172) May 8, '23 at 9:50 am
Add to Discussion

Answers

0
Selected Answer

DJones

This illustrates a different approach to using a UDF.

In the attached revised file, column A identifies some items (inputs and results) and column B holds a numerical value. Cells B9 and B10 hold a simple formula using the 5 inputs but it could be anything.

Click the green "Vary Input(s)" button (shape) and it launches a simple UserForm via this macro (in Module 2):

Sub RectangleRoundedCorners1_Click()
    UserForm1.Show
End Sub

The bold line initialises the UserForm via the code below. You'll see it displays whatever the input names are (which you can change but not live) and shows current values in grey boxes. Change an value and press enter and that box goes white (to show which ones you have) and the new calculation values are shown.

At the top I declare some variables (whose value will be held) to simplify writing the other macros.

Dim FirstInput As Range, FirstOutput As Range, UF_EnableEvents As Boolean



Private Sub UserForm_Initialize()
    'prevent other triggers
    UF_EnableEvents = False

    ' say where the inputs and outputs start
    Set FirstInput = Sheet1.Range("A1")
    Set FirstOutput = Sheet1.Range("A9")
    ' add input labels and values
    With FirstInput
        For n = 1 To 5
            Me.Controls("Label" & n).Caption = .Offset(n - 1, 0).Value
            Me.Controls("TextBox" & n).Value = .Offset(n - 1, 1).Value
            'link to input spreadsheet cell
            Me.Controls("TextBox" & n).ControlSource = FirstInput.Parent.Name & "!" & FirstInput.Offset(n - 1, 1).Address
        Next n
    End With

    ' add result labels and values
    With FirstOutput
        For n = 1 To 2
            Me.Controls("Label" & 29 + n).Caption = .Offset(n - 1, 0).Value
            Me.Controls("TextBox" & 29 + n).Value = .Offset(n - 1, 1).Value
        Next n
    End With

    UF_EnableEvents = True
End Sub

When an input value changes, it triggers codes like this

Private Sub TextBox1_AfterUpdate()

Call VaryInput(1)

End Sub

Private Sub TextBox2_AfterUpdate()

Call VaryInput(2)

End Sub

which just pass a different number to this macro:

Private Sub VaryInput(n As Long)
    ' don't do this on initialisation
    If UF_EnableEvents = False Then Exit Sub

    Me.Controls("TextBox" & n).BackColor = vbWhite

    ' update result values
    With FirstOutput
        For n = 1 To 2
            Me.Controls("TextBox" & 29 + n).Value = .Offset(n - 1, 1).Value
        Next n
    End With

End Sub

The idea is this one bit of code can be extended or corrected easier than doing 5 or even 20+ sets of code.

If this approach appeals, it could be extended to many more inputs (with different "start" locations). You could take inputs and values from diverse places on a spreadsheet.

Note that I haven't impemented code for the button to retore values (but it wouldn't be hard to do).

MAJOR REVISION: 10 May 2023

Further to extensive discussions, the approach below allows users to write a UDF which writes values to cells in another sheet, returns a simple calcutation. The UDF formula can be editted later too.

The second attached file below (UDFake v0_a.xlsm) implements a UDF called UDFunction in cells A10 to D10 (which can be extended) and uses 3 arguments (but could be many more).

If that UDF and 3 numbers are entered in cell B10 of worksheet UDF sheet, say:

=UDF(5,2,8)

it returns value 2 which is from D6 of "Calculation sheet", whose formula is just:

=A1*B2-C3

whose values have been written as 5, 2 and 8 respectively. Enter B10 again and you can change the arguments- press enter though.

When the UDFunction is entered, it calls this in Module1:

Public Function UDFunction(Arg1 As Variant, Arg2 As Variant, Arg3 As Variant) As String

    UDFunction = "#Please Enter#=UDFunction(" & Arg1 & "," & Arg2 & "," & Arg3 & ")"

End Function

and briefly returns the formula string (which is used later) but starting "#Please Enter#".

That triggers this event macro (commented for guidance) with two public variables:

Option Explicit
Public UDFake As String, Prmpt As String

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
    If Target.CountLarge > 1 Or Intersect(Target, Range("A10:D10")) Is Nothing Then Exit Sub

    Dim n As Long, InputAdds As Variant, ResultAdd As String

    ' state addresses for inputs on calculation sheet...
    InputAdds = Array("A1", "B2", "C3")
    ' ... and where the result is found
    ResultAdd = "D6"

    Application.EnableEvents = False
    Dim Args As Variant
    If InStr(Target.Value, Prmpt) = 1 Then
        ' save the formula
        Sheet2.Range(Target.Address) = Target.Value
        ' strip out function and brackets then put arguments in an array,
        Args = Replace(Target.Value, Prmpt & UDFake & "(", "")
        Args = Left(Args, InStrRev(Args, ")") - 1)
        Args = Split(Args, ",")

        ' Write arguments to calculation sheet
        With Sheet3
            For n = 0 To UBound(Args)
                .Range(InputAdds(n)) = Args(n)
            Next n
            ' return result value
            Target.Value = .Range(ResultAdd).Value
        End With
    End If
    Application.EnableEvents = True
End Sub

You'll see that the formula string gets saved in the corresponding cell in a hidden sheet (which you might want to make xlVeryHidden if you use this approach).

Those public variables get set when a cell in A10:D10 is selected. This event code also retrieves the formula for editting:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.CountLarge > 1 Or Intersect(Target, Range("A10:D10")) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    'set a prompt string
    Prmpt = "#Please Enter#"
    UDFake = "=UDFunction"
    With Sheet2.Range(Target.Address)
        If Not IsError(.Value) Then
            If InStr(.Value, UDFake) >= 1 Then
                ' restore saved formula to formula bar without prompt string
                Target.Value = Replace(Sheet2.Range(Target.Address).Value, Prmpt, "")
            End If
        End If
    End With
    Application.EnableEvents = True

End Sub

Hope this helps. If so, please remember to mark this answer as Selected.

Discuss

Discussion

John Ru: Fantastic. Thanks so much for looking at this.
What you have written is a very nice front-end to what I've called Spreadsheet 1.  
NOW...  
I'm attaching your sheet, and I've changed my original Module 1 UDF's to use and work with your Input names. Look at Sheet 2 of the attached file. That will show you what I'm trying to do. After getting Sheet 1's inputs set up (using your uber-slick UserForm front-end), I'd like to reference that Sheet1's calculations by just changing one of it's inputs from ANOTHER set of calcuations that needs them.  

If you'll go to Sheet2, go to cell C6, press F2, and then Enter. The UDF Result1 will be triggered. I have put a STOP statement in the UDF so you can trace through what happens next. You'll see that when the UDF attempts to change Input3 on Sheet1 (in anticipation of getting Result1 and passing it back to Sheet2's cell), Excel says 'Nope - you can't do that' and returns #VALUE to C6 in Sheet2.  

A workaround or alternate approach to that problem is what I'm looking for.  

Thanks again for looking at this.  
DJones (rep: 2) May 8, '23 at 11:21 am
Thanks for the extra information but your additional sheet (and your intention) isn't clear to me. In Sheet2 row 4, you have the note "<--- these values to be used as inputs for the Sheet1 calculations…" Do you want to create a table of values (Result1 and Result2) when the item named in A4 is varied in Sheet1 using the values in C4:H4? (Incidentally your C6 cell formula 
=@Result1(,,C4)
means the UDF sees the two arguments as missing and Input3 as 1 i.e. the value of C4).

I'll do nothing more on this until you clarify (if needs be with a "story" - real world if possible- saying what you expect the user to do and get) but would add that for my example on Sheet1, you can change cells like A1 ("Input1") to whatever, "Frequency (Hz)" say, and that will show when the UserForm is next launched.
John_Ru (rep: 6172) May 8, '23 at 12:19 pm
"Do you want to create a table of values (Result1 and Result2) when the item named in A4 is varied in Sheet1 using the values in C4:H4?"
Precisely. The table's not the point, but being able to create it from the UDF IS. 

"...your C6 cell formula means the UDF sees the two arguments as missing and Input3 as 1 i.e. the value of C4)."
Precisely. Inputs 1 and 2 on Sheet 1 will not be changed from whatever values they currently have, but Input 3 will be changed to 1. And the Result(s) returned. 

You understand precisely. Did you try what I suggested and step through the UDF to see the UDF fail when it tries to change the cell at Input3? Any ideas on that?


DJones (rep: 2) May 8, '23 at 4:32 pm
In response to your paragraphs above:

Para 1:  Creating the table seems like the objective to me (and could be done fairly easily in VBA) - your users won't care if a UDF did it, just that they get the output data they want. I don't think a UDF can do it so I won't pursue it.

2. You gave the UDF argument names like Input1 etc. but they could be Arg1 etc. and the UDF would still see nothing, nothing, C4's value. If you could write to other cells from sheet 2, you'd need to specify the sheet (which you don't) , using just Cells(2,3)... without means VBA would understand the cell to be on Sheet2 since it was launched from there.

3. I did try and I don't have ideas on acheiving that.

Another approach would be to have a one or more cells with validation so the user selects an variable to test (Input3 say) and VBA loops through however many values are to the right of it, changing the other sheet and returning the results (in the table, not that cell). 

If you really want to use a UDF then good luck but I think it's a misguided approach and I can't spend more time on it, sorry.
John_Ru (rep: 6172) May 8, '23 at 6:13 pm
@DJones
I know I said I was "tapping out" but I just couldn't let it go and accept that I was beaten by syntax. (LOL) I have found a way to have the UDF update the cell by having a "button" on the sheet which calls a macro which calls the UDF. I have tested it using just 3 arguments (Inputs) and it works. (and is super simple - I am a huge promoter of the "KISS" rule)
I have read and re-read your last several posts and I am still left with some questions. I realize everything makes sense to you but that is because you have access to the full workbook; whereas we do not. So before I finalize things and post my file I would like some additional info if possible.
1) “user will specify those inputs when he writes the UDF” ? How does the user specify the inputs / write the UDF? Are the Inputs changed via UserForm as John proposed?
2) “some are text, some are integer, some are long, and a few can be anything” What is done with text inputs? What is done with numeric inputs? What is done with the other inputs Need to know all actions for the inputs and the entire formula.
3) Are all 24 arguments (inputs) always used? 
Any answers you can provide will be appreciated. I will finalize based on the additional info and then post the file.

Cheers   :-)
WillieD24 (rep: 557) May 8, '23 at 7:50 pm
@DJones - your comments on my recent post may help Willie too (and vice versa on his post) but try to respond fully please. Normally we'd expect key details to be recorded in the original question (preferably at the onset!) but the discussions might have gone too deep to do that.

@Willie - I'm sure your new ideas will help. To my mind, the non-numeric inputs will be built into the logic of the fuller worksheet (decisions, string concatenations or whatever) - that's why my approach collected the influencing parameters (inputs) and simply reported the results without trying to do the calculations in VBA. Minor thing, I don't see where 24 was set as the number of inputs.
John_Ru (rep: 6172) May 9, '23 at 5:57 am
@John,
I too suspect the text "Inputs" will be used in some sort of concatenation.
In the original post, DJones states 20+ inputs; I chose 24 because it is my favourite number. Things can get very complex very qickly. For mathematical operations the order of the Inputs is critical in order to get the correct result. With 24 Inputs, and using any 3 of the 24, there are 12,144 possibilites; using any 5 there are 5,100,480 possibilities. Then when you add in +, -, *, / the possibilities explode. Hopefully DJones will provide some meaningful answers.
WillieD24 (rep: 557) May 9, '23 at 10:15 am
@Willie - agreed on myriad permutations but we needn't about those- the right ones will be baked into the calculation sheet already so we need only to modify the inputs and report the results corresponding results. 

@DJones - back over to you for clarifications. 
John_Ru (rep: 6172) May 9, '23 at 10:25 am
Willie/John: thanks for looking at this. 

At this point I think we're just chasing our tails. You're frustrated that you're spending time trying to help and I won't (actually can't) share the specific 'user case' or examples. And I'm frustrated that I'm spending time explaining the problem which I thought was simply stated in the OP (and links where others have hit the same problem).

I'm going to spend a couple days seeing if I can resolve, and will get back to you. You've been very kind to keep trying.
DJones (rep: 2) May 9, '23 at 7:30 pm

John - re: your last comments: You understand what I'm trying to do. You don't agree with it or think it's misguided. Fine. Others on other boards have sought the same goal, so I guess there are several of us misguided.
1) No, the table is not the objective, but having a UDF (or other approach) that lets a spreadsheet builder (user) specify inputs and get results from another sheet is. ... i.e. treating a standalone spreadsheet as a 'complex UDF'...easily referenced and result(s) available to another spreadsheet that wants it.
2) Yes, of course. I understand how to specify specific cells. Just using shorthand here for these examples.
3) Thanks for that. That's the answer to the question this is all about.
DJones (rep: 2) May 9, '23 at 7:31 pm

WIllie: Your q1 - ? How does a spreadsheet builder write the UDF? specify inputs? Uh, by typing "=UDFName", then open paren, then typing in the values of the arguments for the UDF (aka inputs). Not sure I understand your q. 
q2: Really doesn't matter. q3: No. See prior discussions. Overall: I think your questions indicate you're designing a solution that would require action by user (to click the button)...that's a non-starter. The point is to fetch the result from other spreadsheet and use it ...in a formula!...of another spreadsheet. That's what UDF's do. I'd just like to use an entire spreadsheet as a UDF - but Excel doesn't permit changing cells in that (or any) spreadsheet once a UDF is called. 

Somehow that sounds familiar to me. :) 

Thanks again guys. I'll get back with what I find.
DJones (rep: 2) May 9, '23 at 7:31 pm
Please see MAJOR REVISION: 10 May 2023 to my Answer and the second file. With that you can write a UDF which writes values to cells in another sheet, returns a simple calculation. The UDF formula can be editted later too.
John_Ru (rep: 6172) May 10, '23 at 1:33 pm
John - I can't wait to dig through this....Will be back soon.
DJones (rep: 2) May 10, '23 at 5:56 pm
@DJones
Please also see my revision above - May 10 - option 3 which I suggest might be what you are looking for. Let me know.
WillieD24 (rep: 557) May 10, '23 at 11:01 pm
WillieD24 - thanks for all of your work and help on this.
Refer to John's' revision. It allows a separate spreadsheet to handle calculations instead of putting those calcs into VBA code as yours does.
DJones (rep: 2) May 11, '23 at 12:49 pm
Glad that worked for you. Thanks for selecting my Answer, DJones
John_Ru (rep: 6172) May 11, '23 at 12:49 pm
John Ru - very nice...and very clever. I was hoping for and alternate approach that would create the ability for Excel to have a 'Complex UDF' - and you have come up with one.

If there's a Hall of Fame for VBA somewhere, this code is definitely an entry.

I'd like to find a way that it doesn't look so odd when the user moves to one of the UDF cells - maybe a way to force entering edit mode (and therefore pressing the Enter key). Application.CommandBars.ExecuteMso "EditCell" doesn't work for my Excel version, and I can't find another idMSO for that simple action. "Cut" is too risky since they might NOT press enter before leaving the cell. I'll keep searching for that or something like it. Until then, this is a real help - and exactly what the doctor ordered: another approach to a Complex UDF! 

Thanks for sticking with it. I know it was a painful journey. 
DJones (rep: 2) May 11, '23 at 1:47 pm
I'm glad a simple idea paid off well.

I don't have more time to add to this but you could reduce Prmpt to just a non-printable e.g. Chr(2) to make it look less odd. 

I prefer not to dabble in Windows libraries (to capture keystrokes) but you could also use KeyUp code to change a Tab key to Enter and Tab (my memory is dull on that- search or ask a new question here perhaps and Willie or I might reply) .
John_Ru (rep: 6172) May 11, '23 at 2:06 pm
A simple Application.SendKeys "{F2}" after restoring the saved formula does the trick. I've just found SendKeys to be a little iffy in the past. So far so good for it in this application, though. 

And yes, I'll change Prmpt to something after I finalize things. Maybe even add a comment to the cell explaining what the user needs to do. It IS now a special cell, after all!

I can tell you that this is a true functionality add for Excel, and one that I predict MS will want to incorporate at some point into the product. AI applications will want / need the ability to get results from Excel spreadsheets easily (current and especially old ones that can't practically be rewritten but have valuable, old, calculations that still work) ...I'll leave it at that.
DJones (rep: 2) May 11, '23 at 2:39 pm
Add to Discussion
1

 DJones,

Updated May 10/23

I am still at a loss (and I think John too) to fully comprehend your explanations. It seems the three of us are not on the same page. Nonetheless, I forged ahead and arrived at what I think is what you are hoping to achieve. Hopefully this, at the least, has laid the building blocks for you.

First, On Sheet1, cell "A5" contains "=result1(3)" which passes the value "3" to the UDF "result1" but your UDF coding doesn't instruct what to do with that "3". As I stated early on "It seems the code for your UDF's needs some work." On Sheet1 I have included notes from Microsoft.com to support this. I have also added some comments to your code (module1) to try to explain why your original code wasn't working.

In my revised attached file (Rev 2), on Sheet2 I have provided 3 possible solutions. You have stated "require action by user (to click the button)...that's a non-starter" so the first two will not be to your liking. I left them in should others want to use those methods. So, the third option ("C16:C20") is probably what you are looking for. Row 22 also shows how the UDF's in "C" can change other cells. I have written 5 new UDF's to illustrate things (module4). As I have done, the code in the UDF must define the function. The code I wrote may/will need some additional code to check the quantity and type of data.

Cheers   :-)

Discuss

Discussion

@Willie -Well done devising this approach. 
John_Ru (rep: 6172) May 11, '23 at 2:08 pm
@John
Thanks John, but I can't take all the credit. Don's Excel - Beginner to Expert course has been a big help. In a nutshell, it all came down to what I was trying to get DJones to understand - SYNTAX, SYNTAX, SYNATX.
Cheers   :-)
WillieD24 (rep: 557) May 11, '23 at 2:42 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login