Automating a model to run repetitively after changing one input.

[excelentkiwi]

Hi all,

I have come across this niggly little problem in excel which I will try to describe as accurately as possible in hope that someone might be able to offer me some valuable advice on how to tackle it.

I have created a complex model where by you enter an area code which prompts the model to carry out a series of calculations on data in other sheets of the model which eventually spits out an output. The model is working well and outputting what I want and expect it to, that being said each repetition of the model takes close to two minutes despite being on a reasonably good office pc. Ideally I would like to be able to leave it overnight to run the 550 different area codes through the model and have the outputs stored.

Someone else suggested to me to try data tables in excel, however when I did it only returned the area code. I believe this is to do with the fact that there is no mathematical relationship between the input and output values due to the relevant data being accessed via vlookups in the calculation process.

I have already run through the first iteration of the model and it took me two days to run it 550 times, and I will have to run it multiple times in the future so trying to find a way to speed up this protest will be highly beneficial!

I look forward to your inputs and hope there is a way forward.

Cheers,
(not so) excelentkiwi

 

[JamesCanale]

Your model is VBA free? just a really complex set of calcs that takes a couple of minutes each time? If so, this VBA code might help.

MrExcelPlayground2.xlsm
	A	B	C	D	E	F
1	Area Codes	Output
2	111	Potato111		Input		output
3	112	Potato112		143	complex model -->	Potato143
4	113	Potato113
5	114	Potato114
6	115	Potato115
7	116	Potato116
8	117	Potato117
9	118	Potato118
10	119	Potato119
11	120	Potato120
12	121	Potato121
13	122	Potato122
14	123	Potato123
15	124	Potato124
16	125	Potato125
Sheet27

Cell Formulas
Range	Formula
F3	F3	="Potato" &D3

I've made a simplified version here, where Column a is the 550 area codes, and column B will be the output. It puts a value into the input cell from column a, then waits, then pulls the data out of the output cell and puts it in the corresponding cell in column B.

Sub complexmodelautomater()
Application.ScreenUpdating = False

Dim t, areacode As Integer

For t = 1 To 550
    areacode = Cells(t + 1, 1)
    Cells(3, 4) = areacode
    Application.Calculate
    
    Do Until Application.CalculationState = xlDone
        DoEvents
    Loop
    
    Cells(t + 1, 2) = Cells(3, 6)
Next t
End Sub

 

[excelentkiwi]

Your model is VBA free? just a really complex set of calcs that takes a couple of minutes each time? If so, this VBA code might help.

MrExcelPlayground2.xlsm
	A	B	C	D	E	F
1	Area Codes	Output
2	111	Potato111		Input		output
3	112	Potato112		143	complex model -->	Potato143
4	113	Potato113
5	114	Potato114
6	115	Potato115
7	116	Potato116
8	117	Potato117
9	118	Potato118
10	119	Potato119
11	120	Potato120
12	121	Potato121
13	122	Potato122
14	123	Potato123
15	124	Potato124
16	125	Potato125
Sheet27

Cell Formulas
Range	Formula
F3	F3	="Potato" &D3

I've made a simplified version here, where Column a is the 550 area codes, and column B will be the output. It puts a value into the input cell from column a, then waits, then pulls the data out of the output cell and puts it in the corresponding cell in column B.

Sub complexmodelautomater()
Application.ScreenUpdating = False

Dim t, areacode As Integer

For t = 1 To 550
    areacode = Cells(t + 1, 1)
    Cells(3, 4) = areacode
    Application.Calculate
   
    Do Until Application.CalculationState = xlDone
        DoEvents
    Loop
   
    Cells(t + 1, 2) = Cells(3, 6)
Next t
End Sub

Hi, thanks for your response.

You're correct in the assumption the model is currently VBA free. After trying to implement the code it throw and error "run time error 6 overflow" which I am currently looking into. If this vba code does run I believe it will be perfect - its just a matter of getting it there

Any pointers on how to address this issue or its quite situational?

Cheers

 

[excelentkiwi]

For further info the code is failing in the line areacode = Cells(t + 1,1) and the page layout is the same as in your example with my values inserted of course

 

[excelentkiwi]

Hi, sorry to keep replying. I have now got the model automation to work by changing the variable type for the area code from integer to 'as long.' I believe this was necessary due to the area codes being 6 digits. After cross referencing with a previous version of the model before automation the values it is spitting out is correct. Thanks a lot for the help James!!

 

[JamesCanale]

Good morning. It all works for you then?

You know, I never declare my variables. It's my sloppy meatballer form. But I do it here because everyone on this site is so professional (and elegant in the simplicity of their code). He is a case were declaring worked against me.

When you run it, close all of your other excels - the application.calculate, might make other workbooks go nuts. I also don't think that it's strictly necessary. You might also selectively disable and reenable screenupdating so you can see progress if it's going to run all night.