1

Running a VBA function which uses the excel SUM function seems to not work when Excel uses another interface language.

Here on stackoverflow some have encounterd language issues but this is related to putting formulas language independent in cells. 

Function sum_value(sum_range) As Single
    sum_value = Application.WorksheetFunction.Sum(sum_range)
End Function

sum_value(1,2,3) = 6

Solution On the Microsoft site I could not find that it is possible to use WorksheetFunctions as methods of the application Object. Relevant documentation below:

JeroenDV
  • 125
  • 2
  • 14

1 Answers1

0

If I understand correctly, you would like to be able to use the English functions as well? Unless you completely omit words in your UDF, it won't really be language independent.

Place this function in a module.

Public Function SUM(Range As Range) as Long

SUM = Application.SUM(Range)

End Function

Calling the SUM() function should then be possible within the WS.

In VBA, EN-US functions can always be used.

  • .Formula uses English formulas
  • .FormulaLocal uses formulas based on the regional version
  • The same counts for .FormulaR1C1 and .FormulaR1C1Local
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
  • Yes, the functions will be written in English to ease collaboration on the code, However I cant force users to use a certain language. So the option you suggested is to be used within the worksheet, not within VBA? To be able to use EN function within VBA it would look something like ``` sum_value = sum_range.formula = "sum()" ``` – JeroenDV Apr 04 '19 at 13:17
  • If you're not planning to place formulas in a sheet with VBA, I don't see the problem. You should be able to use `Application.Sum()` without trouble. – Tim Stack Apr 04 '19 at 13:33
  • Great! I could not find documentation directly on the microsoft site that you can use WorksheetFunctions as Methods of the application object. – JeroenDV Apr 05 '19 at 09:11