Search code examples
excelvbaexcel-2013custom-formatting

Excel VBA 2013 Customised Sub only Pasted not executed in cell with use of FormulaR1C1

I've written a specific sub for formatting data from another cell.

Sub Macro3()
    '>> First record to get the original code in Testing.xlsm <<
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=fG_ReFormattingInvoiceNumber(RC[-3],3)"
    Range("H5").Select
    ActiveWorkbook.Save

    '>>> I wrote in my application this line => ReInvoicingWBook.Sheets _
    '    (G_sheetNameREINVOICinG).Range("U5").FormulaR1C1 = _
    '    "=fG_ReFormattingInvoiceNumber(RC[-19],3)"
End Sub

The problem is that ReInvoicingWBook.Sheets(G_sheetNameREINVOICinG).Range("U5") Cell is just filled with the formula but it is not executed.

ReInvoicingWBook is a file opened by the Excel application I'm developing and writing inside it.

Yet, calling to fG_ReFormattingInvoiceNumber() works nice in the original file Testing.xlsm/ Sub Macro3 where I captured VBA lines.

The 1st thing I see is the fact that ReInvoicingWBook is a pointer to a file XLSM that is monitored by my application. Perhaps, it's a reason...

Any solution or help from you are appreciated.

Solution

  • You can force a calculation of cells by using .Calculate.

    Sub Macro3()
    Range("F5").FormulaR1C1 = "=fG_ReFormattingInvoiceNumber(RC[-3],3)"
    Range("F5").Calculate
    ActiveWorkbook.Save
End Sub

    As a general rule, always use Option Explicit, avoid using .Select and .Activate. Also, fully qualify your ranges (which is something I have not done in the example above because I have no context).