title: VBA Automation/Monitoring/Development
body: <p>I am working as coordinator of EUCs at my department (more than 50 people and more than 150 MS Excel macros for controls and reporting). In order to keep good track of Macros usage and measuring tool performance, I have added a simple code to each workbook that collects usage data (who and when had opened/closed workbook, how much it takes for a macro to perform certain tasks, etc...). The code is pretty simple, it collects all data in a public variable and before the close of the workbook, it saves the content of a variable to a .txt file in a folder.</p>
  
  <pre><code>Option Explicit
  
  Dim info_data as String
  
  Sub check_point(x as integer)
  Select x
   Case 0
   Call WBK_close
   Case 1
   Call WBK_open
   Case 2
   Call WBK_main_start
   Case 3
   Call WBK_main_end
  End Select
  End Sub
  
  Private Sub WBK_close()
   info_data = info_data &amp; "WBK Close;" &amp; Now()
   Open myFile For Output As #1
   Print #1, info_data
   Close #1
  End Sub
  
  Private Sub WBK_open()
   info_data = "WBK Open;" &amp; Now() &amp; vbNewLine
  End Sub
  
  Private Sub WBK_main_start()
   info_data = "WBK Main Start;" &amp; Now() &amp; vbNewLine
  End Sub
  
  Private Sub WBK_main_end()
   info_data = "WBK Main End;" &amp; Now() &amp; vbNewLine
  End Sub
  </code></pre>
  
  <p>I am using a check_point sub at a certain point in the code to make a record.</p>
  
  <p>Sometimes there happen some errors, especially when other macros also writing something to text files. Or when purely written code exit <code>Sub</code> with use of <code>End</code> statement, erasing all data saved to <code>info_data</code>.
  Therefore, I am wondering if there is a smarter way to collect MS Excel usage data (other codes, add-in, use of another programming language, alternative ways of data storing (not txt)).</p>