Excel Macro, вставляя международно действующую формулу во время выполнения

У меня есть электронная таблица Excel с макросом, который вставляет условное форматирование, например:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=UND($A3=""" & lastName & """; $B3=""" & firstName & """)"

Как вы можете видеть, я использовал немецкую формулу для "И" (т.е. "UND" ), и, очевидно, этот код не работает, как только я использую его на французской или английской версии Excel. Обычно формулы локализуются автоматически, но как я могу вставить формулу во время выполнения, которая будет работать во всех версиях?

Ответы

Ответ 1

Хорошо, спасибо, что помогли мне в этом, вы помогли мне взломать этот.

На самом деле невозможно просто использовать английский. Можно использовать английский при работе над формулой, например. путем установки кодирования Range("A1").formula="AND(TRUE)", но это не работает с FormatConditions.

Мое решение - это функция, которая временно записывает формулу в ячейку, читает ее через свойство FormulaLocal и возвращает локализованную формулу, например:

Function GetLocalizedFormula(formula As String)
' returns the English formula from the parameter in the local format
  Dim temporary As String
  temporary = Range("A1").formula
  Range("A1").formula = formula
  Dim result As String
  result = Range("A1").FormulaLocal
  Range("A1").formula = temporary
  GetLocalizedFormula = result
End Function

Возвращенная формула может быть использована на FormatConditions, которая будет повторно локализована или не локализована, когда документ будет позже открыт на другой языковой версии Excel.

Ответ 2

Сохраните (тривиальную версию) формулу в (скрытой) ячейке в вашей книге.

Затем, когда вы открываете книгу, формула будет автоматически переводиться с помощью excel для пользователя.

Теперь вам просто нужно проанализировать эту формулу в script (найдите открытую скобку "(" и возьмите последнее слева от этого:

Используйте что-то вроде:

strLocalizedFormula = Mid(strYourFormula, 2, InStr(1, strYourFormula, "(") - 2)

где strYourFormula будет копией формулы из вашего рабочего листа.

Я надеюсь, что это работает, поскольку я использую только английскую среду.

Кроме того, прочитав это: http://vantedbits.blogspot.nl/2010/10/excel-vba-tip-translate-formulas.html Я думаю, вы должны (только) иметь возможность использовать английскую версию формулы ячейки из VBA.

Ответ 3

Может быть, попробуйте это (непроверено, поскольку у меня есть только английская версия insatlled)

Напишите свою международную версию формулы в сторону ячейки, использующей Range.Formula. Затем верните его из Range.FormulaLocal и напишите эту строку в FormatConditions

Ответ 4

Я только нашел очень элегантное решение проблемы на немецком форуме Excel. Это не записывается в фиктивную ячейку, а использует временный именованный диапазон. Я использовал оригинальную идею (кредит для bst), чтобы написать переводную функцию для обоих направлений.

Преобразование формулы локализованной формулы в английский:

Public Function TranslateFormula_LocalToGeneric(ByVal iFormula As String) As String
    Names.Add "temporaryFormula", RefersToLocal:=iFormula
    TranslateFormula_LocalToGeneric = Names("temporaryFormula").RefersTo
    Names("temporaryFormula").Delete
End Function

Преобразование английской формулы в локализованную формулу:

Public Function TranslateFormula_GenericToLocal(ByVal iFormula As String) As String
    Names.Add "temporaryFormula", RefersTo:=iFormula
    TranslateFormula_GenericToLocal = Names("temporaryFormula").RefersToLocal
    Names("temporaryFormula").Delete
End Function

Это очень удобно, если вам нужно обрабатывать формулы в условном форматировании, так как эти формулы всегда хранятся в виде локализованных формул (но вам может понадобиться их общая версия, например, для использования Application.Evaluate(genericFormula)).

Ответ 5

Спасибо всем! Я нашел сообщение очень полезным.

Мое решение - это комбинация других, я добавляю его на случай, если кто-то найдет его полезным.

Dim tempform As String
Dim strlocalform1 As String
Dim strlocalform2 As String

' Get formula stored in WorksheetA Cell O1 =IFERROR(a,b)
tempform = Worksheets("Sheet").Range("O1").Formula

' Extract from the formula IFERROR statement in local language.
strlocalform1 = Mid(tempform, 2, InStr(1, tempform, "(") - 1)

' Extract from the formula separator , (comma) in local settings.
strlocalform2 = Mid(tempform, InStr(1, tempform, "a") + 1, 1)

' Add formula in local language to desired field.
pvt.CalculatedFields.Add Name:="NewField", Formula:="=" & strlocalform1 & "FORMULA" & strlocalform2 & ")"

Надеюсь, это поможет!