title: Excel VBA Make Function Volatile based on User selected Style Options
body: <p>I'm trying to make a UDF that validates against User defined style options, specifically in this circumstance, for <code>ListObjects</code>, but I was hoping for an universal answer.</p>
  
  <p>Take an example table: Table1</p>
  
  <p>My UDF is as follows, for demonstration purposes we are in cell A2:</p>
  
  <pre><code>=TableHeaderExists("Table1")
  </code></pre>
  
  <p>The function returns a result:</p>
  
  <pre><code>Public Function TableHeaderExists(table_name As String) As Boolean
  
    TableHeaderExists = ThisWorkbook.ActiveSheet.ListObjects(table_name).ShowHeaders
  
  End Function
  </code></pre>
  
  <p>A2 now returns a result. But it does not recalculate when the condition changes by user unchecking the check box in the 'Design Tab'.</p>
  
  <p>So I added <code>Application.Volatile</code> to the Function. </p>
  
  <p>A2 still does not recalculate. So I removed <code>Application.Volatile</code> and added <code>ActiveSheet.Calculate</code></p>
  
  <p>In this case it works for one change, and one change only. </p>
  
  <p>What I did notice, is if I edit any data in the table, ANY data at all, those cells with the validation function are then recalculated, but not when Style Options are altered. </p>
  
  <p>So I then tried to get a result by validating against a range for the header and that too works just like validating against 'ShowHeaders', only updates after body data is altered somehow, not appearance:</p>
  
  <pre><code>Public Function TableHeaderExists(table_name As String) As Boolean
  
  Dim tbl As ListObject
  Dim hdrRng As Range
  
  Application.Volatile
  
  Set tbl = ActiveSheet.ListObjects(table_name)
  Set hdrRng = tbl.HeaderRowRange
  
  If Not hdrRng Is Nothing Then
   TableHeaderExists = True
  Else
   TableHeaderExists = False
  End If
  </code></pre>