Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

We have a lot of spreadsheets and there is a need to upgrade an important Addin.

The change will involve converting an old formula to a new one. It is not a straightforward change because the name and number of parameters are not the same.

Therefore we are investigating how to do it automatically. The new addin is only available in new PCs. It is not installed on my workstation.

Let's say the old formula is oldf and the new one newf

My code will do somehting like this:

ws.Cells.Item(cell_r, cell_c).Formula = "=newf(A1, B2, """CCC""")"

Then I will call workbook.saveAs() to save to a new workbook

Now when I open the new workbook in the new PC, I can see the cell becomes '#NAME'. If I select the cell, do a F2 and then F9, it will work as expected. But it defeats the whole purpose of the automation.

My theory is: when I set the cell to a new formula in my Old PC, since the excel COM object has no idea of the existence of the new addin, so it marks the cell in some invalid internal state. It will not refresh even if it opens in the new PC.

What can be done to fix it?

share|improve this question
    
What happens if you make the change on a PC with the new add-in or is this not an option? I've seen this behaviour before and the simpliest & quickest solution was to just get users to recalc the workbooks on first using the new add-in. That's not such a big deal is it? –  creamyegg Mar 16 '12 at 9:52

2 Answers 2

up vote 1 down vote accepted

No matter what, since you are modifying the workbooks on a pc that does not have the new function, the workbooks will all have to be recalculated once they are on a pc with the new function so that they will actually use the new function. You might need to double-check that, when you open the new workbooks on the new PCs, Excel Calculation is set to automatic.

If you don't want the worksheets to complain about the invalid name, you can create a dummy new function in the add-in on your computer.

Public Function NewF(r As Range, r2 As Range, s As String) As Boolean
   NewF = OldF(r, s)
End Function
share|improve this answer

Simplest solution is to open the new workbook and press CTRL-ALT-F9 to force a global recalc on the whole thing. You should only have to do that once, I'd think.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.