Excel (probably VBA) question
October 27, 2022 7:28 AM Subscribe
I have a model with that has three cells of output. With different inputs, these outputs change. I want to find a way to record all the outputs in a sheet without manually pasting all of them as values.
I'm pretty sure there isn't a way to do this with formulas (though if there is, I'd welcome it). Basically (Visualbasically....), I have three cells that we will call "results of the model" on one sheet. It's the result of a bunch of calculations that depend on the "inputs" from another sheet. I want to have all the results of the model pulled together, in rows, as values, on a third "outputs" sheet, because this is a model that will run hundreds of times and I need all of the results. I want this to happen automatically, without having to paste as values (I need it to be as error-proof as possible, as it will eventually be handed off). Pushing a button would be ok. Help?
I'm pretty sure there isn't a way to do this with formulas (though if there is, I'd welcome it). Basically (Visualbasically....), I have three cells that we will call "results of the model" on one sheet. It's the result of a bunch of calculations that depend on the "inputs" from another sheet. I want to have all the results of the model pulled together, in rows, as values, on a third "outputs" sheet, because this is a model that will run hundreds of times and I need all of the results. I want this to happen automatically, without having to paste as values (I need it to be as error-proof as possible, as it will eventually be handed off). Pushing a button would be ok. Help?
Response by poster: enter three values. push button. enter three values. push button. at first, for a project, this will be me and a colleague. at the end of the project, the model will be given to the client for more occasional use.
posted by millipede at 8:26 AM on October 27, 2022
posted by millipede at 8:26 AM on October 27, 2022
Is the model just a bunch of formulae that do some kind of essentially straightforward transformation of the inputs in order to yield the outputs, or is it a complicated iterative thing?
If the former, can those formulae exist as cells along a row on a Calculations subsheet, with that row replicated as many times as the maximum number of model runs you will need for the life of the spreadsheet?
Would "fill in the next empty row on an Inputs subsheet" be an acceptable substitute for "enter three values, push a button"?
posted by flabdablet at 9:02 AM on October 27, 2022
If the former, can those formulae exist as cells along a row on a Calculations subsheet, with that row replicated as many times as the maximum number of model runs you will need for the life of the spreadsheet?
Would "fill in the next empty row on an Inputs subsheet" be an acceptable substitute for "enter three values, push a button"?
posted by flabdablet at 9:02 AM on October 27, 2022
Response by poster: It is a complicated iterative thing.
posted by millipede at 9:10 AM on October 27, 2022
posted by millipede at 9:10 AM on October 27, 2022
Is the complicated iterative thing implemented as Excel formulae with the involvement of Solver, or in VBA or some other Excel-friendly scripting language, or in some other way?
posted by flabdablet at 11:29 AM on October 27, 2022
posted by flabdablet at 11:29 AM on October 27, 2022
Response by poster: It's formulas, but with a series of lookups to arrays that contain random numbers between a set of values that depend on the inputs.
posted by millipede at 12:21 PM on October 27, 2022
posted by millipede at 12:21 PM on October 27, 2022
I would record a macro to do this, then check the VBA of the recorded macro to make sure it will work longer term.
I would first:
1. Arrange your results cells in a row.
2. Give that range of three cells a name.
3. Create a table for the results, give it a recognizable name.
The benefit of the names is that, so long as the names aren't changed, rows and columns can be added, the worksheet name can be changed, without making the macro not work.
Then, record a macro (if you haven't done before, use the ribbon settings to enable the developer tab, make sure you are using relative references):
1. Using the "Go To" function, go to the results range.
2. Copy.
3. Use Go To to go to the results table.
4. This part I don't recall exactly how you would do it, but try the down arrow and Ctrl + End, then down again to get to the place you paste. Essentially, you want to use keyboard shortcuts to navigate in ways that don't depend on references to specific cells.
5. Paste.
6. If you always want to go back to the same place after this, use Go To again to navigate to that place (or probably just click to there).
Because macro-enabled workbooks don't always play nice and don't auto-save in OneDrive if I recall correctly, I will often store the macros in a separate document saved into the same folder. So long as it is open, the main workbook can have a button using that macro even though it is stored in the other workbook. You can use the Hide feature in the View tab to not see the macro worksheet.
Then open the VBA console and take a look at the code. If you paste it here people might be able to tell you if it will be somewhat "future-proof".
Once satisfied with it, you can insert a button also using the developer tab and assign the macro to the button.
Keeping in mind, I am BY NO MEANS AN EXPERT, and others will have more elegant solutions.
Alternatively, if you have 365, you can use the browser version of Excel to record a script. The scripts will run when the file is opened in the desktop app, without having to save as a macro. This is probably more user-friendly, and secure. However, you may not be able to accomplish all of the same things.
posted by lookoutbelow at 12:47 PM on October 27, 2022
I would first:
1. Arrange your results cells in a row.
2. Give that range of three cells a name.
3. Create a table for the results, give it a recognizable name.
The benefit of the names is that, so long as the names aren't changed, rows and columns can be added, the worksheet name can be changed, without making the macro not work.
Then, record a macro (if you haven't done before, use the ribbon settings to enable the developer tab, make sure you are using relative references):
1. Using the "Go To" function, go to the results range.
2. Copy.
3. Use Go To to go to the results table.
4. This part I don't recall exactly how you would do it, but try the down arrow and Ctrl + End, then down again to get to the place you paste. Essentially, you want to use keyboard shortcuts to navigate in ways that don't depend on references to specific cells.
5. Paste.
6. If you always want to go back to the same place after this, use Go To again to navigate to that place (or probably just click to there).
Because macro-enabled workbooks don't always play nice and don't auto-save in OneDrive if I recall correctly, I will often store the macros in a separate document saved into the same folder. So long as it is open, the main workbook can have a button using that macro even though it is stored in the other workbook. You can use the Hide feature in the View tab to not see the macro worksheet.
Then open the VBA console and take a look at the code. If you paste it here people might be able to tell you if it will be somewhat "future-proof".
Once satisfied with it, you can insert a button also using the developer tab and assign the macro to the button.
Keeping in mind, I am BY NO MEANS AN EXPERT, and others will have more elegant solutions.
Alternatively, if you have 365, you can use the browser version of Excel to record a script. The scripts will run when the file is opened in the desktop app, without having to save as a macro. This is probably more user-friendly, and secure. However, you may not be able to accomplish all of the same things.
posted by lookoutbelow at 12:47 PM on October 27, 2022
This is the type of thing you can do with a data table, or the scenario manager. (Both under data/ what if analysis). This lets you run through a complicated set of calculations for multiple values.
posted by Valancy Rachel at 1:03 PM on October 27, 2022
posted by Valancy Rachel at 1:03 PM on October 27, 2022
Valancy Rachel makes a good point too actually, which is that Excel may be (for 100s of time, very likely is) the wrong tool for this.
posted by lookoutbelow at 9:55 PM on October 27, 2022
posted by lookoutbelow at 9:55 PM on October 27, 2022
Response by poster: I know. But it has to be excel and it has to be in the format it is in for reasons that are not under my control. The problem I’ve identified is the problem I have to solve. I cannot change it to a different problem in this case.
posted by millipede at 2:58 PM on October 28, 2022
posted by millipede at 2:58 PM on October 28, 2022
When you say that the outputs need to be recorded in a sheet: what's going to consume that sheet? Does it have to be in XLSX format specifically, or would CSV do?
VBA has halfway reasonable text file handling and appending a new row of data to an existing CSV file would take very little code attached to your Go button.
If you instead use code that adds a new row of input data and output result cells to the same Excel workbook that the data gets entered into, then your user is going to need to remember to save the whole thing after every session of model runs and the likelihood of losing data becomes rather higher.
posted by flabdablet at 10:20 AM on October 29, 2022
VBA has halfway reasonable text file handling and appending a new row of data to an existing CSV file would take very little code attached to your Go button.
If you instead use code that adds a new row of input data and output result cells to the same Excel workbook that the data gets entered into, then your user is going to need to remember to save the whole thing after every session of model runs and the likelihood of losing data becomes rather higher.
posted by flabdablet at 10:20 AM on October 29, 2022
« Older Is it safe to feed cats (shelled) hard boiled egg... | dinner is a dish best served lukewarm Newer »
This thread is closed to new comments.
What's the workflow you envision for the person whose job it is to enter the data and run the model?
posted by flabdablet at 8:20 AM on October 27, 2022