Formulas stored as text

[sraasch]

I'm trying to copy text-formatted cells containing formulas from a "template" sheet to another sheet where the formulas will be calculated.

I'm storing the formulas as text because I've been unable to stop Excel 2010 from corrupting the formulas (it seems to replace some of the formulas with "#Name" text). Changing the cells to text formatting fixes this.

Due to the large number of cells I'm copying and that I want to retain formatting from the template sheet, I'm using Range.copy to copy the formulas.

I've seen and tried a technique that uses TextToColumns after the copy operation, and this seems to work. I'm hoping for a "prettier" solution.

I understand that using the Evaluate macro may help with this, but I'm concerned about speed of operation during the COPY phase as well as the during the calculation.

Thanks for reading.

 

[Joe4]

Welcome to the Board!

I'm storing the formulas as text because I've been unable to stop Excel 2010 from corrupting the formulas (it seems to replace some of the formulas with "#Name" text).

It might be better to address the source of the problem. Formulas which worked prior to Excel 2010 should work in Excel 2010, unless it is a User Defined Function which you have not copied over, or the Formula uses a Function from an Add-In which you have not activated in your new version of Excel.

Can you post one of the formulas that causes this error?

 

[sraasch]

Thanks.

A Sample formula looks like this:

=IF(Source="Default",Default_Val,UserVal)

Where "Source", "Default_Val", and "UserVal" are names defined in the destination sheet, but not in the template sheet

Excel would often (not always) corrupt the cell containing the formula, leaving "#Name?" in its place.

 

[Joe4]

I haven't worked much with templates, but is it possible to add those named ranges to your templates to avoid the errors?

 

[sraasch]

It's possible, but the templates are intended to be edited by people w/out detailed knowledge of how the guts of the tool work.

So, while addressing the origin of the wierdness would be nice, I don't see a good way of attacking it.

 

[Joe4]

I guess it doesn't make sense to me how you are using this template.

If it is to be used to by people who don't understand the guts of it, and it uses named ranges, but the named ranges aren't in the template, wouldn't the users them have to physically add the named ranges to it to get it work (meaning they would have to know something about it to get it to work)?

 

[sraasch]

The named ranges are present in the template as text fields. The ranges are relative and defined by the VBA to essentially allow the formulas to access the one cell on the row that corresponds to the column.

So, the VBA adds the named ranges based on the text columns. The users don't have to know anything about names, only that they can access a field based on the name for the column.

The user enters text into the template sheet, and the VBA builds the sheet doing the calculations.

Complex, I know, but will work well for this audience.

 

[Joe4]

I don't think it is a good idea to have formulas on your worksheet that reference named ranges before they exist. Here is how I think I would approach this.

If you are naming the ranges via VBA, why not have VBA add the formulas that used these named ranges at run-time too? That should eliminate those errors you are getting because of your formulas referencing named ranges that do not exist as of yet.

 

[sraasch]

I want the user to be able to add the equation (and column headers, coloring, etc) w/out changing the VBA. As I understand your suggestion, the user would have to add the equation to the VBA.

 

[Joe4]

I want the user to be able to add the equation (and column headers, coloring, etc) w/out changing the VBA. As I understand your suggestion, the user would have to add the equation to the VBA.

Now I am totally confused. If it is the user who is adding the equation, and it is not already in the template, then why would it be in there before he adds it, creating these errors you are talking about?

It sounds like there might be a lot going here with this template, and how to use it, that without us understanding exactly how it is supposed to work, makes it very difficult to provide the advice you are looking for.