Unexpected Value Returned on Count

[theteerex]

Public NumRows As Long

The number of used rows on Worskheet 1. Variable is public because multiple macros use it.

Private Sub Workbook_Open()
  NumRows = Worksheets("TableSize").Range("A1").Value
  MsgBox "NumRows = " & NumRows
End Sub

Calculates the value of the variable NumRows, which is the number of rows used on Sheet 1. MsgBox was inserted simply to verify code was working.
Sheet TableSize only has information in A1.

Private Sub Worksheet_Calculate()


    Dim n As Long
    n = Worksheets("TableSize").Range("A1").Value [B][COLOR=#008000]'A1 contains the formula "=ROW(INDEX(Sheet1,1,1))+ROWS(Sheet1)-1" used to count rows on Sheet 1[/COLOR][/B]
    MsgBox n & "NumRows=" & NumRows [B][COLOR=#ff0000]'For some reason NumRows returns 0 here when it returns 32 above. n returns 32 like it should[/COLOR][/B]
    If n = NumRows Then Exit Sub
    If n > NumRows Then Call NewDatabaseEntry
    
    NumRows = n


End Sub

Am I missing something crucial?

 

[Fluff]

Where have you put this

Public NumRows As Long

 

[mole999]

This I believe is the same post as > https://www.mrexcel.com/forum/excel...ot-met-excel-vba-post5036892.html#post5036892 though now you have identified a count is wrong