Is it posible to "paste" a Dutch translation of an Ecel formula in a cell

[stranno]

Is it possible to paste a dutch translation of an Excel Formula in a cell (via VBA) Without the use of SendKeys "{F2}~" (and let it work).
I dont like the use of SendKeys. One of the reasons is the problem with numlock. Besides that in my program is an userform active which causes
complications in combination with SendKeys. And after AppActivate ("Microsoft Excel"), to shift the focus from the UserForm to the active worksheet,
the cell selection becomes problematic. For instance de code line Range("A4").select does select Range("A5") on the worksheet. How bizar.
So for me no SendKeys.

In the attached workbook i included 4 macros. Some of them fail. I tried for many hours to find a acceptable solution. But i stuck.
I tried evaluation, volatile, etc etc. Nothing works.

Ideally, I would like to see that Test1 (name of macro) would work. Thus not paste from the clipboard.

Who has an any idea?

The reason i want to do this, is to make it possible for users to insert an Excel formula in a combobox (my users work with a Dutch version).
The combobox value

The difference with a normal Formula is that the user doesn't refer to cells but to column numbers which are being calculated in a macro.
For instance : a user insert "=Jaar (var1)" (translated: = Year(var1). "var1" represents the name of a certain column head. let say a persons "day of birth".
The macro tries to find this column head (day of birth) in de first row and returns the column number (=Coln).

So if w = "=" & "Jaar(var1)" and Coln = the returned column number (e.g. 26) Which wille be converted into "Z" then:
via Replace(w; "var1";Coln) we get "=Jaar(Z2)" (the row number is Always 2, after that the formula will be dragged down.)

This last formula (which is in Dutch) will be put in a certain cell. And this does not work.

Ofcourse you may skip the reason why i want to make this possible. But some people like to know.

[stranno]

I think i found a possible solution (not an elegant one).

I noticed that if you paste a copy from a text string (e.g. an Excel formula in a foreign language) from a temporarily created shape into a cell, then it works.
Excel recognizes the text as an formula.

The attached workbook shows an an example. The macro will be activated by clicking the commandbutton on the userform.

More suggestions?

[snb]

If you use in VBA:

cells(1)="=year(today())"

your Dutch users will see:

"=jaar(vandaag())"

[stranno]

Yes but my users compose the formulas themself (in Dutch). So they type "=jaar(vandaag())" and not cells(1)="=year(today())"
Moreover they type in a combobox not in a normal cell on a worksheet. Then (after a kind of translation) the contents of this combobox will be transferred to a cell.

Also the users don't use "normal" Excel fomulas like i explained before. I would suggest, to take a look at my second sent workbook. This will give you an idea.

Consider that "=Jaar(A1)" (In this case) has not been typed by the user. It has been converted by code into "=Jaar(A1)". The original formula could have been = Jaar(var1)
whereby var1 refers to (is a substitute for) a particular column head.

[Aflatoon]

Did you try the Range.FormulaLocal property?

[stranno]

Never heard of it before. I'll Have a look.

[stranno]

Did you try the Range.FormulaLocal property?
No, doesn't help.

[SamT]

This might be a use for a Dictionary (Scripting). Have a csv file with dutch to VBA (English) pairs. (Csv for easy adding to) Have the Code fill a dictionary from the csv.

Then literally translate the Dutch strings to VBA before pasting them into cells. Let Excel retranslate the formula back to Dutch as per snb

snb has a neat one line code to parse any string into an array

Then it's just

For each it In a
If Exists(Dictionary(it) then Formula = Replace(Formula, it, Dictionary(it)
NextRplace? Substitute? I' have to look it up.

[snb]

if you use


cells(1).formulalocal="=jaar(vandaag())"

the result in cell A1 is "#Name?"

if you use


cells(1)="=year(today())"

the result of
msgbox cells(1).formulalocal

is

"=jaar(vandaag())"

[stranno]

I got the same result as you snb: #Name?

Tsam there is yet another issue. The arguments in English formulas are separated by commas, where in the dutch formulas colons are used. Ofcourse that's not the biggest problem, but nevertheless.
I also don't know whether the principles of the functions are comparable (analog) in both languages. And ofcourse it's a bunch of work.

But guys have you looked at my post #2?:

Sub Test()
Dim sh As Shape
Dim TextbShape As Shape
w = "=jaar(a1)"
Application.ScreenUpdating = False
Set TextbShape = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 50, 130, 500, 30)
TextbShape.TextFrame2.TextRange.Characters.Text = w
TextbShape.TextFrame2.TextRange.Copy
Range("D1").Select
ActiveSheet.Paste
TextbShape.Delete
Set TextbShape = Nothing
'Drag formula down
Range("D1").AutoFill Destination:=Range("D1:D5"), Type:=xlFillDefault
End Sub

This macro does exactly what i want, but i it's not very robust. I mean writing a text (Dutch formula) to a shape, copy it from there, transfer it to a cell and finally delete the shape. But it works.

snb, you suggested: "if you use cells(1)="=year(today())".... but the thing is, i (or the user) do(es) not use that. I/they use: "=jaar(vandaag())" And if you store this in a variable and place it
in a cell, the function does not work. You tried it youself. It only works after sendkeys ({F2}~)

[SamT]

Tsam there is yet another issue. The arguments in English formulas are separated by commas, where in the dutch formulas colons are used.
That is one line of code

[Jan Karel Pieterse]

ActiveCell.FormulaLocal = "=JAAR(VANDAAG())"

should work, but ONLY in a Dutch Excel. It works on my Dutch 2010. The fact that you get a #NAME error indicates you are on English Excel which does not "speak" Dutch function names.
FormulaLocal in an English Excel requires English function names, BUT uses the Windows list separator instead of the US comma.
Why are you entering Dutch function names in an English Excel? Better to start learning the English Function names instead.

[stranno]

I have no indication that i work with an English version of Excel. What i see is all in Dutch. And the program that i make is not meant for myself. It's meant for Dutch users who work with a Dutch version of Excel. I know how to deal with English Formulas. But i don't think the users are willing to do so.

Samt, you're right. It's not a big deal. I'll give it a try. But not now. Tomorrow i've an appointment with the guys who are going to use this program. So for the time being i will use my own dirty solution.

[Jan Karel Pieterse]

In that case, the FormulaLocal property SHOULD work. It always has for me.
If I change this line of code in your Test1 routine:


Cells(1, 2).Formula = "=" & wN

to


Cells(1, 2).FormulaLocal = "=" & wN

it just works.

[stranno]

Nu breekt me de klomp (well damn it). Now it works indeed. Did i do somthing wrong??? Or what. Yesterday it did not work. But that can't be.
This is just what i need. Thanks Jan Karel for your perseverance with me.
Also thanks Aflatoon for your idea.


snb, do you have an English version of Excel?