MS ecel 2010 macro fails - unable to update cell's formula

Question

0.00/5 (No votes)

See more:

My MS Excel Macro (subroutine) that updates cell formula began to fail (after the latest Microsoft update 2017-03-10). Now when this section of code runs MS Excel halts then tries to recover. The line of code causing the stoppage is the re-assignment back to the cell with the new/changed formula. I tested the line of code by pointing to an empty cell and it runs successful. Pointing it back to a cell already filled the code fails. I've google extensively possible causes without finding a solution. Your assistance is greatly appreciated. Below code snippet shows what I've am doing:

Code snippet:

VB

  '   Insert new row in "Register"; Push down rows; automatic update row numbers
  '   iRowInsert code inserts new row (oWS1.Range("A5").EntireRow.Insert)
  Call iRowInsert
  
  Dim oWS1, oWS2, i, j, k, orgFormula, newFormula
  Dim oRNG, maxRows, maxCols, rowStart, rowCurrent
  
    
  Set oWS1 = Worksheets("Register")
  Set oWS2 = Worksheets("Budget")

  '   Identify range beginning row number
  rowStart = 5
  rowCurrent = rowStart
  '   Retrieve cell's formula
    orgFormula = oWS2.Cells(rowCurrent, 4).Formula
  '   orgFormula is "=SUMIFS(Register!$I$6:$I412,Register!$K$6:$K412,D$1,Register!$H$6:$H412,$A5)"
  '   Replace original formula's starting row number
    newFormula = Replace(newFormula, "$6:", "$4:")
    oWS2.Cells(rowCurrent, 4).Locked = False     ' really not needed - workbook not protected
  '   *******  fails when re-setting a filled cell's formula *****
    oWS2.Cells(rowCurrent, 4).Formula = newFormula
  '   ************************************************************
'     oWS2.Cells(rowCurrent, 3).Value = newFormula ' works correctly when inserted into empty cell
     oWS2.Cells(rowCurrent, 4).Locked = True     ' really not needed - workbook not protected

What I have tried:

(1) Google similar problems and reviewed suggested results
(2) Double/Triple checked format's content and structure -
(3) Ran code against an empty cell - Successful insert
(4) Ran code against a filled cell - Failed

Posted 18-Mar-17 4:23am

Member 11875489

Updated 19-Mar-17 10:42am

Nelek

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2017-03-18T06:25:00

Solution 1

read carefully what the camment says and what next line do !

VB

'   Replace original formula's starting row number
  newFormula = Replace(newFormula, "$6:", "$4:")

When you don't understand what your code is doing or why it does what it does, the answer is debugger.
Use the debugger to see what your code is doing. Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute, it is an incredible learning tool.

Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]
Basic Debugging with Visual Studio 2010 - YouTube[^]

The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't find bugs, it just help you to. When the code don't do what is expected, you are close to a bug.

[Update]

Quote:
I've run debugger and verify correct contents stored in both variables, origFormula and newFormula

You have checked that your expectation is correct, and it is, you need to check that your code match the expectation too, and it is not.

VB

newFormula = Replace(newFormula, "$6:", "$4:")

I told you to reread very carefully this line.

Posted 18-Mar-17 6:25am

Patrice T

Updated 19-Mar-17 7:05am

v3

Comments

Member 11875489 19-Mar-17 10:21am

I've run debugger and verify correct contents stored in both variables, origFormula and newFormula. Macro immediately and unexpectedly aborts with execution of newFormula value assignment to replace origFormula stored in the cell. When an empty cell is used to receive the newFormula it stores correctly. (?? no error message is shown - just recovery attempt)

Member 11875489 19-Mar-17 10:25am

Additionally, this marco successfully ran for over 2 years without a code change. Now it fails.

Bryian Tan 19-Mar-17 12:59pm

Thanks to Microsoft update I guess :)

Patrice T 19-Mar-17 13:06pm

No, read update.

Member 11875489 19-Mar-17 14:27pm

BTW: I trapped this error, reported error number is 1004 (short title "Trust Center...". I've double-checked security option, which I set this option to "Enable all Macros" (not a good idea for the long term). I've unchecked Locked options for both worksheets (commented out code lines for unlock/lock). Created Self-Signed Certificate. Macro fails (same 1004 error number).... time to pass my newly discovered problem along to Microsoft.

Patrice T 19-Mar-17 15:33pm

Unless the code in the question is not the code you use, it can't work because this line is wrong, it don't do what the comment says.

Member 11875489 21-Mar-17 9:38am

Resolved problem. Inserted "...Cells(x, y).ClearContents" after Locked=false. Worksheet unprotected, according to locked specification the worksheet must be protected. Before inserting ClearContent the returned error number is 1004. After adding the clear cell instruction the returned error number is 0 (fixed for now).

Thanks for your valued information and suggestions.

MS ecel 2010 macro fails - unable to update cell's formula

1 solution

Solution 1

Add your solution here

Preview 0

MS ecel 2010 macro fails - unable to update cell's formula

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us