Calculated Field Office 2003 Settings

[mel1s2]

I have a question about settings in 2003,
Out of about 50 to 60 people using one of my Excel files, 3 are having an issue with calculated pivot fields being added.
I have checked all the Tools-Options settings and have matched mine exactly to the people who have the error, but no errors occur on my machine or 47 other people.
The error tells me that the formula is wrong (its not, and it has to be there). Is there some setting somewhere that will not allow Calculated fields to be added? If so, I sure I can change this in my code, but I need to know where it is or what it is.
thank you in advance,
melanie

 

[Andrew Poulsom]

I think you had better post the offending code. Are all your users in the same country?

 

[mel1s2]

Offending Code

There are about 23 lines of code adding calculated fields throughout the file, here is an example of one of them...
The users are from all different countries, the issue is mainly in Brazil, however I have 1 person in Southeast Europe that has the same error. Each of these people are using Windows XP Pro, and Office 2003

the pivTbl is set to the current pivot table that is created and fields are already added including one data field...
pivTbl.PivotFields(11).orientaion = xldatafield
Then the error:
pivTbl.CalculatedFields.Add "InforMts", "=IF('" & pivTbl.PivotFields _ (12).Name & "'=0,0,'" pivTbl.PivotFiles(12).Name & "'/1000)"
The reason for not just placing the name is due to other language issues, so I place the number to take care of this, so basically
.add "InforMts", "=IF('In Vol'=0,0,'In Vol'/1000)"

Thanks in advance, this one is driving me crazy, and the people trying to do their work.
mel

 

[Andrew Poulsom]

I expect it's because they are using the semicolon as an argument separator instead of the comma. This character in use returned by:

Application.International(xlListSeparator)

So try eg:

ArgSep = Application.International(xlListSeparator)
pivTbl.CalculatedFields.Add "InforMts", "=IF('" & pivTbl.PivotFields _ (12).Name & "'=0" & ArgSep & "0" & ArgSep & "'" pivTbl.PivotFields(12).Name & "'/1000)"

 

[mel1s2]

I will have them test now. However, If I change my international settings to use a semicolon shouldn't it crash, so that I can test myself instead of sending them a new file.

thanks,
mel

 

[Andrew Poulsom]

I will have them test now. However, If I change my international settings to use a semicolon shouldn't it crash, so that I can test myself instead of sending them a new file.

thanks,
mel

Yes, you can try that.

 

[mel1s2]

Changing mine didn't work, the code still what it was suppose to, I think it depends not only on the semicolon, but there operating system being set up in what ever other than English, because the test they just ran worked. I think. I put a message box after the code saying "This is it". That way if she got that message, the code went through.
Oh thank you sooooooooo much, you have no clue how much of a stress breaker you have been. I thought this one bug was going to be the end of a great program.

thanks,
melanie