Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Language problem?? (97 (&maybe 2000?))

    Here's a piece of code that runs fine on a English version of EXCEL (97 & 2000). A colleage of mine who has a German version of EXCEL 97 gets an error message in the RED line...

    Range("L2:L100").Select
    With Selection
    .FormatConditions.Delete
    <font color=red>.FormatConditions.Add Type:=xlExpression, Formula1:="=(RC[1]>=8)"</font color=red>
    .FormatConditions(1).Font.ColorIndex = 3
    End With

    I think it has something to do with the square brackets ([ ]) but I'm not completely sure...

    Anyone with a clue and a fix?

    Erik Jan

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Language problem?? (97 (&maybe 2000?))

    What is the error message and the translation? Try exporting the macro in the English version and importing it into the German version. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    I think it has to do with the RC notation. If I change this (on my Dutch version) to absolute addressing, then it works. Can't figure out why for the moment.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    Can you have a macro recorded using RC notation on the German version and see what syntax is used. Perhaps square brackets have some significance for the German version.

    Hans :
    Did the original code work on your Dutch system ?.

    Andrew C

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    Andrew,

    The original code did not work on my Dutch system. I tried it out on my English system, where it worked fine. However, when I record a macro (when the RC reference style is used), I get something like this

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""2*R6C4"""

    In case of absolute addresses, I got:

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""2*$A$1"""

    Maybe it has to do with the quotes. In any case, all these different 'formula' expressions work fine on an English system.

  6. #6
    Lounger
    Join Date
    May 2001
    Location
    Germany
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    On my German system, the following code (recorded) works fine:

    .FormatConditions.Add Type:=xlExpression, Formula1:="=ZS(1)>8"

    You can use the RC notation in code on German systems, I have done that before. But I think, in this combination (with Formula1 and quotations), Excel expects the formula to be written exactly as it would be entered into a local Excel version's worksheet cell. Which is, in the case of German, not RC but ZS - Z for Zeile (row) and S for Spalte (column).

    Doris

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    Doris,

    Whilst the code you posted (Formula1:="=ZS(1)>8") wil run Ok, it will provide a different result than the required code which uses square brackets [] (Formula1:="=ZS[1]>8"). If you check the results in the Conditonal Formatt dialog box you will see the difference, =(L4(1)>=8) as opposed to =(M4>=8).

    However your main point about Z S in place of R C is interesting and useful, thank you.

    Andrew C

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    All,

    Thanks a lot for your feedback. It took some time before I was able to test out some of your suggestions with my German colleague. Here's my findings:

    The German error message:
    Laufzeitfehler '5': Unzul

  9. #9
    Lounger
    Join Date
    May 2001
    Location
    Germany
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    Hi Andrew,
    the way I understood the problem, it was required that the conditional formatting checks the cell to the right of the active cell (in case of L4 that would be M4) to see whether it is larger than 8.
    On my system, the code: Formula1:="=ZS(1)>=8" does exactly this: applied to L4, it produces in the Conditional Formatting check box a: =M4>=8.
    ZS(-1) would check the cell to the left, K4, ZS the cell itself, L4.
    Square brackets, ZS[1], are not accepted and produce an error: "05 Invalid procedure call or invalid argument".
    Doris

  10. #10
    Lounger
    Join Date
    May 2001
    Location
    Germany
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    Hi Erik Jan,
    you could try the Application.LanguageSettings Object. I found the following macro in the Visual Basic Help and run it on my Excel to determine the language:

    Sub LanguageTest()
    MsgBox "The following locale IDs are registered " & _
    "for this application: Install Language - " & _
    Application.LanguageSettings.LanguageID(msoLanguag eIDInstall) & _
    " User Interface Language - " & _
    Application.LanguageSettings.LanguageID(msoLanguag eIDUI) & _
    " Help Language - " & _
    Application.LanguageSettings.LanguageID(msoLanguag eIDHelp)
    End Sub

    The result is 1031, which seems to be the code for German.
    I don't know whether it is the Install Language, msoLanguageIDInstall, which counts in your case, or the User Interface Language, msoLanguageIDUI.
    Doris

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    Thanks,

    Works for me...(1033) but when I run on the German (EXCEL 97 !!) system of my colleague we get error messages.

    I checked the references but they seem identical (apart from the fact that on my EX2000 the EXCEL 9.0 Object library is referenced while on my colleague's EX97, this reference is to the 8.0 Library).

    When I type Application and then a point in the Immediate window, I do NOT get "LanguageSettings" as one of the possible words in the pop-up...

    Now what??? Should I load something else? Is there another way that will work?
    Could I do an API-call maybe??

    Erik Jan

  12. #12
    Lounger
    Join Date
    May 2001
    Location
    Germany
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    Arrghh, right, the Application.LanguageSettings object seems to be new in Excel 2K.
    I just checked the Excel 97 Visual Basic Help and it is not there.
    Now, I hope someone else has a new idea ...
    Doris

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    Have a look at a previous <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=30375&Search= true&Forum=xl&Words=language&Match=And&Searchpage= 2&Limit=10&Old=allposts&Main=30365>post</A>.

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    I don't know if this might work, but can't you use the absolute reference style, add the expression for the conditional formatting and then switch back to the relative reference style, like this:

    <pre> Range("C1:C7").Select
    Application.ReferenceStyle = xlA1
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=2*A1"
    Application.ReferenceStyle = xlR1C1
    </pre>


    If you do this, with only these 2 quotes, then the formula 2*A1 is converted to the 'relative address' formula

    =2*RK[-2]

    which is the Dutch version.

  15. #15
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Language problem?? (97 (&maybe 2000?))

    Thanks Hans... that did it! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •