Different Workbooks interfering with each other

[mwollert]

I am suddenly having a strange problem. I open a workbook that has cells calculated with a function call in VBA. The workbook works fine when it is the only one open. When I open another workbook that also has some cells calculated with function calls in VBA, the first workbook changes all cells that were calculated with a function to #value. I go to that workbook and press CNTL-ALT-F9 and the values come back but then all cells calculated with functions go to 0 on the other workbook.

The functions are not the same name and both are contained in a module saved within each workbook. The functions do the same thing (they calculate income tax) but I changed the names thinking they were conflicting with each other and still only 1 sheet at a time will display the cells that use a function to calculate the values.

Seems to be a fairly recent problem as they have been opened together in the past and did not conflict with one another. Not sure what I changed to cause the confliction.

Any suggestions?

[scottiex]

I think you will need to post the code, or even better the sheets.

[mwollert]

The code for both functions is :

Private Function TaxCalculate(Income As Currency, Sheet As Integer, StartingRow As Integer, StartingCol As Integer, Brackets As Integer)

  'You must pass 5 values
  '       1- Pass a CURRENCY as the amount of Income to calculate the tax on
  '       2- Pass the SHEET number that the tax table is on
  '       3- Pass the STARTING ROW of the Tax Table
  '       4- Pass The STARTING COLUMM of the Tax Table (as number 1=A, 2=B, etc)
  '       5- Pass the NUMBER of BRACKETS (Maximum is 20)
  
    
    Dim Limits(20) As Currency
    Dim PreviousTax(20) As Currency
    Dim TaxRate(20) As Single
    Dim Tax As Currency
    
    ' Load the Tax Table
    
    For x = 0 To Brackets - 1
         Limits(x + 1) = Worksheets(Sheet).Cells(StartingRow + x, StartingCol)
         PreviousTax(x + 1) = Worksheets(Sheet).Cells(StartingRow + x, StartingCol + 1)
         TaxRate(x + 1) = Worksheets(Sheet).Cells(StartingRow + x, StartingCol + 2)
    Next x
          
    ' Calculate The Tax
    
    For x = 1 To Brackets
        If Income < Limits(x) Then
             Tax = PreviousTax(x) + ((Income - Limits(x - 1)) * TaxRate(x))
             Exit For
        End If
    Next x
    
    TaxCalculate = Tax
    
      
End Function

I changed the name in the second workbook to rTaxCalculate

[Norie]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[scottiex]

Looks like you added the code tags so I presume I can post.

The code seems to work for me (in two books) - I don't think you should need to change the second workbook's function name, maybe you made a mistake doing that or there is some other issue with your workbooks that we cannot see.

You could try putting the macro in a personal workbook or similar instead.