VBA code limit in MS Excel 1

[JensKKK]

I wote a VBA script in MS Excel to perform a custom curve fit. The program became quiet lengthy and recently I encountered error messages that the Solver Add in experienced an error or is low in memory.

I tested the program on different computers with different amounts of memory (ranging from 128 - 750 MB) and it seems that the physical amount of memory is not the limiting factor or causing the problem.

Has anybody here experienced that VBA can't execute code when the macro becomes too long?

Any help is appreciated.

 

[xlbo]

won't be anything to do with length of code - almost certainly it will be multiple iterations where an object is not released from memory e.g.

For i = 1 to 1000

Set myRange = Range("A" & i)

Next i

Sets a range object 1000 times but never releases it from memory. To do so, you need to set it to nothing eg

For i = 1 to 1000
'initialise object
Set myRange = Range("A" & i)
'release object
Set myRange = nothing

Next i

My guess is that if you are using solver, you are performing lots of iterations and something is not being released from memory

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JensKKK]

I am using the solver resetcommand at the start of the calculation and at the end of each calculation.

SOLVERReset

I think that resets all solver settings and avoids.

What is the biggest VB macro that you have written in in MS Excel?

 

[JensKKK]

Thanks for your reply Geoff,

just to give you some facts that main macro and subroutines thereof that does the calculation contains 405 lines of code (1665 words).

I get less error messages when I reduce VBA code, but as soon as I add more code. I run into trouble and VBA shows "strange" error messages. Strange, because when I delete VBA code in a different part of the program the errror message disappear, secondly VBA shows me error message for parts of my code that I didn't edit and this part of the code was running previously.

Thanks for sharing your thoughts with me.

 

[xlbo]

Not sure what the most code in 1 module is for me but I have had a look at a few I have written and that kind of length and that number of words is not particularly big.

Without you sharing the code (particularly if you SET any variables and the beginning / end of loops) I can't really help any more. the Amount of code is not overly long although if it is all in 1 macro, you may be better off splitting it up into multiple sub procedures / functions more for ease of readability and debugging than anything else.

I know that the solver uses a lot of internal memory so you do need to be careful about instantiating and releasing objects though..


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Another thing to try is HELP - Detect & Repair.

Yet another, reconstruct the workbook. Copy the code and paste into new modules in the new workbook. Do not Export/Import the modules.

Debug/Compile.

Skip,

_[red][/red]

 

[JensKKK]

Geoff,

can I make the code visible to you.

Should I just post it? Or is there a better way?

Thanks

 

[xlbo]

Post the code although if you have 405 lines, please don't post it all.

Please post:

All Declaration of variables

Start and End of each sub

start and end of each loop

You can use "Code" tags to allow for easier reading

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JensKKK]

Skip,

I can't see Detect & Repair in the help menu.

(I am runiing MS Excel 2002 SP3).

Any suggestions where I should look?

R.

 

[SkipVought]

It is in the help menu when the WORKSHEET is visible, not the VB Editor.

Skip,

_[red][/red]

 

[JensKKK]

There you go Geoff,

Here is a short intro.

Macro 1) prepare the spreadsheet for the calculation and goes through a data table. The solver is reset before it goes into the main for next loop and the solver gets reset after the program terminated/finished all calculations.
Macro 2) calculates curve fit parameters and calls macro 4)
Macro 3) calculates curve fit parameters and calls macro 4)
Macro 4) calls the solver

1) Sub Algo_Solve_dose_response_curve()
2) Sub Solve_Potency_Low(Delta_High, Speed_limit, high, low, R2 As Single)
3) Sub Solve_Potency_High(Delta_High, Speed_limit, high, low, R2 As Single)
4) Sub Algo_ext_pot_solver()


1) Sub Algo_Solve_dose_response_curve()

'Declare variables
Dim Noisefactor, Noise, Score, value, Percent_inhibition, RFU_Inhibition As Single
Dim sum_RFU, Delta_High, highest_rfu, lowest_rfu, Ki, Kd, IC50 As Single
Dim Results(12), Percent_Increment, Speed_limit As Single
Dim Error_String, Namestr As String
Dim msb, i, j, Upper_Limit, Number_of_Proteins, Fitting_speed, Number_of_Arrays As Integer
Dim BSL_Ligand As Integer

On Error GoTo errorhandler


Geoff: The solver is reset before I go into the for next loop. Is that a problem?


'Solver must not calculate solution with negative Kd or bmax
'reference cell H114 contains the upper limit for EC50 (which is 0)
SOLVERReset
SolverAdd CellRef:="$H$114", Relation:=1, FormulaText:="$H$103"
SolverAdd CellRef:="$H$119", Relation:=3, FormulaText:="$H$103"



For j = 1 To Number_of_Proteins
Cells(104, 15) = Round(j * Percent_Increment, 1)
'reset r2 for each calculation
Cells(107, 14) = -1000000
'inner for next loop that loads new data

'Stop screen updating
Application.ScreenUpdating = False

value = 0
highest_rfu = 0
lowest_rfu = 1000000
'read (from average) and write (algorithms) the current rfu value, Kd and protein name from the average RFU page
Sheets("average rfu").Select
Namestr = Cells(9 + j, 3) 'read Gene name
'Read Kd value for Ki calculation
Kd = Cells(9 + j, 17 + BSL_Ligand)
For i = 1 To Number_of_Arrays
Results(i) = Cells(9 + j, 3 + i)
Next

Sheets("algorithms").Select
Cells(111, 8) = Kd
For i = 1 To Number_of_Arrays
value = Results(i) 'read source value
Cells(96, 7 + i) = value 'write value into destination cell
If value > highest_rfu Then
highest_rfu = value
End If
If value < lowest_rfu Then
lowest_rfu = value
End If
Next
'Read standard deviation and write it in Algorithms
Sheets("rfu st dev").Select
For i = 1 To Number_of_Arrays
Results(i) = Cells(9 + j, 3 + i)
Next
Sheets("algorithms").Select
For i = 1 To Number_of_Arrays
value = Results(i) 'read source value
Cells(120, 7 + i) = value 'write value into destination cell
Next


Cells(9992, 9) = j 'write the current calculation on the screen
Cells(9993, 9) = Namestr
'Initialize: EC50 with best guess value
'Read and write best guess EC50 value
value = Cells(102, 10)
Cells(103, 8) = value

high = highest_rfu
low = lowest_rfu
'generate reference R2 value and optimize high RFUs first
Cells(106, 8) = high
Cells(105, 8) = low
Application.Run "Algo_ext_pot_solver"
R2 = Cells(104, 8)


Application.Run "find_delta_high", highest_rfu, lowest_rfu
Delta_High = Cells(9, 19)
high = highest_rfu - Delta_High
If Upper_Limit = 1 Then
Delta_High = 0.00001
End If
i = 0
Speed_limit = Delta_High / Upper_Limit
'call procedure to find optimal "high" value
Application.Run "solve_potency_high", Delta_High, Speed_limit, high, low, R2
high = Cells(110, 14)
'If (highest_rfu - lowest_rfu) <> 0 Then
' Delta_High = (highest_rfu - lowest_rfu) / 4
'Else
' Delta_High = 0
'End If
Application.Run "find_delta_high", highest_rfu, lowest_rfu
Delta_High = Cells(9, 19)

low = lowest_rfu + Delta_High

If Upper_Limit = 1 Then
Delta_High = 0.00001
End If
'call procedure to find optimal "low" value
Application.Run "solve_potency_low", Delta_High, Speed_limit, high, low, R2


This is the end of the main for next loop
Next 'j
'delete conditions for ki and EC50 after the calculation is finished
SOLVERReset


These are the Do while loops that call the solver. The solver runs in a separate macro

Sub Solve_Potency_Low(Delta_High, Speed_limit, high, low, R2 As Single)
Dim value As Single
Sheets("Algorithms").Select
Do While Delta_High > Speed_limit
Cells(106, 8) = high
Cells(105, 8) = low
Application.Run _
"Algo_ext_pot_solver"
value = Cells(104, 8)
If value > R2 Then
R2 = value
Delta_High = Delta_High / 2
low = low + Delta_High
Else
Delta_High = Delta_High / 2
low = low - Delta_High
End If
Loop
End Sub
Sub Solve_Potency_High(Delta_High, Speed_limit, high, low, R2 As Single)
Do While Delta_High > Speed_limit
Cells(106, 8) = high
Cells(105, 8) = low
Application.Run _
"Algo_ext_pot_solver"
value = Cells(104, 8)
If value > R2 Then
R2 = value
Delta_High = Delta_High / 2
high = high - Delta_High
Else
Delta_High = Delta_High / 2
high = high + Delta_High
End If
Loop
End Sub



This is the routine that calls the solver

Sub Algo_ext_pot_solver()
Dim i As Integer
Dim Error_String As String
On Error GoTo errorhandler
Sheets("algorithms").Select
Number_of_Arrays = Cells(11, 21)
SolverOk SetCell:="$H$101", MaxMinVal:=2, ValueOf:="0", ByChange:="$H$103"
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

'read and write results from source to destination kd,bmax, R2, ssq and fitted_data array
'Read
If Cells(104, 8) > Cells(107, 14) Then
Cells(106, 14) = Cells(103, 8) 'EC50
Cells(107, 14) = Cells(104, 8) 'R2
Cells(110, 14) = Cells(106, 8) 'best ligand conc. high
'Don't display curve fit if R2 is below 0.0
If Cells(107, 14) > 0 Then
For i = 1 To 12
Cells(113, 7 + i) = ""
Cells(115, 7 + i) = ""
Cells(116, 7 + i) = ""

Next
For i = 1 To Number_of_Arrays
value = Cells(97, 7 + i) 'read fitted data
Cells(113, 7 + i) = value 'write value into destination cell
value = Cells(96, 7 + i) 'read raw data
Cells(115, 7 + i) = value 'write value into destination cell
value = Cells(95, 7 + i) 'read ligand concentration data
Cells(116, 7 + i) = value 'write value into destination cell

Next 'i
End If
End If
Exit Sub
errorhandler:
Error_String = err.Number & " " & err.Description
Application.Run "Procognia_ErrorHandler", 2, Error_String
End Sub

 

[xlbo]

ok - doesn't seem to be much wrong at first glance - you have no object variables so it is not an object memory leak.

Something to bear in mind if you are iterating the solver is that you may be running a loop whilst it is calculating - do you have anything in there that waits whilst the solver finishes before continuing to process ??

Other than that, I would be interested to see what doing a detect and repair does as there doesn't appear to be a lot wrong with your code.

Could you also give us an example of the error messages you are getting ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JensKKK]

Q1) As far as I am aware there is nothing waiting for the solver to finish. Once the solver is finished it hands the conrol back to macro 2/3.


*** Error Message: Solver: An unexpected internal error occurred, or available memory was exhausted.***

I'll try the detect and repair later.

R.

 

[JensKKK]

Tried the detect and repair, but as far as I can see it did not improve matters.

I found another nice command in solver related post which opens the solver.xla and can prevent crashes on computer that don't have the solver selected.

'On Error GoTo errorhandler

Application.Run "Solver.xla!Autpen"

that will help with a couple of solver issues that I had encounterd.