How to get name of the computer in VBA?

Dim sHostName As String

' Get Host Name / Get Computer Name

sHostName = Environ$("computername")

You can do like this:

Sub Get_Environmental_Variable()

Dim sHostName As String
Dim sUserName As String

' Get Host Name / Get Computer Name    
sHostName = Environ$("computername")

' Get Current User Name    
sUserName = Environ$("username")

End Sub

A shell method to read the environmental variable for this courtesy of devhut

Debug.Print CreateObject("WScript.Shell").ExpandEnvironmentStrings("%COMPUTERNAME%")

---

Same source gives an API method:

Option Explicit

#If VBA7 And Win64 Then
    'x64 Declarations
    Declare PtrSafe Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
#Else
    'x32 Declaration
    Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
#End If

Public Sub test()

    Debug.Print ComputerName
    
End Sub

Public Function ComputerName() As String
    Dim sBuff                 As String * 255
    Dim lBuffLen              As Long
    Dim lResult               As Long
 
    lBuffLen = 255
    lResult = GetComputerName(sBuff, lBuffLen)
    If lBuffLen > 0 Then
        ComputerName = Left(sBuff, lBuffLen)
    End If
End Function

Looks like I'm late to the game, but this is a common question...

This is probably the code you want.

Please note that this code is in the public domain, from Usenet, MSDN, and the Excellerando blog.

Public Function ComputerName() As String
'' Returns the host name

'' Uses late-binding: bad for performance and stability, useful for 
'' code portability. The correct declaration is:

'   Dim objNetwork  As IWshRuntimeLibrary.WshNetwork
'   Set objNetwork = New IWshRuntimeLibrary.WshNetwork

    Dim objNetwork As Object
    Set objNetwork = CreateObject("WScript.Network")

    ComputerName = objNetwork.ComputerName
    
    Set objNetwork = Nothing

End Function

You'll probably need this, too:

Public Function UserName(Optional WithDomain As Boolean = False) As String
'' Returns the user's network name

'' Uses late-binding: bad for performance and stability, useful for
'' code portability. The correct declaration is:

'   Dim objNetwork  As IWshRuntimeLibrary.WshNetwork
'   Set objNetwork = New IWshRuntimeLibrary.WshNetwork


    Dim objNetwork As Object
    Set objNetwork = CreateObject("WScript.Network")

    If WithDomain Then
        UserName = objNetwork.UserDomain & "\" & objNetwork.UserName
    Else
        UserName = objNetwork.UserName
    End If
    
    Set objNetwork = Nothing

End Function