Getting the subfolder name is not a problem. Getting the subfolder size can cause the permissions error. FSO methods can make trapping those errors a bit tricky sometimes.

If you don't mind flashing of the black screen while the shell is working, a shell method such as snb's example can work well.

Of course we can poke all of the subfolder names into an array using the fso method and write it to a range in one line of code as snb did.

I used snb's preferred method in this example. I used the shell's DIR command line switch of /ad to just list the subfolder names in the parent folder. Use /s if you want to find all subfolders.

My last c:\Windows subfolder has no name but has a size. I am not sure how that happens.

As before, set the reference to the Microsoft Scripting Runtime object. Change the value of sPath to suit.

Option Explicit

Sub snb_ken()
  Dim fPath As String
  Dim sn As Variant, i As Long
  Dim fso As FileSystemObject
  
  fPath = "c:\Windows"
  Set fso = New FileSystemObject
  
  sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir " & """" & _
    fPath & """" & " /ad/b").StdOut.ReadAll, vbCrLf)
  Range("A1").Resize(UBound(sn) + 1) = Application.Transpose(sn)
  'Debug.Print sn(UBound(sn))
  
  On Error Resume Next
  For i = LBound(sn) To UBound(sn)
    sn(i) = Round(fso.GetFolder(fPath & "\" & sn(i)).Size / 1048576, 0)
  Next i
  Range("B1").Resize(UBound(sn) + 1) = Application.Transpose(sn)
  
  'Debug.Print sn(UBound(sn))
  Set fso = Nothing
End Sub