VBA: Slow solver-loop

Question

I have a code that in a simplified way calculates blast furnace gases for 168 hours (eg a week)

It reads in some input values and chemical values and calculated the molar masses in the system. After that the solver calculates in what chemical form (mainly CO, CO2) the gases exit in. The problem is that it's extremely slow. If I have this excel workbook open it takes minutes and I haven't even bothered to let it run to the end when there are more workbooks open.

I'm very new to VBA, but I'd expect it to be a bit faster if I could set it to solve the equations in VBA instead of letting the values "iterate" back and forth between the excel worksheet and the VBA solver, by gradually solving the cell values - if I just knew how, IF it is possible or a good idea.

The code in its entity, first the general calculations:

   Sub BFgas()

Datamatrix = Range(Cells.Find("Datamatrix").Offset(1, 0).Address, Cells.Find("Datamatrix").Offset(21, 0).Address)
ReDim BFoutput(1 To 168, 1 To 3) As Double



M_pigiron_Matrix = Range(Cells.Find("BF1 Pig iron production").Offset(1, 0).Address, Cells.Find("BF1 Pig iron production").Offset(168 + 1, 0).Address)
Bf_blast_Matrix = Range(Cells.Find("BF 1 - Blast").Offset(1, 0).Address, Cells.Find("BF 1 - Blast").Offset(168 + 1, 0).Address)
Bf_oxygen_Matrix = Range(Cells.Find("BF 1 - Oxygen").Offset(1, 0).Address, Cells.Find("BF 1 - Oxygen").Offset(168 + 1, 0).Address)

M = 1
Do

M_pigiron = M_pigiron_Matrix(M, 1) 'Tons of pig iron
Bf_blast = Bf_blast_Matrix(M, 1) 'Nm3
Bf_oxygen = Bf_oxygen_Matrix(M, 1) 'Nm3

If Bf_blast = 0 Or Bf_oxygen = 0 Then
Do
M_pigiron = M_pigiron_Matrix(M, 1) 'Tons of pig iron
Bf_blast = Bf_blast_Matrix(M, 1) 'Nm3
Bf_oxygen = Bf_oxygen_Matrix(M, 1) 'Nm3
M = M + 1
Loop While Bf_oxygen = 0 Or Bf_blast = 0
End If



n_N2_blast = Bf_blast * Datamatrix(19, 1) / Datamatrix(17, 1) 'kmol
n_O2_blast = Bf_blast * Datamatrix(18, 1) / Datamatrix(17, 1) 'kmol
n_O2_oxygenintake = Bf_oxygen / Datamatrix(17, 1) 'kmol
n_total_O_in = (n_O2_blast + n_O2_oxygenintake) * 2 'kmol




'Calculates the amounts of coke, briquettes and scrap

Cokeratio = Cells.Find("Input data").Offset(1, 1).Value2
Briqratio = Cells.Find("Input data").Offset(2, 1).Value2
Scrapratio = Cells.Find("Input data").Offset(3, 1).Value2
m_oil = Cells.Find("Input data").Offset(4, 1).Value2


m_coke = Cokeratio * M_pigiron * 1000 'kg
m_briq = Briqratio * M_pigiron 'kg
m_scrap = Scrapratio * M_pigiron 'kg


'Fe/Iron calculations
'Calculates the molar masses of iron and coal in pig iron, briqettes and scrap

n_Fe_pigiron = Datamatrix(3, 1) * M_pigiron * 1000 / Datamatrix(15, 1) 'kmol

n_Fe_briq = Datamatrix(12, 1) * m_briq / Datamatrix(15, 1) 'kmol

n_Fe_scrap = Datamatrix(13, 1) * m_scrap / Datamatrix(15, 1) 'kmol

'Calculates how many kmol is needed from pellets

n_Fe_pellets = n_Fe_pigiron - n_Fe_briq - n_Fe_scrap
m_pellets = n_Fe_pellets / Datamatrix(11, 1) * Datamatrix(15, 1) 'Divides by the iron content 0.72, to get the total mass



'O/Oxygen calculations
'Calculates the total incoming oxygen
'(m_pel*x_pellets,O + m_briq*x_O,briq)/M_O + n_blast,O2*2 + n_Oxygen,O2*2

Oxygen_in = m_pellets * Datamatrix(10, 1) / Datamatrix(16, 1) + m_briq * Datamatrix(9, 1) / Datamatrix(16, 1) + n_total_O_in 'kmol
Cells.Find("Solutions").Offset(0, 1).Value = Oxygen_in

'C/Coal calculations
'Calculates the incoming coal minus what comes out with the pig iron, leaving what comes out with the bf-gases
'm_coke,*x_C,coke + m_oil*x_C,oil + m_br*x_br,C = m_rj*x_C,rj + V_tg*(y,co + y,co2)

Coal_for_bf_gas = (m_coke * Datamatrix(4, 1) / Datamatrix(14, 1) + m_oil * Datamatrix(5, 1) / Datamatrix(14, 1) + m_briq * Datamatrix(6, 1) / Datamatrix(14, 1)) - M_pigiron * 1000 * Datamatrix(1, 1) / Datamatrix(14, 1)
Cells.Find("Solutions").Offset(0, 2).Value = Coal_for_bf_gas

'N/Nitrogen
'Nitrogen is mainly what comes in with the blast

N2_for_bf_gas = n_N2_blast
Cells.Find("Solutions").Offset(0, 3).Value = N2_for_bf_gas



'Sets in the hydrogen just in case
'H/hydrogen
'H_for_bf_gas = m_coke * Datamatrix(21, 1) / Datamatrix(20, 1) + m_oil * Datamatrix(7, 1) / Datamatrix(20, 1)

The solver part:

SolverReset 'Code solves the problem for a specific set of lines, in this case meaning hours
SolverOptions Precision:=1, Iterations:=100, AssumeNonNeg:=True
SolverOk setCell:=Cells.Find("Differences").Offset(1, 0).Address, MaxMinVal:=3, ValueOf:="0", ByChange:=Range(Cells.Find("Testing here").Offset(0, 1).Address, Cells.Find("Testing here").Offset(0, 3).Address)

SolverAdd cellRef:=Range(Cells.Find("Testing here").Offset(0, 2).Address, Cells.Find("Testing here").Offset(0, 3).Address), _
relation:=3, _
formulaText:=0.1

SolverAdd cellRef:=Range(Cells.Find("Testing here").Offset(0, 2).Address, Cells.Find("Testing here").Offset(0, 3).Address), _
relation:=1, _
formulaText:=0.4

SolverAdd cellRef:=Cells.Find("Testing here").Offset(0, 1).Address, _
relation:=3, _
formulaText:=(Bf_blast + Bf_oxygen) * 1.2

SolverAdd cellRef:=Cells.Find("Testing here").Offset(0, 1).Address, _
relation:=1, _
formulaText:=(Bf_blast + Bf_oxygen) * 2

SolverSolve userFinish:=True

BFoutput(M, 1) = Cells.Find("Testing here").Offset(0, 1).Value
BFoutput(M, 2) = Cells.Find("Testing here").Offset(0, 2).Value
BFoutput(M, 3) = Cells.Find("Testing here").Offset(0, 3).Value



M = M + 1
Loop While M < 169

Cells.Find("BF1 - Output data").Offset(2, 0).Resize(UBound(BFoutput, 1), 3).Value = BFoutput

Answer 1

I'm not a chemical engineer, so I don't know the equations you're trying to solve.

I'm guessing that they're non-linear, transient, and iterative. 168*3 = 504 degrees of freedom doesn't seem that large to me, but it could be a lot of work if you have lots of small time steps with iterations for each one.

I can't tell if you're doing a transient or steady state problem from the code you posted.

The numerical problems that I'm more familiar with (solid mechanics and heat transfer) are very sensitive to algorithm. The equations can be subject to time step restrictions for stability reasons, depending on the integration scheme chosen.

If you're solving a non-linear steady state problem the same comments would apply, except for iteration step size instead of time step size.

I can't glean much about this from your VB code, but I'll offer these recommendations:

1. Write out your equations, do a Fourier transform, and see if there are any stability restrictions.
2. Think about a tool kit like Matlab. They've got out of the box implementations that might be more highly optimized than your custom code.
3. I'm not aware of any profiling capabilities for VB or Excel, but you can't fix a problem without data. I'd see if I could get some information about where the time is being spent before hypothesizing about a solution.