Newton Excel Bach, not (just) an Excel Blog

There was a question at Eng-Tips about how to get the physical serial number of a hard disk (rather than the soft number, that is changed by a re-format): http://eng-tips.com/viewthread.cfm?qid=285948&page=1

A search found this discussion with the answer: http://thedailyreviewer.com/office/view/programmatically-obtain-the-hard-disks-serial-number-from-vba-101853101
(see answer number 8 )

from which I wrote the following code for an Excel User Defined Function (UDF) that will return the physical serial number of all the hard disks connected to the computer it is running on (enter as an array function to display data for more than one disk):

Function GetPhysicalSerial() As Variant

Dim obj As Object
Dim WMI As Object
Dim SNList() As String, i As Long, Count As Long

Set WMI = GetObject("WinMgmts:")

For Each obj In WMI.InstancesOf("Win32_PhysicalMedia")
If obj.SerialNumber <> "" Then
Count = Count + 1
Next

ReDim SNList(1 To Count, 1 To 1)

i = 1
For Each obj In WMI.InstancesOf("Win32_PhysicalMedia")
SNList(i, 1) = obj.SerialNumber
i = i + 1
If i > Count Then
 Exit For
Next

GetPhysicalSerial = SNList
End Function

References required for the function to work are shown in the screen shot below:

References for WinMgMts Object

I haven’t used the WinMgmts object before, but it seems that it will return a wide variety of other information about your hardware, although the usage is not particularly user-friendly.  The best source of information I could find was:

http://msdn.microsoft.com/en-us/library/aa394585