Foreign Excel Issue?

James_Latimer · Sep 8, 2011

Hello,

I was wondering if someone could give me some information please. I have written some code that works fine on excel 2003 and 2007 here in the UK. I have emailed the file to a colleague in Germany who is getting system errors.

His set-up is excel 2003 and is set exactly as mine is (same service pack etc) but his returns System 13 errors.

The code is quite long so i didnt want to post it all on here if i didnt need to.

I have been told that when using a version of excel that isnt in english it converts the text into German (or whichever country it is) and it may be a translation issue!?!?

I'm not sure if the person who told me this was pulling my leg or not.

If anyone could give me some pointers it would be much appreciated!

Many thanks

Norie · Sep 8, 2011

VBA is basically American English wherever it's used.

Formulas and some other things can be language specific, for example different names for functions, different separators etc.

Can you post some of the code?

Ideally the part that's causing the problem.

James_Latimer · Sep 8, 2011

Thanks for the quick response Norie! Much appreciated.
The code is as follows...

Code:

    For PSA_Run = 1 To NumberOfPSAs
    
        Wid = 222 * (PSA_Run / NumberOfPSAs)
        PercDone = (PSA_Run / NumberOfPSAs) * 100
             
        Form_Progress.lbl_Time = "Running simulation " & FormatNumber(PSA_Run, 0, , , vbUseDefault) & " of " & FormatNumber(NumberOfPSAs, 0, , , vbUseDefault) & ".  Approximately " & _
        TimeIterationTotal & " remaining."
        
        Form_Progress.Lbl_Progress.Width = Wid
        Form_Progress.Lbl_Percentage = Round(PercDone) & "% Complete"

        TimeIterationTotalCalc = (TimeIterationEnd - TimeIterationStart) * (NumberOfPSAs - PSA_Run)
        TimeIterationTotal = Format(TimeIterationTotalCalc / (24# * 60 * 60), "hh:mm:ss")
        
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 0) = PSA_Run
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 1) = Sheets("Results_Standard").Range("r_prolastin_cost")
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 2) = Sheets("Results_Standard").Range("r_prolastin_qalys")
        
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 3) = Sheets("Results_Standard").Range("r_no_treat_cost")
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 4) = Sheets("Results_Standard").Range("r_no_treat_qaly")
        
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 5) = Sheets("Results_Standard").Range("r_marg_cost")
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 6) = Sheets("Results_Standard").Range("r_marg_qalys")
                
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 7) = Sheets("Results_Standard").Range("r_marg_cost") / Sheets("Controls").Range("patient_number")
        
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 8) = Sheets("Results_Standard").Range("r_marg_qalys") / Sheets("Controls").Range("patient_number")
        
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 9) = Sheets("Results_Standard").Range("p_ICER")
        
                        
               
        Calculate
               
       DoEvents
       
       If PSA_Run = 1 Then
        TimeIterationEnd = Timer
        End If

The line with the error is...

Code:

Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 7) = Sheets("Results_Standard").Range("r_marg_cost") / Sheets("Controls").Range("patient_number")

Surely named ranges dont get translated.... do they?

Again many thanks for the assistance!

Norie · Sep 8, 2011

James

I wouldn't think so.

What's the actual error message, in English?

Off the top of my head I think it's 'Type mismatch'.

If that us the case it could be caused by a number if things.

It could actually be something in the data/formulas on the worksheets.

I notice that that line of code, and the next, refer to a named range called patient_number.

Might be worth checking that out.

James_Latimer · Sep 8, 2011

So sorry, the error is a type 13 mismatch. I failed to mention that important fact! (please refer to my user account image)

I think i have found the issue but am unsure of a work around.

Using the value of 500,000.00 as an example, in the Germany version this number is formatted as 500.000,00 (basically a comma for a decimal place and a full stop as the thousand seperator).

In this instance my vba code is dividing 448.371,59 by 773 (which i am assuming is causing the error).

How would i go about taking this into account? Not sure how to handle this.

Sorry for being stupid!!!

Norie · Sep 8, 2011

You could try just adding .Value at the end of each range reference

Code:

Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 7).Value = Sheets("Results_Standard").Range("r_marg_cost").Value / Sheets("Controls").Range("patient_number").Value
        
        Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 8).Value = Sheets("Results_Standard").Range("r_marg_qalys").Value / Sheets("Controls").Range("patient_number").Value

Normally you don't really need that but perhaps since you are dealing with different number formats you do.

I've actually been caught out by not using Value in the past, so I tend to add it automatically now.

Almost forgot, did you check what the named range actually refers to?

James_Latimer · Sep 8, 2011

I made the amendments and my colleague says he has got the same errors.

The named ranges refered to the cells that they should be refering to.

Any ideas?

Norie · Sep 8, 2011

James

Either add watches to all the elements of the expression, eg Range("parent_name") or separate it out into temporary variables.

Something like this perhaps:

Code:

MargCost = Sheets("Results_Standard").Range("r_marg_cost")
PatientNo = Sheets("Controls").Range("patient_number")
 
Debug.Print "MargCost=" & MargCost
 
Debug.Print "PatientNo=" & PatientNo
 
Sheets("PSA_Summary").Range("p_PSAStart_CET").Offset(PSA_Run, 7) = MargCost / PatientNo

That should allow you to monitor what their values is.

You don't need the Debug.Print but it might be handy to see what the values of are on every iteration of the code.

Actually, that's one thing I forgot to ask.

Does the code fail on the first iteration or does it run a few then fail?-

Of course that would probably only happen if the values were changing.

James_Latimer · Oct 4, 2011

Apologies for the delayed reply!

It was bad formula that was causing the issue.

I found a few formulas that were including the value in the speech marks...
 for example

=IF(Model_Scenario=0; "61,838,154"; "4,450,446")
The value 61,838,154 in Germany is 61.838.154 to us, the two "decimal places" was screwing it all up.

after amending it to be
 =IF(Model_Scenario=0; 61838154; 4450446)

it worked fine

sorry for being stupid, ive no idea for the life of me why i did the formula that way.

oopsy.

thanks to all who had a look though. much appreciated!

Norie · Oct 4, 2011

James

I did kind of mention formulas in post #4.

Mind you it was buried in the middle of some other ramblings and didn't give much detail.

Foreign Excel Issue?

James_Latimer

Active Member

Excel Facts

Norie

Well-known Member

James_Latimer

Active Member

Norie

Well-known Member

James_Latimer

Active Member

Norie

Well-known Member

James_Latimer

Active Member

Norie

Well-known Member

James_Latimer

Active Member

Norie

Well-known Member

Similar threads

Forum statistics

Share this page

Foreign Excel Issue?

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock