title: converting formula into string vba
body: <p>My excel file has 3 different type of VBA functions. In a sheet, I want to show the value from the defined name function. Initially I used this code to convert formula into string:</p>
  <pre><code>Function EvaluateString(strTextString As String)
      Application.Volatile
      EvaluateString = Evaluate(strTextString)
  End Function
  </code></pre>
  <p>This function was placed in a individual module. The problem with this is that it runs in every module causing the running time for my other modules to be extremely slow.</p>
  <p>I tried placing this function in a module itself, but it does not work. It gave me an error of &quot;Only comments may appear after End Sub, End Function, or End Property&quot;.</p>
  <p>This is my code when I received that prompt</p>
  <pre><code>Sub PasteAsText()
  
  Dim lastRow As Long
  lastRow = Worksheets(&quot;Data_Column&quot;).Cells(Rows.Count, 1).End(xlUp).Row
  
  Function EvaluateString(strTextString As String)
      Application.Volatile
      EvaluateString = Evaluate(strTextString)
  End Function
  
  Worksheets(&quot;Data_Column&quot;).Range(&quot;D1:D&quot; &amp; lastRow).Clear
  
  Worksheets(&quot;Data_Column&quot;).Range(&quot;C1:C&quot; &amp; lastRow).Copy
  Worksheets(&quot;Data_Column&quot;).Range(&quot;D1:D&quot; &amp; lastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
  Worksheets(&quot;Data_Column&quot;).Range(&quot;D1:D&quot; &amp; lastRow).NumberFormat = &quot;@&quot;
  Worksheets(&quot;Data_Column&quot;).Select
  
  End Sub
  
  </code></pre>