Results 1 to 5 of 5

Thread: #VALUE! Problems

  1. #1

    #VALUE! Problems



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:

    1. Whatís going on?
    2. Is there a solution within Excel?
    3. Is VBA too slow, would another language be better?
    4. Would the issues go away if this was rebuilt in SQL or some other environment?

  2. #2
    Seeker joseph4tw's Avatar
    Join Date
    May 2012
    Location
    South Florida, USA
    Posts
    13
    Articles
    0
    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?

  3. #3
    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.

  4. #4
    Seeker joseph4tw's Avatar
    Join Date
    May 2012
    Location
    South Florida, USA
    Posts
    13
    Articles
    0
    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.

  5. #5

    re #VALUE! Problems

    Quote Originally Posted by joseph4tw View Post
    There are a lot of ways you can improve VBA code that many people don't utilize.
    Check out this page for more info:
    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.

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •