Forum Discussion
Formulas returning nonsensical results
I recently had a few formulas start returning nonsensical results and am at a total loss. For example, a formula that takes the max of (0.095, 0.095, 0.2995, 0.2995) is returning 0.7787. When I 'F9...
Zack Barresse , SS attached. Error I previously mentioned is on sheet 'CODE'. A similar error is occurring on sheet 'WEBHOLES' in cell F6.
Excel_Building_Eng50 , as soon as I try to calculate something in this sheet (e.g. in any empty cell =1+2) it forwards me to VBA. It gives an error
but that doesn't matter, I guess you have no such error. The point is your VBA code is involved into the sheet recalculation.
- That doesn't have anything to do with the problem, although it should be addressed. There is only one Windows API reference, which is in module 'Sub_GetBeamData':
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
It should be like this:
#If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else
Private Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End If
You could wrap it in a function, such as the below code, assuming it's utilizing the above [conditional compilation] code and resides in the same module:
Public Sub API_Sleep(ByVal Milliseconds As Long)
Sleep Milliseconds
End Sub
We can use that conditional compilation because VBA7 can accept ptr safe, which includes 64 bit, and previous to VBA7 was only 32 bit. Full information on how it works can be found at the below link.
https://answers.microsoft.com/en-us/msoffice/forum/all/that-whacky-conditional-compiler-constant/3da810b8-9b89-4372-8104-a10c2ad25d4d?auth=1Zack Barresse SergeiBaklan , Thank you for point that out. I've since removed the 'Sleep' reference as we were not actually using it for anything.
As expected, the cell formula problems persist. Any further thoughts or findings on what might be the issue here?
Thanks again!
- There's several circular references. Since iterative calculations aren't turned on, I'm assuming this isn't desired. I've been tracing formula results which, when manually calculated, are returning a zero value. Some additional oddities is if you try ISNUMBER() on those specific cells you mentioned in your original post, it returns FALSE. Likewise, if you manually calculate those cells, they all return zero values as well. I'm not sure how long it will take me not being familiar with your model. Assuming for the moment that circular references are NOT desired, I'd recommend you pulling on that thread and tracing calculations back to where the circular reference originates, which you'd have the best luck finding as well.
