Excel : Refreshing specific formulas in worksheet programmatically.

Question

Using Application.CalculateFull() i can refresh entire worksheet. Is there a way to refresh only specific formulas in the worksheet and not the entire worksheet?

Answer 1

Range("A3:C5").Calculate 'calculate all the cells from A3 to C5

OR

Range("A3").Calculate  'only calculate cell A3

Answer 2

Hey what i mean is i have developed an excel plugin.. It exposes 3 user defined formulas (x, y , z) to excel application. All that works fine.. Now i need to add a refresh button that when clicked will refresh only those (x, y and z) formulas in the worksheet. So in the button's click handler what code i need to write?? Writing Application.CalculateFull() refreshes all the formulas. Thats not what i want.

Answer 3

what i mean is i have developed an excel plugin.. It exposes 3 user defined formulas (x, y , z) to excel application. All that works fine.. Now i need to add a refresh button that when clicked will refresh only those (x, y and z) formulas in the worksheet. So in the button's click handler what code i need to write?? Writing Application.CalculateFull() refreshes all the formulas. Thats not what i want.

My top suggestion to achieve this would be to do a Find/Replace of the name of your user defined function (UDF) across the entire workbook. For example, if your UDF were named "MyFunction" you would want to replace "MyFunction(" with "MyFunction(".

Assuming that your Excel.Application reference were named 'excelApp', code to do a find/replace across all workbooks using C# could look as follows:

foreach (Excel.Workbook workbook in excelApp.Workbooks)
{
    foreach (Excel.Worksheet worksheet in workbook.Worksheets)
    {
        worksheet.Cells.Replace(
            "MyFunction(",
            "MyFunction(",
            Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByRows,
            false,
            Type.Missing,
            Type.Missing,
            Type.Missing);
    }
}

Other lesser ideas:

(a) Utilize a Real-Time Data (RTD) server. This is usually utilized for having data such as stock prices update frequently or continuously. This could also be used, however, to execute your "Refresh On Demand" approach. I personally think that this would be overkill in in this case, as Find/Replace works 100% perfectly and is far easier to implement, but RTD could definitely be used here. For an article on how to do this, see Building Excel Real-Time Data Components in Visual Basic .NET it's geared towards VB.NET, not C#, but the principles are identical.

(b) Add another parameter to your function MyFunction(x, y, z, Refresh). By having each function reference the 'Refresh' cell, which can be placed on a hidden worksheet or hidden workbook, you can change the 'Refresh' cell to force all your functions to recalculate. The downside is that it requires this extra, dummy parameter.

(c) Stick with Application.CalculateFull(), as this is by far the easiest to code. I understand the performance issues, but it is extremely easy to code (just one line) and is 100% reliable.

Overall, I think that I would go with the Find/Replace approach. It is a clean, simple, and very effective technique for selective recalculations.

-- Mike

Answer 4

Thanks a lot for your answer. The find/replace one seems just fine for my purpose.