Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a formula that sets a cell value to #N/A (to skip it in a chart). Now I'm replacing the formalas by VBA code; I could set the cal formula to =NA() like Range("A1").formula ="=NA()"


    However this fails in different languages as there =NA() is called different.

    Two questions:
    (1) How do I set a cell value to #N/A without being dependent on the language installed
    (2) How do I set formulas in a cell, e.g. =AVERAGE("A1:A10") without being dependent on the language installed

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't understand why this would fail - if you assign a formula using VBA, you should always specify formula in English, regardless of the language of the user interface.

    If I execute the following line in the Dutch language version of Excel:

    Range("B1").Formula = "=AVERAGE(A1:A10)"

    the result will be

    =GEMIDDELDE(A1:A10)

    because GEMIDDELDE is the Dutch name for the AVERAGE function.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799930' date='26-Oct-2009 11:25']I don't understand why this would fail - if you assign a formula using VBA, you should always specify formula in English, regardless of the language of the user interface.

    If I execute the following line in the Dutch language version of Excel:

    Range("B1").Formula = "=AVERAGE(A1:A10)"

    the result will be

    =GEMIDDELDE(A1:A10)

    because GEMIDDELDE is the Dutch name for the AVERAGE function.[/quote]

    Are you sure? Did you actually TRY this out? Note that you're setting a STRING as formula, I believe I had seen before that this failed.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I am sure - I've tested this hundreds, if not thousands of times. It works perfectly for "ordinary" formulas.

    Problems arise if you try to set a formula involving the INDIRECT function, because the argument to INDIRECT is a string that will be evaluated at runtime in the user interface, so you need to specify the range in the interface language.

    By the way, to set an error value in a cell, you can use code like this:

    Code:
    With Range("A1")
      .Formula = "=NA()"
      .Value = .Value
    End With

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can also use:
    Code:
    activecell.Value = cverr(xlerrna)
    FWIW. I agree about the Formula though - it's US-style required, including list separators as I recall.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='rory' post='799935' date='26-Oct-2009 12:40']it's US-style required, including list separators as I recall.[/quote]
    That's correct: numbers should use the point as decimal separator, and arguments must be separated by commas, regardless of your system settings.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799938' date='26-Oct-2009 13:20']That's correct: numbers should use the point as decimal separator, and arguments must be separated by commas, regardless of your system settings.[/quote]
    So also here, I don't need this???

    With Range("DataIn")
    .Rows(1).Select
    With .FormatConditions
    .Delete
    If Lang <> 31 Then
    .Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)"
    Else
    .Add Type:=xlExpression, Formula1:="=REST(RIJ(),2)" 'Dutch
    End If
    End With
    <etc>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [s]Indeed, the line

    .Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)"

    should work whatever the interface language is.[/s]

    I apologize, this does NOT work - unlike cell formulas, formulas for FormatConditions should be specified using the interface language!

    (And this is a big nuisance if you don't know which language your users will have!)

Posting Permissions

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