Calculated Field Office 2003 Settings

mel1s2

New Member
Joined
Jul 16, 2005
Messages
48
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I think you had better post the offending code. Are all your users in the same country?
 

mel1s2

New Member
Joined
Jul 16, 2005
Messages
48
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

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

mel1s2

New Member
Joined
Jul 16, 2005
Messages
48

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 16, 2005
Messages
48
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
 

Forum statistics

Threads
1,136,517
Messages
5,676,314
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top