Get Computer Name and other useful VBA functions

I have a number of PeopleSoft nVision reports that have macros attached to them.  PS nVision on the client can be thought of as a wrapper around Microsoft Excel – so from this point on we’ll treat any nVision layout as an Excel workbook.

These workbooks are on different servers or can be run on a clients PC.  A lot of them use templates to create a final report.  The macros need the path to the templates – and while the directory structure in our development to QA to production environments all stay the same – it’s the computer name that obviously changes.

I have some helper functions that I use to help get information used by my various macros.

To get the computer name – in the General Declarations of the code module add:

Private Declare Function GetComputerName Lib “kernel32” Alias “GetComputerNameA” _
(ByVal lpBuffer As String, nSize As Long) As Long

I also declare some other variables:

Private Const MAX_COMPUTERNAME_LENGTH = 31
Private Const sNameUnknown = “NOT_FOUND”
Private mvalComputerName As String
Private mvalHaveValidName As Boolean

Then it’s all put together in this function:

Private Function ComputerNameIs() As String
    Dim lngLength As Long
    Dim lngResult As Long
    Dim strNameBuffer As String
    ‘Maximum Computer Name + Terminating Null Char
    lngLength = MAX_COMPUTERNAME_LENGTH + 1
    ‘Create Buffer
    strNameBuffer = String(lngLength, “X”)
    ‘Get the computer Name
    lngResult = GetComputerName(strNameBuffer, lngLength)
    If lngResult <> 0 Then
        ComputerNameIs = Mid(strNameBuffer, 1, lngLength)
        mvalHaveValidName = True
    Else
        ComputerNameIs = sNameUnknown
        mvalHaveValidName = False
    End If
   
End Function

I’ll be adding more in future posts.