4

I can't seem to find a VBA command that returns the memory in use or the memory available. In Excel 2013 there was Application.MemoryUsed but when I try that in Excel 2016 I get "Type mismatch", regardless if I use

    dim myVar      as variant
      myvar = Application.MemoryUsed

or

    MsgBox CStr(Application.MemoryUsed)

It's probably a simple thing. Or?

Community
  • 1
  • 1
Joe Phi
  • 340
  • 1
  • 4
  • 14

1 Answers1

6

I found the answer shortly after I put the question.

found here: https://social.msdn.microsoft.com/Forums/office/en-US/e3aefd82-ec6a-49c7-9fbf-5d57d8ef65ca/check-size-of-excelexe-in-memory?forum=exceldev

   Declare Function GetCurrentProcessId Lib "kernel32" () As Long

   Function GetMemUsage()

     ' Returns the current Excel.Application
     ' memory usage in MB

     Set objSWbemServices = GetObject("winmgmts:")
     GetMemUsage = objSWbemServices.Get( _
       "Win32_Process.Handle='" & _
       GetCurrentProcessId & "'").WorkingSetSize / 1024

     Set objSWbemServices = Nothing

   End Function

Thanks to Anonimista!

Joe Phi
  • 340
  • 1
  • 4
  • 14
  • 1
    I could be wrong, but I think this returns working set size in KB. The Windows API `GetProcessWorkingSetSize()` function [documentation](https://msdn.microsoft.com/en-us/library/windows/desktop/ms683226(v=vs.85).aspx) indicates that it returns memory locations in bytes. – SmrtGrunt Mar 06 '18 at 17:53
  • @PaulDH - the `GetProcessWorkingSetSize()` function return a value in ***bytes***. **The `/ 1024` converts it to *kilobytes***. Thus, the function above returns **Excel's `Working Set (Memory)` in `KB`**, which can be demonstrated by opening the `Processes` tab in *Task Manager*, hitting ALT+VS and selecting the checkboxes to view all 7 of the `Memory` columns. – ashleedawg Mar 11 '18 at 12:38
  • @ashleedawg - So we're in agreement then. I added my comment because the comments in the original code snippet indicated a return in MB. Thanks for adding the extra explanation to my comment. – SmrtGrunt Mar 12 '18 at 14:42
  • I actually originally commented in *disagreement*, then deleted it a moment later when I noticed the `/1024`. :) – ashleedawg Mar 12 '18 at 14:53
  • I tried the function in the answer above, running in VBA for Outlook 365. I get a compile error variable not defined on `objSWbemServices` Is this function specific to VBA in Excel, or has VBA changed in the past few years? – tim11g Dec 23 '21 at 20:58
  • @tim11g - you might want to add `Dim objSWbemServices As Object` to the function – Joe Phi Jan 04 '22 at 09:58