Complex math - language issue

**cfuttrup** · 05-14-2010, 03:22 PM

Hi all.

I hope someone can help me out with the problem described here.

I've discovered that the complex math in Excel is badly implemented, since in english and danish, the different names means that the complex math stops working. For example we have:

English: IMSUB
Danish: IMAGSUB

and

English: IMPRODUCT
Danish: IMAGPRODUKT

If I create the spreadsheet in danish, it doesn't work in english version of Excel.

I thought I'd make a macro, which could search/replace these text strings. I'd place a
button and the user could choose language him-/herself. Just started looking into macros two days ago.

First I did a macro recording, which didn't continue to work. Simply because when I did the search/replace, the macro "forgets" to choose the entire workbook. It only searches the active sheet.

I continued by putting the words I want to search/replace into some cells. They are placed in Sheet "DK" in cell range B104-C104 as you can see below.

I coded the following:

Please Login or Register  to view this content.

Unfortunately the macro doesn't find anything to replace ... I am at a loss here. Running "BREAK" and "WATCH" the expressions DK1 and EN1 (stepping through the macro with F5) I can see that they stay empty ...

Please help me accomplish what I'd like to do.

Best regards,
Claus

**davegugg** · 05-14-2010, 05:11 PM

Maybe you could use the built-in find/replace function to just change those functions in the formulas in your cells:

Please Login or Register  to view this content.

**cfuttrup** · 05-14-2010, 06:11 PM

Hi Dave.

Thanks. Your version works. It also seems relatively simple.

Is there any way in which I can change the "fixed" strings (IMAGSUB, IMSUB) into variables that I can load from some cells in the spreadsheet? I attempted to do that - just so that e.g. a German (or e.g. French) user could substitute the danish complex math functions for their own language ditto ...

Best regards,
Claus

**davegugg** · 05-17-2010, 11:26 AM

Yes, you can replace them with cells on a sheet, or you could use input boxes so the user types them directly.

Please Login or Register  to view this content.

Examples of each is shown in the code above.

**squiggler47** · 05-17-2010, 11:45 AM

Thats interesting, I thought that excel files used a token system in <2007 versions, which would mean the function names should automatically convert between languages!

**cfuttrup** · 05-21-2010, 02:13 PM

Dear davegugg

Thank you very much for your help. I seem to run into the "missing defining the variable" ... because other code examples doesn't show this part. Your examples are most helpful.

Dear squiggler47

Yes it is interesting that complex math doesn't convert ... it's only a problem with the complex functions (which are activated by enabling Analysis ToolPak), AFAIK.

Best regards,
Claus