My spreadsheet is large with many fields calculated with VBA functions. Until my computer was forced to restart due to an MS update, the spreadsheet was working perfectly.
My pc is fast, an AMD Athlon II quad core with 4 GB memory. The spreadsheet has been running in 2 environments [dual boot machine]: XP64 SP2 {there isn’t an SP3 for XP64} with Office 2007 and Win 7 with Excel 2010. All of the data is hard coded [i.e. there are no file reads either locally or from the internet].
When the machine was rebooted, most of the fields were filled with #VALUE!. One of the fields was simply the product of 2 numbers [in the form =A*B]. Double clicking on the field shows the 2 numbers are defined without a #VALUE! In either. Occasionally, after the double click it would calculate correctly and slowly the rest of the row would calculate correctly.
Next, often A or B was zero so a function replaced the product: [essentially, if A or B = 0 return zero otherwise return the product] This allowed examination with the function key. It showed the inputs and the correct product. The value would display in the cell slowly. Generally, the rest of the row would subsequently calculate.
I have several questions:
- What’s going on?
- Is there a solution within Excel?
- Is VBA too slow, would another language be better?
- Would the issues go away if this was rebuilt in SQL or some other environment?
Hello,
This is a little tough to figure out. It might be easier to forget about why it happened and focus on fixing it (you may never find out what exactly did happen with an MS Update).
- Have you tried forcing the worksheet to recalculate? Press Shift+F9 to do that.
- Is it possible to attach the workbook for further inspection?
i ripped out a ton of code and it runs. Shift F9 takes well over 10 minutes. Would conversion of the VBA to C# speed things up?
Thanx for the help.
There are a lot of ways you can improve VBA code that many people don't utilize.
Check out this page for more info:
http://vbaexpress.com/forum/showthread.php?t=9882
You have to be a member to view it, though. Not sure why it's private for members only. However, it's free and it's worth joining just to read that one article. Explains everything.
Also, feel free to post some code and I can give you some pointers on how to optimize it if you think there's room for improvement.
Also, I personally think C++ is the fastest code that executes for most/any program. However, it's extremely difficult/annoying to code it for Excel (maybe the new Tools for Office makes it easier, haven't really looked into it). C# would be faster in most instances compared to VBA. But again, I'd look into optimizing what you already have.
And you mention that you are using VBA for formula functions in your workbook. If you can come up with clever ways to use just Excel's native formulas, it will be 100 times faster for sure. Always opt for formulas before you venture into VBA functions for formulas.
I read the article you suggested and modified my code in several places. I tried to post an efficiency related on that website, but it indicated I was not permitted to post. I'll post a new thread here.Originally Posted by joseph4tw
Here's my VBA [all suggestions gladly accepted]
Function Recalc(SumStage2, Limit, Stage2, Gain, AvgIfGain, Trigger, Base)
Dim Alt
If SumStage2 > Limit Then Alt = 1 Else Alt = 0
If (AvgIfGain > 0 And Gain / (AvgIfGain + 0.001) > Trigger) Or Base = 0 Or Stage2 = 0 Then Recalc = 0 Else Recalc = Alt * Application.WorksheetFunction.Min(Base, Stage2) + (1 - Alt) * Stage2
End Function
Public Function AVGIF(ByVal checkRange As Range, ByVal criteria As Variant, ByVal avgRange As Range) As Variant
Dim sumIfResult As Double
Dim countIfResult As Double
sumIfResult = Application.WorksheetFunction.SumIf(checkRange, criteria, avgRange)
countIfResult = Application.WorksheetFunction.CountIf(checkRange, criteria)
If countIfResult = 0 Then
AVGIF = 0
Else
AVGIF = sumIfResult / countIfResult
End If
End Function
Function shares(Balance, price, pct_active, last_pct_active, Last)
If Last = 0 Then
If pct_active > 0 Then shares = Int(Balance * pct_active / price) Else shares = 0
Exit Function
End If
If pct_active <> last_pct_active Then shares = Int(Balance * pct_active / price) Else shares = Last
End Function
Public Function checkmember(a As Date, dates As Range, Flag) As String
Dim B As String, C
B = ""
For Each C In dates
If DateValue(C.VALUE) = a Then B = Flag
Next C
checkmember = B
End Function
Function SharpeRatio(InvestReturn, RiskFree) As Double
Dim AverageReturn As Double
Dim StandardDev As Double
Dim ExcessReturn() As Double
Dim nValues As Integer
nValues = InvestReturn.Rows.Count
ReDim ExcessReturn(1 To nValues)
For I = 1 To nValues
ExcessReturn(I) = InvestReturn(I) - RiskFree(I)
Next I
AverageReturn = Application.WorksheetFunction.Average(ExcessReturn)
StandardDev = Application.WorksheetFunction.StDev(ExcessReturn)
SharpeRatio = AverageReturn / StandardDev
End Function
Function XavCorePct6(core, XAVCore, COREpcnt, coreext, Limit, Sum, Scal)
Dim use
If core < XAVCore Then
XavCorePct6 = 0
Exit Function
End If
use = COREpcnt + Sum * Scal
If Sum > 0 And use <= Limit Then
XavCorePct6 = Application.WorksheetFunction.Min(COREpcnt, Limit - use)
Exit Function
End If
If use <= Limit Then XavCorePct6 = Application.WorksheetFunction.Min(coreext, Limit - use) Else XavCorePct6 = 0
End Function
Function INITTRADE(THISDATE As Range, STARTDATE As Date, val As Range)
Dim C, B
Dim ICOUNT As Integer, MAX As Integer
Dim ARR1()
Dim ARR2()
MAX = THISDATE.Rows.Count
'MsgBox (MAX)
ARR1 = THISDATE
ARR2 = val
For ICOUNT = 1 To MAX
If DateValue(ARR1(ICOUNT)) = STARTDATE Then
INITTRADE = ARR2(ICOUNT, 1)
Exit Function
End If
Next ICOUNT
End Function
Function minf(a, B)
minf = Application.WorksheetFunction.Min(a, B)
End Function
Function maxf(a, B)
maxf = Application.WorksheetFunction.MAX(a, B)
End Function
Reply With Quote
Bookmarks