Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Multilanguage translation
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Multilanguage translation
Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references have
different names in the different countries. How can I easily adapt a
script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Multilanguage translation
Hi Simon

Can you send me a simple test workbook(private) that will blow in a German version
I go install a German version today on a Virtual PC to test this

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Simon" wrote in message oups.com...
Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references have
different names in the different countries. How can I easily adapt a
script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,253
Default Multilanguage translation


replace with english R1C1 references and you should be fine.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Simon wrote :

Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references
have different names in the different countries. How can I easily
adapt a script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Multilanguage translation
That's I was thinking also

Thanks for confirm it keepITcool
No need for installing the German version then for me

--
Regards Ron de Bruin
http://www.rondebruin.nl


"keepITcool" wrote in message ft.com...


replace with english R1C1 references and you should be fine.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Simon wrote :

Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references
have different names in the different countries. How can I easily
adapt a script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,440
Default Multilanguage translation
I don't think you have to.
If I use the Multilingual option it translates automatically, I assume (not
guarantee!) that happens with standard versions as well.
Otherwise, use the A1 reference style.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Simon" wrote in message
oups.com...
Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references have
different names in the different countries. How can I easily adapt a
script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,253
Default Multilanguage translation

I tested it also with functions in the strings..
although localized functions and localized R1C1 are accepted...

I think you should stick to A1 references & functions in usenglish)
Relative refs will be correct if written for for the first cell in
range.


With Range("A6:A100")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"RC6<1)*(RC2+RC3<=R4C2)*(RC2<"""")", xlR1C1, xlA1, , .Cells(1))
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=(R4C2=RC2)*(RC2+RC3=R4C2)*(RC2<"""")", xlR1C1, xlA1, ,
..Cells(1))

With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Simon wrote :

Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references
have different names in the different countries. How can I easily
adapt a script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,440
Default Multilanguage translation
Excel translates functions and reference styles in conditional formats just
as it does worksheet formulas.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"keepITcool" wrote in message
ft.com...

I tested it also with functions in the strings..
although localized functions and localized R1C1 are accepted...

I think you should stick to A1 references & functions in usenglish)
Relative refs will be correct if written for for the first cell in
range.


With Range("A6:A100")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"RC6<1)*(RC2+RC3<=R4C2)*(RC2<"""")", xlR1C1, xlA1, , .Cells(1))
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=(R4C2=RC2)*(RC2+RC3=R4C2)*(RC2<"""")", xlR1C1, xlA1, ,
.Cells(1))

With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Simon wrote :

Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references
have different names in the different countries. How can I easily
adapt a script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,253
Default Multilanguage translation

hmm.. s't flawed in copy.paste :)

Sub x()
'Add Conditional formatting
With Range("A6:A100")

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=RC6=100%", _
xlR1C1, xlA1, , .Cells(1))
.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=(RC6<1)*(RC2+RC3<=R4C2)*(RC2<"""")", _
xlR1C1, xlA1, , .Cells(1))
.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=(R4C2=RC2)*(RC2+RC3=R4C2)*(RC2<"""")", _
xlR1C1, xlA1, , .Cells(1))

With .FormatConditions(1).Font
.Strikethrough = True
End With
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With

End With
End Sub

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :


I tested it also with functions in the strings..
although localized functions and localized R1C1 are accepted...

I think you should stick to A1 references & functions in usenglish)
Relative refs will be correct if written for for the first cell in
range.


With Range("A6:A100")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"RC6<1)*(RC2+RC3<=R4C2)*(RC2<"""")", xlR1C1, xlA1, ,
.Cells(1)) With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=(R4C2=RC2)*(RC2+RC3=R4C2)*(RC2<"""")", xlR1C1, xlA1, ,
.Cells(1))

With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,253
Default Multilanguage translation
niek..

true .. ONCE you have a working formula.

when setting the formula:

in cells you can choose to assign
US/Local A1/r1c1
and with

in formatconditions you cant...
(excel will do it's best to get it right,
attempting USenglish/a1 first

similar to
Cells(1).VALUE = "=sum(a1:a100)"





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Niek Otten wrote :

Excel translates functions and reference styles in conditional
formats just as it does worksheet formulas.
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Multilanguage translation
Hi everybody,
thanks for your contribution to solving my problem. There is no more
multilanguage problem, thanks a lot!

*** Sent via Developersdex http://www.developersdex.com ***
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
multilanguage custom menus Tony Excel Programming 7 May 2nd 05 10:24 PM
multilanguage custome menu Tony Excel Discussion (Misc queries) 0 May 2nd 05 12:11 AM
Value translation Sheldon Excel Programming 1 September 28th 04 09:16 PM
Multilanguage issue Von Shean Excel Programming 1 January 22nd 04 10:03 AM
Need Translation Phil Hageman Excel Programming 1 July 25th 03 03:19 PM


All times are GMT +1. The time now is 11:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement