title: Excel formulas replaced with values sporadically after using macro
body: <p>I wrote a macro for Excel to populate data from one internal file to an external monthly report. As is, 90-95% of the time, it works perfectly. However, I have found that occasionally, it appears to turn the equations in the original file into values, which then forces me to have to fix all of the equations before I run the next month's report. </p>
  
  <p>The code (below) works by running from the internal report and opening the most recent external report (or one selected by the user). In both reports, there are matching named ranges. The macro then iterates through the named ranges and sets the external report's ranges to equal the value of the internal report's ranges. </p>
  
  <p>Currently, I populate client reports from nearly identical internal reports about 15 times a month, and despite all of my best efforts, I cannot appear to replicate the problem. It just seems to do it sometimes and not others. I usually do not even find out that the original formulas are gone until the following month (though, now I am paying more attention to the potential problem). </p>
  
  <p>I read that if Excel copies a range from one instance of Excel to another, it can cause the original equations to be <a href="http://database.ittoolbox.com/groups/technical-functional/excel-l/formulas-disappeared-in-excel-file-4665988" rel="nofollow">set to values</a>, but I do not believe that my code should be creating separate instances of Excel, and when I added a code to try to warn me when a second instance was started, it did not seem to address the problem. Has anyone experienced a similar issue or does anyone have any idea of what might be going on (either in how to reproduce or to fix it?). </p>
  
  <p>Thank you for you help. </p>
  
  <p>FYI: I am running Excel 2010 on Windows 7. Also, the files are hosted on a shared drive, and therefore are not running of my local machine. </p>
  
  <pre><code>Option Explicit
  Dim wbSeoReport As Workbook 'Internal workbook
  Dim wbClientReport As Workbook 'workbook for clients
  Dim sSeoReportName As String
  Dim sClientFileName As String 'wbClientReport Name
  Dim sFilePath As String 'Folder containing this file
  
  Sub Populate_Client_File()
  
  Dim replace_page As Worksheet
  Dim sCompanyName As String 'Client company name
  Dim sClientFileNameAndPath As String 'wbClientReport Name and path for opening file
  'These are used to find the most recent version (date is this month or last, version is the highest available, starting at 10)
  Dim iClientFileMonth As Integer
  Dim iOriginalMonth As Integer
  Dim iClientFileYear As Integer
  Dim iClientFileVersion As Integer
  Dim sClientFileVersion As String 'this is used to add the v to the client version as iClientFileVersion iterates from 10-1
  Dim objFileDialog As FileDialog 'This is for selecting a file when the user does not want the most recent
  Dim iUserInput As Integer 'Used to decide whether the most recent file will be used or not.
  Dim bFileExists As Boolean
  Dim bStayInLoop As Boolean
  Dim x As Long, z As Long
  Dim labels(1 To 1000) As String
  
  Set wbSeoReport = ThisWorkbook
  
  If ExcelInstances &gt; 1 Then 'This checks the instances of Excel, which could be an issue making formulas saving as values.
      MsgBox "There are " &amp; str(ExcelInstances) &amp; " instances of Excel open. Please close extra instances and start again."
      Exit Sub
  End If
  
  sFilePath = ActiveWorkbook.path &amp; "\"
  
  iUserInput = MsgBox("Would you like to use the most recent file?", vbYesNoCancel) 'Results returned as integers: 6 is yes, 7 is no, 2 is cancel
  
  If iUserInput = 6 Then
  'The following code seeks to identify the most recent client file based on month and file version
  'it then checks if it is open, opens it, and sets the file as wbClientReport
  
      iClientFileMonth = Month(wbSeoReport.Worksheets("Traffic Summary").range("S1").value)
      iOriginalMonth = iClientFileMonth 'This is used for only running the file finder once.
      iClientFileYear = Year(wbSeoReport.Worksheets("Traffic Summary").range("S1").value) - 2000
      sCompanyName = wbSeoReport.Worksheets("Traffic Summary").range("Z1").value
      bFileExists = False
  
       bStayInLoop = True
       'This loop is used to go through this month and the previous.
       'If it fails, you can select the report to open.
          Do While bStayInLoop = True
  
                  For iClientFileVersion = 10 To 0 Step -1
                  'This loops through file versions starting at 10.
                      If iClientFileVersion &gt; 0 Then
                          sClientFileVersion = " v" &amp; iClientFileVersion
                      Else
                          sClientFileVersion = ""
                      End If
                      sClientFileNameAndPath = sFilePath &amp; sCompanyName &amp; " - MOM -  Client Report  " &amp; iClientFileYear &amp; " - " &amp; iClientFileMonth _
                      &amp; sClientFileVersion &amp; ".xlsm"
                      sClientFileName = sCompanyName &amp; " - MOM -  Client Report  " &amp; iClientFileYear &amp; " - " &amp; iClientFileMonth _
                      &amp; sClientFileVersion &amp; ".xlsm"
                      bFileExists = IsFile(sClientFileNameAndPath)
                      If bFileExists = True Then
                          bStayInLoop = False
                          Exit For
                          'GoTo exitLoop
                      End If
                  Next iClientFileVersion
  
                  If bStayInLoop = True Then
  
                      If iOriginalMonth - 1 = 0 And iClientFileMonth - 1 = 0 Then
                          iClientFileMonth = 12
                          iClientFileYear = iClientFileYear - 1
                      ElseIf iClientFileMonth = iOriginalMonth Then
                          iClientFileMonth = iClientFileMonth - 1
                      Else
                          iUserInput = 7 'Allows user to find file.
                          bStayInLoop = False
                      End If
                  End If
  
          Loop
  
          'This sets an opened file (or opens it) to the wbClientReport
          If bFileExists = True Then
              If BookOpen(sClientFileName) = True Then
                  Set wbClientReport = Workbooks(sClientFileName)
              Else
                  Set wbClientReport = Workbooks.Open(sClientFileNameAndPath)
              End If
          End If
  End If
  If iUserInput = 7 Then
  'This allows a user to select their own file.
      Set objFileDialog = Application.FileDialog(msoFileDialogOpen)
      With objFileDialog
          .InitialFileName = sFilePath
          .AllowMultiSelect = False
          .Show
          On Error Resume Next
           sClientFileName = Dir(.SelectedItems.Item(1))
          .Execute
      End With
  'This sets an opened file (or opens it) to the wbClientReport
      Set wbClientReport = Workbooks(sClientFileName)
  ElseIf iUserInput = 6 Then
      'Do nothing. Work has already been done.
  Else
      'User pressed cancel
      GoTo EndOfCode
  End If
  
  sSeoReportName = wbSeoReport.name
  
  If ExcelInstances &gt; 1 Then 'This checks the instances of Excel, which could be an issue making formulas saving as values.
      MsgBox "There are " &amp; str(ExcelInstances) &amp; " instances of Excel open. Please close extra instances and start again."
      Exit Sub
  End If
  
  'This code calls the function that sets the named ranges equal to each other to populate
  'the data from the SEO Report to the Client Report
  
  Populate_Client_Template
  
  'This makes errors = 0 to look better for clients.
  For Each replace_page In wbClientReport.Worksheets
  
          replace_page.Cells.Replace what:="#DIV/0!", Replacement:="0", LookAt:=xlWhole, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
  Next
  EndOfCode:
  Application.ScreenUpdating = True
  End Sub
  
  Private Sub Populate_Client_Template()
  Dim replace_page As Worksheet
  Dim label As String 'Used to identify one label in a named range.
  Dim labels(1 To 1000) As String 'Used to store all of the named range labels.
  Dim x As Long, z As Long 'x keeps track of the entry number in labels. z is a loop iterator.
  Dim rngTitleRange As range
  
  x = 0
  
  labels(x + 1) = "Branded_Keywords"
  x = x + 1
  labels(x + 1) = "Conversion_Rate_by_Source"
  x = x + 1
  '...
  'This adds about 30 more names of the named ranges to the array "labels" to be iterated through in the following loop.
  
  On Error Resume Next
  
  For z = 1 To x + 1
      Fill_Client_Report (labels(z)) 'Uses function to populate Client Report with data.
      Fill_Client_Report (labels(z) &amp; "_Titles") 'Uses function to populate named ranges that have title info (ie column a in 13 month window).
  Next z
  
  End Sub
  
  Sub Fill_Client_Report(label As String)
  
  Dim wsClient_Page As Worksheet 'This is used to select the page on which a named range exists in the client report
  Dim wsSeo_Page As Worksheet 'This is used to select the page on which a named range exists in the SEO Report
  Dim rngTestRange As range 'This is used to check if a named range exists in the respective documents.
  
  
  wbSeoReport.Activate
  On Error Resume Next
  Set rngTestRange = range(label)
  If Not rngTestRange Is Nothing Then 'This checks if the named range exists in the SEO Report
      Set wsSeo_Page = range(label).parent 'This sets the page containing the named range.
      wbClientReport.Activate
      Set rngTestRange = range(label)
      If Not rngTestRange Is Nothing Then 'This checks if the named range exists in the Client Report
          Set wsClient_Page = range(label).parent 'This sets the page containing the named range.
      'The following sets the two ranges equal to populate the client report with seo report data.
          wsClient_Page.range(label).value = wsSeo_Page.range(label).value
      Else
          'This shows is the named range isn't in the client report.
          Debug.Print label
      End If
  Else
      'This shows if the named range isn't in the seo report.
      Debug.Print label
  End If
  
  End Sub
  
  Function IsFile(fName As String) As Boolean
      'Returns TRUE if the provided name points to an existing file.
      'Returns FALSE if not existing, or if it's a folder
          On Error Resume Next
          IsFile = ((GetAttr(fName) And vbDirectory) &lt;&gt; vbDirectory)
  End Function
  
  
  Function BookOpen(strBookName As String) As Boolean
      'This code checks if a workbook is open
      Dim Bk As Workbook
      On Error Resume Next
      Set Bk = Workbooks(strBookName)
      On Error GoTo 0
      If Bk Is Nothing Then
          BookOpen = False
      Else
          BookOpen = True
      End If
  End Function
  </code></pre>