Automation error on shared Excel 2007 file with custom functions
-
Friday, May 11, 2012 2:13 PM
Hi,
We are having an issue with a macro enabled excel 2007 file. The issue is that, intermittantly, the user receives an Automation error on opening the file. Actually it happens with every file with the same VBA code.
A little background on this is that we needed to create a custom function to calculate taxes on certain values. In office 2007 and 2010 we had to convert the original xlsx file to xlsm to accommodate the VBA code. The file is also shared.
This does not happen when sharing is disabled, and as I said before, it is an intermittant problem, so it does not seem that the code is at fault.
It is very difficult to debug to see if a certain part of the code causes this, as it only happens in shared mode when the code is protected.
Also when the file is opened and caused an Automation error, another user can open the file without experiencing the Automation error.
The usual "fix" for this is to kill the task in task manager, reopen excel and disable macros. This will allow one to open the file again without the macro running. Then unshare and save. Re-enable macros and reopen the file. Re-enable sharing and one is good to go a few times without the error.
The sheet contains more than 20 instances where the custom functions are used and the automation error seems to happen for each instance.
Here is the code
Attribute VB_Name = "TaxCalc" Option Explicit Public Function avanTax(TAXE_IN As Double, Optional PriceYear As Integer = 2012) As Double 'Written by Daniel Combrinck for Samcon Inc 'Updated 2 feb 2012 Dim level1, level2, level3, level4, tax1, tax2, tax3, tax4, credit2, credit4, tps, tvq, SalesTax, testResult As Double Dim counter, big, small As Integer counter = 0 big = 0 small = 0 If PriceYear = 2012 Then 'Enter values here for each level level1 = 216204 tax1 = 1.08102 level2 = 339012 tax2 = 1.22808 credit2 = 23949.58 level3 = 395514 tax3 = 1.13004 level4 = 517387.5 tax4 = 1.218735 credit4 = 25471.7 tps = 0.05 tvq = 0.095 ElseIf PriceYear = 2010 Then 'Enter values here for each level level1 = 216307.2 tax1 = 1.081536 level2 = 249615 tax2 = 1.332312 credit2 = 37645.23 level3 = 388290 tax3 = 1.1094 level4 = 507937.5 tax4 = 1.196475 credit4 = 25471.7 tps = 0.05 tvq = 0.075 ElseIf PriceYear = 2011 Then 'Enter values here for each level level1 = 215172 tax1 = 1.07586 level2 = 335916 tax2 = 1.20744 credit2 = 21794.87 level3 = 391902 tax3 = 1.11972 level4 = 512662.5 tax4 = 1.207605 credit4 = 25471.7 tps = 0.05 tvq = 0.085 Else MsgBox "L'anée n'a pas été définie. S.V.P, contactez votre département de T.-I.." Return End If SalesTax = tps + ((tps + 1) * tvq) 'The following block removes credits and divides by a percentage to derive the original value 'This procdure lacks precision and an adjustment is made later to correct the discrepancies. On Error Resume Next If TAXE_IN <= level1 Then avanTax = TAXE_IN / tax1 ElseIf TAXE_IN <= level2 Then avanTax = TAXE_IN / tax2 + credit2 ElseIf TAXE_IN <= level3 Then avanTax = TAXE_IN / tax3 ElseIf TAXE_IN <= level4 Then avanTax = TAXE_IN / tax4 + credit4 Else avanTax = TAXE_IN / (SalesTax + 1) End If If err.Number <> 0 Then avanTax = 0 End If 'Re-calculate result by adding tax and compare avanTax = Round(avanTax, 2) testResult = taxIn(avanTax, PriceYear) 'If there is a difference between the tax and before tax figures for before tax, the result from this 'funtion is adjusted until the calculation in both directions are equal. Do counter = counter + 1 If testResult <> TAXE_IN And (big = 0 Or small = 0) Then If testResult > TAXE_IN Then avanTax = avanTax - 0.01 big = big + 1 Else avanTax = avanTax + 0.01 small = small + 1 End If End If testResult = taxIn(avanTax, PriceYear) If big * small Then testResult = TAXE_IN End If Loop Until testResult = TAXE_IN Or counter > 200 avanTax = Round(avanTax, 2) End Function Public Function taxIn(avanTax As Double, Optional PriceYear As Integer = 2012) As Double 'Written by Daniel Combrinck for Samcon Inc 'Updated January 2012 Dim tps, tpsCrPerc, tpsCrMax, tpsPriceLow, tpsPriceHigh, tpsDivider, tpsValue, tpsCred As Double Dim tvq, tvqCrPerc, tvqCrMax, tvqPriceLow, tvqPriceHigh, tvqDivider, tvqValue, tvqCred As Double If PriceYear = 2012 Then tps = 0.05 tpsCrPerc = 0.36 tpsCrMax = 6300 tpsPriceLow = 350000 tpsPriceHigh = 450000 tpsDivider = 100000 tvq = 0.095 tvqCrPerc = 0.491429 tvqCrMax = 9804 tvqPriceLow = 200000 tvqPriceHigh = 300000 tvqDivider = 100000 ElseIf PriceYear = 2011 Then tps = 0.05 tpsCrPerc = 0.36 tpsCrMax = 6300 tpsPriceLow = 350000 tpsPriceHigh = 450000 tpsDivider = 100000 tvq = 0.085 tvqCrPerc = 0.491429 tvqCrMax = 8772 tvqPriceLow = 200000 tvqPriceHigh = 300000 tvqDivider = 100000 ElseIf PriceYear = 2010 Then tps = 0.05 tpsCrPerc = 0.36 tpsCrMax = 6300 tpsPriceLow = 350000 tpsPriceHigh = 450000 tpsDivider = 100000 tvq = 0.075 tvqCrPerc = 0.353829 tvqCrMax = 5573 tvqPriceLow = 200000 tvqPriceHigh = 225000 tvqDivider = 25000 Else MsgBox "L'anée n'a pas été définie. S.V.P, contactez votre département de T.-I.." Return End If 'Calculate TPS value tpsValue = Round(avanTax * tps, 2) 'Calculate TPS Credit tpsCred = Round(tpsValue * tpsCrPerc, 2) If tpsCred > tpsCrMax Then tpsCred = tpsCrMax End If If avanTax < tpsPriceLow Then ElseIf avanTax < tpsPriceHigh Then tpsCred = Round(tpsCred * Round((tpsPriceHigh - avanTax) / tpsDivider, 4), 2) Else tpsCred = 0 End If tpsCred = -tpsCred 'Calculate TVQ value tvqValue = Round((avanTax + tpsValue) * tvq, 2) 'Calculate TVQ Credit tvqCred = Round(tvqValue * tvqCrPerc, 2) If tvqCred > tvqCrMax Then tvqCred = tvqCrMax End If If avanTax < tvqPriceLow Then ElseIf avanTax < tvqPriceHigh Then tvqCred = Round(tvqCred * Round((tvqPriceHigh - avanTax) / tvqDivider, 4), 2) Else tvqCred = 0 End If tvqCred = Round(-tvqCred + tpsCred * tvq, 2) taxIn = Round(avanTax + tpsValue + tpsCred + tvqValue + tvqCred, 2) End Function
All Replies
-
Friday, May 11, 2012 4:58 PM
Custom functions will not cause that error, so it is not your code. It is the sharing part, which I cannot comment on, since I think sharing is a hugely bad idea that has been poorly implemented in Excel. Note that Excel Web Apps offer collaborative editing, but I haven't used that yet:
On a side note, your dimensioning lines (like this one)
Dim tps, tpsCrPerc, tpsCrMax, tpsPriceLow, tpsPriceHigh, tpsDivider, tpsValue, tpsCred As Double
are declaring all but tpsCred as Variant, not as Double. Doesn't really matter, but just a heads up... VBA allows single line dim statement but requires explicit-by-variable declaration of type:
Dim tps As Double, tpsCrPerc As Double,....., tpsCred As Double
HTH, Bernie
- Edited by Bernie Deitrick, Excel MVP 2000-2010 Friday, May 11, 2012 5:04 PM
-
Friday, May 11, 2012 10:16 PM
Bernie,
You are right sharing Excel is not perfect, but in a perfect world I would be using a proper database instead. We are aiming for Sharepoint, which will be a better solution for managing what we do. I will then be able to migrate what is currently shared in excel to SQL under Sharepoint.
Thanks for the heads up. I was not aware of the explicit-by-variable declaration.
Daniel
-
Monday, May 14, 2012 10:22 AMDid you get any solution for this?
-
Monday, May 14, 2012 1:28 PM
"Did you get any solution for this?"
No solution yet, only a workaround. The workaround being to choose between macros and a shared environment. We have been using Excel in a shared environment for years, with only minor annoyances and now, that we are introducing custom functions to the mix, Excel is unable to cope.
I guess I will have to open another ticket with Microsoft. It will be my 5th this year.
-
Monday, May 14, 2012 2:00 PM
You could probably bypass the problem by moving the VBA code to a separate unshared workbook, which could be an XLAM addin if you wanted.
Then either install the addin on the users PCs or add a line to the workbook open of the original (shared) workbook which opens the XLAM.
Or you could rewrite the function as an XLL.
Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/
- Marked As Answer by Daniel Samcon Tuesday, May 15, 2012 5:53 PM
-
Monday, May 14, 2012 5:56 PM
Thanks for this idea. I have implemented a call to open an external add in, in some of my shared macro enabled workbooks. This seems to work. I will give it a few days to test.
The bonus I can see with this approach is that only one file is used from one location. This helps with version control.
.png)
