VBA with goal seek takes too long

Stata15 · #1 07-20-2015, 05:49 AM

Hi,

I have got an issue with running a VBA command. The command repetes the goal seek function for all my observations (one oberservation=one line). Since I have a total of 670.000 oberservations it takes a very long time for excel to compute all the numbers.

A friend of mine helped to build the VBA command:

Code:

Public Function Zielwert()
Dim z As Integer
 
z = 2
 
For i = 1 To 500
 
  Range("EY" & z).GoalSeek Goal:=Range("K" & z).Value, ChangingCell:=Range("EW" & z)
  Range("EY" & z).GoalSeek Goal:=Range("K" & z).Value, ChangingCell:=Range("EW" & z)
 
  z = z + 1
Next i
 
End Function

I tried to run the VBA for 500 observations and for 1000. For 1000 it takes not twice as much time but way more time! And 10.000 observations do not need 10x as much but more than a night to finish.

So I need a solution for how to perform the goal seek function with VBA but a lot faster than right now. Maybe a change in the VBA command is required or several VBA commands in different sheets, since it runs faster with smaller numbers of observations?

Does anyone know what to do?
Thanks in advance!

macropod · #2 07-21-2015, 02:23 PM

First off, why do you have two of:
Range("EY" & z).GoalSeek Goal:=Range("K" & z).Value, ChangingCell:=Range("EW" & z)

As for the increase in time taken, that's probably because you're not giving the OS & Excel any housekeeping time. Try inserting the following into your loop:
If i Mod 100 = 0 Then DoEvents

Stata15 · #3 07-21-2015, 11:51 PM

Thanks a lot macropod!
The first advice reall helped to speed things up.
When I inserted the "If i Mod 100 = 0 Then DoEvents" the duration did not change.

However it still takes a really long time to compute larger sets of data.
For example I tried this for 500 observations and it took 25 seconds, but with 1000 observations it took 2:40 minutes. So the time grows exponentially. Apparently Excel has problems with larger samples.
Do you think it might make sense to conduct the goal seek function in consecutive sheets to work with smaller numbers of observations each time?
And if yes how would the loop for this have to look like?

Thanks in advance!

macropod · #4 07-22-2015, 12:22 AM

You could probably speed things up further by starting your macro with:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

and ending it with:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automatic goal seek program-Help required	calltobala	Excel Programming	0	05-08-2015 08:41 AM
Excel 2013, Problem with a shared file, takes long time to load and save and loads missing data	PStark	Excel	0	12-04-2014 07:52 AM
Excel VBA Multiple Automatic Goal Seek Required – Please Assist us	champaben	Excel Programming	13	06-22-2014 05:54 AM
set daily goal for actual workdays, excluding weekends holidays and days off	Brian Reilly	Excel	1	01-24-2014 11:56 PM
Adding a goal line to a chart	uhlersa	Excel	1	01-16-2012 08:18 PM