The Solver add-in allows you to perform "what if" calculations based on several variables.
I hope the above above might be of some use.This is an additional add-in that can be loaded when necessary. I can't say that all "Application." functions are slow, but they can certainly have an impact. Note that any command that starts with "Application." or "WorksheetFunction." is referring to an Excel function. When using worksheet functions in large, time consuming macros you should evaluate the impact of rewriting the function. If you have any worksheet functions in your calculations, I suggest caution. if you can reduce the number of numerical operations, there are gains to be had there as well. If you can you make your math more efficient, i.e. Select, can you not reference the sheets directly instead. So there's no longer a performance advantage to using Integer variables in fact, Long variables may be slightly faster because VBA does not have to convert them. In recent versions, however, VBA converts all integer values to type Long, even if they're declared as type Integer.
Traditionally, VBA programmers have used integers to hold small numbers, because they required less memory. Intel(R) Core(TM) i7-3770 CPU 3.40GHz, 3401 Mhz, 4 Core(s), 8 Logical Processor(s)ĭim i, j, s, t, k As Integermeans that k is an integer and the rest are variant.
Intel core i5 2450m generation windows 7#
I am running this in Excel 2016 on Windows 7 Range("N10").Offset((NumBeams + 5) * 19, s * 15).PasteSpecial (xlPasteValues) ' Application.StatusBar = LoopCounter & " of " & LoopMax & ", " & Format(LoopCounter / LoopMax, "0%") & " of Calculations Complete: " SolverAdd CellRef:=Range(PerpLine).Offset(i, t + s * 15).Address, Relation:=2, FormulaText:=Range(Deck).Offset(i, t + s * 15).Address ' SolverDelete CellRef:=MinCope, Relation:=2, FormulaText:=AllowCopeR SolverOk SetCell:=Range(PerpLine).Offset(i, t + s * 15).Address, MaxMinVal:=1, ValueOf:=0, B圜hange:=Range(PerpIntersect).Offset(i, t + s * 15 - StartOffset).Address, _ If t = 0 Then StartOffset = 2 Else StartOffset = 0 'Application.StatusBar = LoopCounter & " of " & LoopMax & ", " & Format(LoopCounter / LoopMax, "0%") & " of Calculations Complete: " SolverAdd CellRef:=Range(MinCope).Offset(i, s * 15).Address, Relation:=2, FormulaText:=AllowCopeR SolverOk SetCell:=Range(MinCope).Offset(i, s * 15).Address, MaxMinVal:=1, ValueOf:=0, B圜hange:=Range(TopBeam).Offset(i, s * 15), _ If k = 0 Then MinCope = Range("T10").Offset((NumBeams + 5) * 18, 0).Address Else MinCope = Range("T10").Offset((NumBeams + 5) * 17, 0).Addressįor s = 0 To NumSpans - 1 'loops through spansįor i = 0 To NumBeams - 1 'loops through beamsĪllowCopeR = Range("G10").Offset((NumBeams + 5) * 17 + i, s * 15).Address LoopCounter = Round(LoopMax * 0.05, 0) ' starts solver percentage at 5%įor k = 0 To 2 'runs program multiple times do to iterative nature LoopMax = LoopMax * 1.1 'adds 10% so the set up is 5% and the summary and charts are 5% PerpIntersect = Range("I10").Offset((NumBeams + 5) * 13, 0).Address
StartDeckElev = Range("I10").Offset((NumBeams + 5) * 4, 0).Address 'solves for beam elevations to set haunch thickness toĭim PerpIntersect, PerpLine, Deck As String
0 kommentar(er)
