Limitation of SWITCH statemanet

R

rolfb

Hi,
is there a limitation to the number of expressions which can be used in a
switch?

the following works:
------------------------
Switch([Enterprise Project Text6]="Peso (ARG)";3,0788;[Enterprise Project
Text6]="$ (AUS)";1,3574;[Enterprise Project Text6]="Real";2,2523;[Enterprise
Project Text6]="$ (CDN)";1,1197;[Enterprise Project Text6]="Peso
(CHIL)";548,2456;[Enterprise Project Text6]="Renminbi";8,0077;[Enterprise
Project Text6]="Peso (CO)";2558,199;[Enterprise Project
Text6]="Colon";512,36;[Enterprise Project Text6]="Kuna";5,7782;[Enterprise
Project Text6]="Koruna (CZ)";22,6552;[Enterprise Project Text6]="Krona
(DK)";5,9277;[Enterprise Project Text6]="$ (HK)";7,7663;[Enterprise Project
Text6]="Forint";220,022;[Enterprise Project Text6]="Krona (IS)";74,89)
------------------------
If I want to add one additional expression I get an error.

Any help or advice is appreciated.

Rolf
 
J

JackD

There is a limit to the number of characters at 255 or something like that.
Maybe if you abbreviate the country codes to two characters you could
squeeze another expression in there. Another thing would be the remove the
( ) around the country abbreviations.

-Jack Dahlgren
 
J

John

JackD said:
There is a limit to the number of characters at 255 or something like that.
Maybe if you abbreviate the country codes to two characters you could
squeeze another expression in there. Another thing would be the remove the
( ) around the country abbreviations.

-Jack Dahlgren
Click to expand...

Jack,
Yeah, that's the first thing I thought also, but if you do a quick
count, the formula posted has well over 255 characters already (closer
to 600).

John
rolfb said:
Hi,
is there a limitation to the number of expressions which can be used in a
switch?

the following works:
------------------------
Switch([Enterprise Project Text6]="Peso (ARG)";3,0788;[Enterprise Project
Text6]="$ (AUS)";1,3574;[Enterprise Project
Text6]="Real";2,2523;[Enterprise
Project Text6]="$ (CDN)";1,1197;[Enterprise Project Text6]="Peso
(CHIL)";548,2456;[Enterprise Project Text6]="Renminbi";8,0077;[Enterprise
Project Text6]="Peso (CO)";2558,199;[Enterprise Project
Text6]="Colon";512,36;[Enterprise Project Text6]="Kuna";5,7782;[Enterprise
Project Text6]="Koruna (CZ)";22,6552;[Enterprise Project Text6]="Krona
(DK)";5,9277;[Enterprise Project Text6]="$ (HK)";7,7663;[Enterprise
Project
Text6]="Forint";220,022;[Enterprise Project Text6]="Krona (IS)";74,89)
------------------------
If I want to add one additional expression I get an error.

Any help or advice is appreciated.

Rolf
Click to expand...
Click to expand...
 
J

JackD

Perhaps 512 is the limitation then?
This formula certainly looks too long.
-Jack


John said:
JackD said:
There is a limit to the number of characters at 255 or something like
that.
Maybe if you abbreviate the country codes to two characters you could
squeeze another expression in there. Another thing would be the remove
the
( ) around the country abbreviations.

-Jack Dahlgren
Click to expand...

Jack,
Yeah, that's the first thing I thought also, but if you do a quick
count, the formula posted has well over 255 characters already (closer
to 600).

John
rolfb said:
Hi,
is there a limitation to the number of expressions which can be used in
a
switch?

the following works:
------------------------
Switch([Enterprise Project Text6]="Peso (ARG)";3,0788;[Enterprise
Project
Text6]="$ (AUS)";1,3574;[Enterprise Project
Text6]="Real";2,2523;[Enterprise
Project Text6]="$ (CDN)";1,1197;[Enterprise Project Text6]="Peso
(CHIL)";548,2456;[Enterprise Project
Text6]="Renminbi";8,0077;[Enterprise
Project Text6]="Peso (CO)";2558,199;[Enterprise Project
Text6]="Colon";512,36;[Enterprise Project
Text6]="Kuna";5,7782;[Enterprise
Project Text6]="Koruna (CZ)";22,6552;[Enterprise Project Text6]="Krona
(DK)";5,9277;[Enterprise Project Text6]="$ (HK)";7,7663;[Enterprise
Project
Text6]="Forint";220,022;[Enterprise Project Text6]="Krona (IS)";74,89)
------------------------
If I want to add one additional expression I get an error.

Any help or advice is appreciated.

Rolf
Click to expand...
Click to expand...
Click to expand...
 
J

John

JackD said:
Perhaps 512 is the limitation then?
This formula certainly looks too long.
-Jack
Click to expand...

Jack,
I agree, the formula is way too long, it should have been broken into
manageable parts at the onset. I didn't do a full count but it looks
like the total character count is more like 600+ so it just doesn't fit
any convenient power of 2.

John
John said:
JackD said:
There is a limit to the number of characters at 255 or something like
that.
Maybe if you abbreviate the country codes to two characters you could
squeeze another expression in there. Another thing would be the remove
the
( ) around the country abbreviations.

-Jack Dahlgren
Click to expand...

Jack,
Yeah, that's the first thing I thought also, but if you do a quick
count, the formula posted has well over 255 characters already (closer
to 600).

John
Hi,
is there a limitation to the number of expressions which can be used in
a
switch?

the following works:
------------------------
Switch([Enterprise Project Text6]="Peso (ARG)";3,0788;[Enterprise
Project
Text6]="$ (AUS)";1,3574;[Enterprise Project
Text6]="Real";2,2523;[Enterprise
Project Text6]="$ (CDN)";1,1197;[Enterprise Project Text6]="Peso
(CHIL)";548,2456;[Enterprise Project
Text6]="Renminbi";8,0077;[Enterprise
Project Text6]="Peso (CO)";2558,199;[Enterprise Project
Text6]="Colon";512,36;[Enterprise Project
Text6]="Kuna";5,7782;[Enterprise
Project Text6]="Koruna (CZ)";22,6552;[Enterprise Project Text6]="Krona
(DK)";5,9277;[Enterprise Project Text6]="$ (HK)";7,7663;[Enterprise
Project
Text6]="Forint";220,022;[Enterprise Project Text6]="Krona (IS)";74,89)
------------------------
If I want to add one additional expression I get an error.

Any help or advice is appreciated.

Rolf
Click to expand...
Click to expand...
Click to expand...
 
J

JulieS

John said:
Jack,
I agree, the formula is way too long, it should have been broken into
manageable parts at the onset. I didn't do a full count but it looks
like the total character count is more like 600+ so it just doesn't
fit
any convenient power of 2.

John
Click to expand...
<snip>

Ah, the power of Word's word count :)
600 characters (no spaces)
637 characters (with spaces)

Frankly I'm surprised at over 600 characters. However, I haven't been
able to find any reference to the exact number allowed in a formula.

Julie
 
R

rolfb

Hi JackD,
may be the formula looks too long and I can certainly reduce the number of
characters used.
However, I need to setup a switch construct that supports 36 different cases
which is used for currency conversion.

Is there a better way to do it?

Thanks

Rolf

JackD said:
Perhaps 512 is the limitation then?
This formula certainly looks too long.
-Jack


John said:
JackD said:
There is a limit to the number of characters at 255 or something like
that.
Maybe if you abbreviate the country codes to two characters you could
squeeze another expression in there. Another thing would be the remove
the
( ) around the country abbreviations.

-Jack Dahlgren
Click to expand...

Jack,
Yeah, that's the first thing I thought also, but if you do a quick
count, the formula posted has well over 255 characters already (closer
to 600).

John
Hi,
is there a limitation to the number of expressions which can be used in
a
switch?

the following works:
------------------------
Switch([Enterprise Project Text6]="Peso (ARG)";3,0788;[Enterprise
Project
Text6]="$ (AUS)";1,3574;[Enterprise Project
Text6]="Real";2,2523;[Enterprise
Project Text6]="$ (CDN)";1,1197;[Enterprise Project Text6]="Peso
(CHIL)";548,2456;[Enterprise Project
Text6]="Renminbi";8,0077;[Enterprise
Project Text6]="Peso (CO)";2558,199;[Enterprise Project
Text6]="Colon";512,36;[Enterprise Project
Text6]="Kuna";5,7782;[Enterprise
Project Text6]="Koruna (CZ)";22,6552;[Enterprise Project Text6]="Krona
(DK)";5,9277;[Enterprise Project Text6]="$ (HK)";7,7663;[Enterprise
Project
Text6]="Forint";220,022;[Enterprise Project Text6]="Krona (IS)";74,89)
------------------------
If I want to add one additional expression I get an error.

Any help or advice is appreciated.

Rolf
Click to expand...
Click to expand...
Click to expand...
 
J

John

JulieS said:
<snip>

Ah, the power of Word's word count :)
600 characters (no spaces)
637 characters (with spaces)

Frankly I'm surprised at over 600 characters. However, I haven't been
able to find any reference to the exact number allowed in a formula.

Julie
Click to expand...

Julie,
So my ballpark estimate was pretty accurate. Note the exact string
length could also be done with a simple use of the Len function in VBA
and yes, when counting characters in a string, spaces are included.

The answer must be out there somewhere. I guess we didn't consider the
possibility that the entry for that last expression actually had an
error and that's why the user got an error message. Sometimes we miss
the obvious.

John
 
J

JackD

A VBA Macro can have any number of cases. It just won't be updated until you
run the macro unlike a custom formula which works whenever the project
recalcs. Since it looks like your expression only really needs to be run
once after someone inputs a value, using a macro would be a reasonable thing
to do.

Open Project, hit ALT+F11 to enter the visual basic editor and then you can
write a macro.
I have a number of examples at my site: http://zo-d.com/blog
Look in the Programming category.
If that doesn't get you started come back here and we can help.

As an added advantage, a macro could check an external file to get the
latest exchange rates, otherwise you are going to be editing those numbers
as you go quite often.

-Jack




rolfb said:
Hi JackD,
may be the formula looks too long and I can certainly reduce the number of
characters used.
However, I need to setup a switch construct that supports 36 different
cases
which is used for currency conversion.

Is there a better way to do it?

Thanks

Rolf

JackD said:
Perhaps 512 is the limitation then?
This formula certainly looks too long.
-Jack


John said:
There is a limit to the number of characters at 255 or something like
that.
Maybe if you abbreviate the country codes to two characters you could
squeeze another expression in there. Another thing would be the remove
the
( ) around the country abbreviations.

-Jack Dahlgren

Jack,
Yeah, that's the first thing I thought also, but if you do a quick
count, the formula posted has well over 255 characters already (closer
to 600).

John

Hi,
is there a limitation to the number of expressions which can be used
in
a
switch?

the following works:
------------------------
Switch([Enterprise Project Text6]="Peso (ARG)";3,0788;[Enterprise
Project
Text6]="$ (AUS)";1,3574;[Enterprise Project
Text6]="Real";2,2523;[Enterprise
Project Text6]="$ (CDN)";1,1197;[Enterprise Project Text6]="Peso
(CHIL)";548,2456;[Enterprise Project
Text6]="Renminbi";8,0077;[Enterprise
Project Text6]="Peso (CO)";2558,199;[Enterprise Project
Text6]="Colon";512,36;[Enterprise Project
Text6]="Kuna";5,7782;[Enterprise
Project Text6]="Koruna (CZ)";22,6552;[Enterprise Project
Text6]="Krona
(DK)";5,9277;[Enterprise Project Text6]="$ (HK)";7,7663;[Enterprise
Project
Text6]="Forint";220,022;[Enterprise Project Text6]="Krona
(IS)";74,89)
------------------------
If I want to add one additional expression I get an error.

Any help or advice is appreciated.

Rolf
Click to expand...
Click to expand...
Click to expand...
 
Top