Formulae exceeding 1024 characters

  • Thread starter Thread starter Geoff Goodacre
  • Start date Start date
G

Geoff Goodacre

I'm supporting an application using Excel 97/VBA that
needs to consolidate totals from corresponding cells in
many different workbooks into another workbook. The
technique being used in code is to cycle through the
workbooks and build the formula concatenating to the
previous formula each time.

This works well except that the file names and sheet names
are quite long (to make them descriptive for users) and in
some cases the formula can exceed the 1024 limit by a
factor of 2 or 3.

Is there another way to approach this that is, if not
unlimited, less expensive in use of formula characters?
Unfortunately I can't just accumulate totals or use the
Consolidate function because of the user requirements.

It is an option to upgrade the users to Excel 2002 or 2003
if necessary.
 
While I question how useful filenames that can make a formula 3000
characters long really are, you can shorten your formula considerably by
defining the filenames as workbook names:

Choose Insert/Name/Define:

Name in workbook: shortName
Refers to ="<big hairy long file name>"

Then substitute shortName in your formulae
 
JE McGimpsey said:
While I question how useful filenames that can make a formula 3000
characters long really are,
Click to expand...

Unfortunately, lots of us are forced to work on Windows XP, where the only
root folder write enabled for the average guy is

C:\Documents and Settings\<username>\My Documents\

Not only is it not-too-useful (as you mentioned) and childishly named, it
makes all paths wider than Windows' non-resizable File dialogs (Open, Save,
Browse, ...). Now put some meaningfully named subfolders in there, and you
have it.

Best wishes Harald
Followup to newsgroup only please
 
Very many thanks, this solves our problem.
-----Original Message-----
While I question how useful filenames that can make a formula 3000
characters long really are, you can shorten your formula considerably by
defining the filenames as workbook names:

Choose Insert/Name/Define:

Name in workbook: shortName
Refers to ="<big hairy long file name>"

Then substitute shortName in your formulae


.
Click to expand...
 
Harald Staff said:
Not only is it not-too-useful (as you mentioned) and childishly named,
Click to expand...

Yeah, whenever I'm told that I work on a non-serious computer one of my
(many) retorts often involves the saccharine cutesiness of "My xxxx".

Uggghhh...
 
Yeah, whenever I'm told that I work on a non-serious computer one of my
(many) retorts often involves the saccharine cutesiness of "My xxxx".
Click to expand...

FWLIW, it ain't the computer, it's the OS. No question Mac OS whatever is better
than Windows whatever (where whatever is any version sold in the last 6 years).
Standard PCs are actually usable when running under Linux, under which home
directories are /home/<username>/whateverelse. Nice short sensible pathnames.
 
...
...
Unfortunately, lots of us are forced to work on Windows XP, where the only
root folder write enabled for the average guy is

C:\Documents and Settings\<username>\My Documents\
Click to expand...
...

Out of idle curiosity, does WinXP still provide the character mode SUBST.EXE
command? If so, can the average user 'map' local drives using it? As in

ConsolePrompt> subst Z: "C:\Documents and Settings\<username>\My Documents"

If this works, can WinXP users still add shortcuts to batch files in Startup
folders? If so, is this the sort of pure user convenience feature that Microsoft
has never bothered to show Windows system admins?
 
Out of idle curiosity, does WinXP still provide the character mode SUBST.EXE
command? If so, can the average user 'map' local drives using it?
Click to expand...

I have no idea, Harlan. I have administrator priviledges on my computers,
and I <moderate> am not exactly in love with Win XP </moderate>, so
motivation to learn more about it is almost absent.
 
Harald

From Windows XP Help and Support..............

Subst

Associates a path with a drive letter. Used without parameters, subst displays
the names of the virtual drives in effect.

Syntax
subst [drive1: [drive2:]Path]

subst drive1: /d

Parameters
drive1:
Specifies the virtual drive to which you want to assign a path.
drive2:
Specifies the physical drive that contains the specified path (if different
from the current drive).
Path
Specifies the path that you want to assign to a virtual drive.
/d
Deletes a virtual drive.
/?
Displays help at the command prompt.
Remarks
The following commands do not work, or should not be used, on drives used in
the subst command:
chkdsk
diskcomp
diskcopy
format
label
recover
The drive1 parameter must be within the range specified by the lastdrive
command. If not, subst displays the following error message:
Invalid parameter - drive1:

Examples
To create a virtual drive Z for the path B:\User\Betty\Forms, type:

subst z: b:\user\betty\forms

Now, instead of typing the full path, you can reach this directory by typing
the letter of the virtual drive, followed by a colon, as follows:

z:

Gord
 
...
...
From Windows XP Help and Support..............

Subst ...
The drive1 parameter must be within the range specified by the lastdrive
command. If not, subst displays the following error message:
Invalid parameter - drive1:
Click to expand...
...

Nice to see Microsoft is as negligent about updating online help for Windows as
it is for Excel. There's no LASTDRIVE setting ('command'?) in WinNT/2K/XP.
Clearly a hold-over from DOS 3.x. Fills one with great confidence in the OS to
know that online help hasn't been revised since the late 1980s.
 
Back
Top