VBA created formulas won't autocalculate

[Frans Muller]

There is a problem, which i've been struggling with for some time now.
I've created a workbook with a macro that creates worksheets within
the workbook. The macro also enters formulas refering to cells of
other worksheets.
At first everything seems to work properly in the new worksheet.

The problem is that when I run the macro again to create another
worksheet, the formulas in the previous created worksheet fail to
autocalculate although "autocalculate" is on. Pressing <F9> doens't
work as well. The only thing that works is to enter the cell and then
press <enter>. However the formulas in the new worksheet do work
properly, right until another new worksheet is created.

Has anybody encountered this problem, and is so.... any solutions?

Thanks in advance,

Frans Muller

The part of the VBA code that creates the worksheet:

Function Naar_vergelijk()
Dim Naam As String
Dim naam5 As String
If ActiveCell.Row < 6 Or ActiveCell.Row > 98 Then
Show_Error ("Blad kan niet worden weergegeven, deze valt
buiten de selectie.")
Exit Function
End If
Naam = SetSheetName
naam5 = "Ink" + Right$(Naam, Len(Naam) - 4)
On Error GoTo Foutafhandeling
Sheets(Naam).Select
x = 0
If x = 0 Then Exit Function
Foutafhandeling:
Werkboek_beveiliging_opheffen
errorhandling Naam
Sheets(Naam).Visible = True
Sheets(naam5).Visible = True
Sheets(Naam).Select
Werkboek_beveiliging_zetten
End Function

Function errorhandling(naam3)
Dim naam4 As String
Dim teller As String
Dim teller2 As String

Resultaat_Beveiliging_Opheffen
naam4 = "Ink" + Right$(naam3, Len(naam3) - 4)
Select Case Error
Case Is = Error(9)
Sheets("Ink_standaard").Copy Before:=Sheets(1)
Sheets(1).name = naam4
Sheets(naam4).Move Before:=Sheets("resultaat")

Worksheets("rekenblad").Cells(3, 1).Value = formule1
Sheets("Verg_standaard").Copy Before:=Sheets(1)
Sheets(1).name = naam3
Sheets(naam3).Move Before:=Sheets("resultaat")

Worksheets(naam4).Range("A6:A42").NumberFormat = "General"
Worksheets(naam4).Range("C6:C42").NumberFormat = "General"
Worksheets(naam4).Range("E6:E42").NumberFormat = "General"
formule1 = "=" + naam3 + "!$A$"
formule2 = "=" + naam3 + "!$F$"
formule3 = "=" + naam3 + "!$N$"
Worksheets("rekenblad").Cells(1, 2).Value =
Worksheets("rekenblad").Cells(1, 1).Value + 5
teller2 = Worksheets("rekenblad").Cells(1, 2).Value
formule4 = "=Keuze!$E$"
onderwerp = "=Keuze!$D$"
For x = 9 To 45

formule = formule1 & x
Worksheets(naam4).Cells(x - 3, 1).Formula = formule

formule = formule2 & x
Worksheets(naam4).Cells(x - 3, 3).Formula = formule

formule = formule3 & x
Worksheets(naam4).Cells(x - 3, 5).Formula = formule
Next x
formule = onderwerp + teller2
Worksheets(naam3).Cells(4, 2).Formula = formule
formule = formule4 + teller2
Worksheets(naam3).Cells(5, 7).Formula = formule

Worksheets(naam4).Cells(3, 2).Formula = formule
Worksheets(naam4).Cells(3, 6).Formula =
Worksheets("rekenblad").Cells(1, 1).Value
Worksheets(naam3).Cells(2, 3).Formula =
Worksheets("rekenblad").Cells(1, 1).Value
Worksheets(naam4).Range("A6:A42").NumberFormat = ";;@"
Worksheets(naam4).Range("C6:C42").NumberFormat =
"#,##0.00_-;[Red]#,##0.00-;;@"
Worksheets(naam4).Range("E6:E42").NumberFormat =
"#,##0.00_-;[Red]#,##0.00-;;@"
regel = Worksheets("rekenblad").Cells(1, 1) + 9
Select Case regel
Case 10 To 38
regel1 = regel
Case 39 To 66
regel1 = regel + 2
Case 67 To 92
regel1 = regel + 4
End Select

Worksheets("resultaat").Cells(regel1, 1) = formule
Worksheets("resultaat").Cells(regel1, 3) = "=" + naam4 +
"!$C$57"
Worksheets("resultaat").Cells(regel1, 6) = "=" + naam4 +
"!$E$57"
Worksheets("resultaat").Cells(regel1, 10) = "=" + naam4 +
"!$C$59"

End Select
Resultaat_Beveiliging_Zetten
Nieuw_Beveiliging_Zetten naam3
Nieuw_Beveiliging_Zetten naam4
End Function

 

[Bob Phillips]

Frans,

Try preceding your code with

Application.Volatile

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Patrick Molloy]

Frans is using 'function' rather than 'sub' in that these
are code modules and not UDF's
Application.Volatile doesn't work.
I suspect that within his code he needs to add something
like

Worksheets(naam4).Calculate

....although if Shift+F9 doesn't work, then this probably
won't

 

[bruce taylor]

If the formulae refer to the new sheet in anyway, it my
require the file be saved before they will reliably
calculate

 

[Frans Muller]

Thanks for the quick reply.

I did try the application.volatile statement, unfortunately it did not
help.
But I have to say that I'm not sure where to place the statement. I
placed it in the beginning of the Function Naar_vergelijk.

For now, I redesigned the workbook so that it doesn't need to enter
formulas in the worksheet, and that the transportation of the data is
manually by a macro.

Still, it is a problem I've encountered several times now. And the
advantages of entering formulas in a worksheet by macro are too great
to let go, and I hope to find a "cure" someday before I'm old and
grey.

Frans

 

[Niek Otten]

Hi Frans,

I found it a bit difficult to see what exactly you were trying to do.
Nevertheless, a few general remarks about functions and macros.
You can not change anything in worksheets from within a function, just from
a macro.
All data the function needs to do what it has to do, should be declared as
arguments and given as arguments to the actual function call. Any direct
reference to cells in worksheets will not be recognized bij Excel; therefore
they will not recalculate reliably. "Application.Volatile" is often
recommende to cure this, but there are still doubts as to Excel will know
the order of recalculation. How could it know?
So in general, always pass your data as arguments.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel