Function, sub or something else for specific problem

Dear all,

Under link: https://imgur.com/a/Hc2nP you can see image from simple excel file (overview of what I would like to get).

I would like to make excel file in which I will have a lot of inputs (and later on I would like to have possibility to add more inputs). In VBA I would like to make code for specific calculations which will be using all inputs and computing few values. For eg. I would like to print into excel cells few of this calculated values. I made two cases:

First with functions. I do not like that solution because if I will print into cells 100 values I need to have 100 functions and change each exactly the same function except one function.

Second with Sub. Here on the other hand I have one code, but I need to click button to execute calculations and I do not like this because I would like to execute calculations on the fly like in case of functions (change in input variable equals automatic change in each output).

Could you give me a tip what is best way to do this?
Best regards,
Pawel

Pawel

Why do you have (almost) identical functions, output1 and output2?

I don't see very much to go on to give specific suggestions. A few questions:
1. What does this mean?

I do not like that solution because if I will print into cells 100 values I need to have 100 functions and change each exactly the same function except one function.

I am not sure what your concern here is.
I could see combining your 2 functions into one single function with an additional input value.

Please Login or Register  to view this content.

If you are unfamiliar with the select case statement: https://msdn.microsoft.com/en-us/VBA...case-statement
2)

I suppose that with the function I can display only one variable?

UDF's that return only one value are easier to code, but UDF's are not limited to returning single values. A UDF can return an array of values, similar to the built in functions LINEST() and FREQUENCY(). I have an example here discussing how to code an array function UDF: https://www.excelforum.com/excel-pro...-function.html

I guess at this point I see two somewhat related questions -- how to add a toggle to a UDF so that you can specify which output of several possible outputs the UDF returns, or how to code a UDF so that it can output multiple results. Am I understanding correctly? Is there something I am misunderstanding?

Maybe there is an option of an automatic Sub without a button?

There are event procedures (change and calculate event seem applicable): http://what-when-how.com/excel-vba/a...-in-excel-vba/ and/or http://www.cpearson.com/Excel/Events.aspx

An interesting problem. I don't know that I have a good, final answer to it. I know that I sometimes struggle with this same kind of question. Is it better to program one large procedure with multiple toggles and decision trees and branches to handle every possible scenario that I expect to encounter, or to have several smaller procedures where each one is programmed to handle a specific part or branch of the task. I don't know that there is a single "best" answer to this question. In my own programming, I find myself kind of uncomfortable with the idea of a single large procedure that expands with each new wrinkle I throw at the problem. I would be interested in other experienced programmers' thoughts on this, but I can see some value in carefully planning how you want to code the procedure for the current project and make it expandable for future projects.

Part of this might be about what you mean by "expand". For example, you have a "table" of compounds and molecular weights hard coded into the procedure. Sometimes, "expand" might mean simply that I want to be able to use more or fewer compounds in the list. In those cases, I might prefer to have the table of compounds in the spreadsheet (perhaps identified with a suitable named range). Then, when I want to expand the list of compounds, I don't need to edit the VBA code, I can simply edit the table of compounds and the code can respond accordingly.

At this point, I might suggest that you step back and look at the project as a whole. What task(s) does it need to perform now? How will it need to expand?

Pawel

Crazy idea here, but why not explain what you are actually trying to do?

And whilst you are at it, please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
  
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.