Vba Get Computer Name using environment variable

Find Computer Name in Excel VBA

In this page, get three different vba code to get Computer name.

  1. Environment variable
  2. Windows Api
  3. wScript.Network

To get the computer name or the host name there can be more multiple ways. But, one of these could serve the purpose.

1. Get Computer name in VBA using Eniron

In here we are going to use the environment variable.

Here is the code to get hostname or computername.

Function GetComputerName() As String
    Dim sHostComputer As String
    'Get Host Name or PC name from Environment variable
    sHostComputer = VBA.Environ$("computername")
    'Return Computer Name
    GetComputerName = sHostComputer
End Function

This can also be verified from the command prompt itself.

Dos Cmd Environment Variable for Computer Name

To display the computer name from dos command prompt SET command can be used. This value is stored in the environment variable name COMPUTERNAME

Type command: >SET Computername

This will display the computer name as how it is displayed in the above Vba code.

2. Find Computer Name using Windows API

This simple code uses a Windows Api present in kernel 32 dll. This also returns the same value as previous one.

Just that, this can be used in case the above fails.

Private Declare Function apiGetComputerName Lib "kernel32" Alias _
    "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Sub getComputername()
    'Create String Variable to get computer/host/machine name
    Dim sComputerName As String
    sComputerName = VBA.String$(50, 0)
    'Call Api to get the Computer name
    Call apiGetComputerName(sComputerName, 50)
    MsgBox sComputerName
End Sub

The above function will not work, if You did not allocate or initialize the string variable.

Before calling the function the string is supposed to have a variable with space to hold the return value.

I tried it once without allocating, the Excel application stopped to respond and restarted. So, it is better to save the work before trying the above code.

3. VBA code – Get Computer name using wScript

This wScript is a method that we can use to run vbscript or javascript code.

We use this module in here to get the Network host or computername.

Sub wScript_Getcomputername()
    'Define Variable/Objects to get Computer name
    Dim wScriptObj As Object
    Dim sComputerName As String
    'Create wScript object
    Set wScriptObj = CreateObject("WScript.Network")
    'Display Computername
    MsgBox wScriptObj.ComputerName
    MsgBox wScriptObj.UserDomain
End Sub

This method can also be used in a vbscript code.

You can use any one of these methods to get the computer name, domain name, user name etc.,