0

I have written a C++.NET component which can be called from VBA. The component calculated a very large array of numbers (given some input) and writes this array to a binary file.

I have noticed that if I generate files using the exact same settings calling the component via VBA I get different results each time. I can see differences in bytes when opening the binary files in a hex editor. However if I do the same thing but by calling the component in a C++.NET or C#.NET executable I do not get any differences at all - the files generated using the same settings are identical each time they are generated in this way.

The differences are only small and are just floating point differences. However, I'd like to find out why this is happening. Why do I get differences when calling it via VBA but not via C++.NET or C#.NET?

For the record - I'm running 32 bit Excel (for VBA) on a 64 bit machine here (if that makes any difference...)

Any thoughts on this would be massively appreciated.

Rajiv Patel
  • 21
  • 2
  • http://stackoverflow.com/a/16917514/17034 – Hans Passant Nov 12 '15 at 09:47
  • Thanks @HansPassant. This helps to explain why the numbers produced by the executables are different to the numbers produced when calling the dll via Excel VBA. But what I'm not sure about is why I get different numbers each time I call the dll with Excel VBA. Is it possible that the FPU control changed by Excel means that these calculations are no longer determinsitic? – Rajiv Patel Nov 12 '15 at 11:37
  • No, the most likely source of that is just a plain bug. Like forgetting to initialize a local variable. – Hans Passant Nov 12 '15 at 11:42
  • @HansPassant - do you mean a bug by not initialising a local variable in my source code? This seems unlikely because the issue doesn't happen if I regenerate files by calling the dll twice in C# (even twice in the same executable). I also get the differences in Excel VBA if i generate one file. Close Excel. Start it up again and then generate it again. So it's a whole new instance of the class being used. In any case, I was using the same instance of the class used to do the calculations when I generated it twice in succession in C#. – Rajiv Patel Nov 12 '15 at 12:28
  • I think maybe I got to the bottom of this. My component uses multi-threading. I believe the floating point state is changed by Excel but not necessarily copied across to all threads used by the component. So potentially different settings are used in each thread which gives rise to the different results each time. – Rajiv Patel Nov 13 '15 at 11:44
  • Were you able to definitively prove that this was happening? What did you do to resolve the problem? I have a C# Excel add-in that uses Parallel.For and seems to exhibit same behavior. It seems to be a bit random, about 19 out of 20 times it behaves one way, 1 out of 20 it behaves the same was as it does when I constrain my code to single threaded (MaxConcurrentThreads = 1). – Terry Aug 06 '17 at 15:38
  • @Terry: See answer I posted below. – Rajiv Patel Aug 06 '17 at 23:27

1 Answers1

1

@Terry - I think I was able to prove this was happening. First let me explain what the problem was (or at least what I think is a plausible explanation of what it was).

Why a C# executable gives different results to Excel?

Any application can set the floating point state to be whatever it requires. In fact, this is good practice so you can ensure specific precision in your application (if it matters).

When you run from a C# executable, the floating point settings used are whatever the compiler decided. However, when you run it in Excel, Excel changes the floating point settings to be whatever it requires which may be different to the executable. This is why you get a difference.

 Why running within Excel may give different results each time?

Excel changes the floating point settings to whatever it needs. However, it only does this on the threads that it needs to use. So if Excel needs N threads, it will set the floating point settings on those N threads. The floating point settings on any newly spawned threads are not copied from the "master" thread. So if the process in your add-in needs M > N threads, then there are M-N threads which are spawned with different floating point settings to the N set by Excel. So now you have M threads kicking around which have two sets of different floating point settings. Because the threads which get used in parallel programming are not deterministic, you may get different results each time you run in Excel.

 How I resolved it?

I resolved this by explicitly setting the floating point settings on every thread used to ensure it is always the same. I introduced the following routine. It's in C++ but you should be able to get it working in C# too.

[DllImport("msvcrt.dll")] int _controlfp(int IN_New, int IN_Mask);
void FixFPU()
{
    _controlfp(_MCW_DN, _DN_SAVE);
    _controlfp(_MCW_EM, _EM_INVALID);
    _controlfp(_MCW_RC, _RC_CHOP);
    _controlfp(_MCW_PC, _PC_53);
}

I called this routine inside the Parallel.For loop. This ensures that each thread used by the Parallel.For loop has the same floating point settings. By explicitly specifying the floating point state, you override Excel's floating point settings on those threads while your routine is running from your add-in. I also called this routine at the start of my process to ensure that the single thread used at the start is also running the same floating point settings. This ensures that any calculations done prior to getting to the Parallel.For loop are executed with the same precision as those inside the Parallel.For loop.

It's the last statement in that routine which makes the biggest difference as this sets the precision.

I hope this helps you to resolve your problem.

Community
  • 1
  • 1
Rajiv Patel
  • 21
  • 2