CODEWITHSUNDEEP
excelexcel-2016vba
Joe Phi
Joe Phi

Reputation: 350

how to read the amount of used or free memory in Excel 2016 VBA

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?

Upvotes: 4

Views: 14342

Answers (1)

Joe Phi
Joe Phi

Reputation: 350

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!

Upvotes: 6

Related Questions