MS Office and VBA

This category holds articles regarding general things in MS Office VBA independent from the MS Office application.  

The below method will replace all special or escape characters in a provided String.

This is of use, if you have to use before unknown Strings from any source for example in a file name.

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
' @Author - Alexander Bolte
' @Description - replaces all characters, which match the pattern "[^A-Za-z0-9-_ ]" in a given String.
' @Param aText - a String in which invalid characters should be replaced.
' Returns the handed String from aText excluding all replaced characters.
Public Function replaceSpecialCharacters(ByVal aText As String) As String
    Dim regEx As New RegExp
    Const cReplEscapeChar As String = "[^A-Za-z0-9-_ ]"
    
    regEx.pattern = cReplEscapeChar
    regEx.Global = True
    regEx.IgnoreCase = True
    aText = regEx.Replace(aText, "")
    
    replaceSpecialCharacters = aText
End Function

Enjoy. 

The following looked simple enough for me.

Source Code

Here a solution, which works for me however you have to be careful since it relies on a certain environment variable to be present. If this environment variable is not available the method automatically assumes the os to operate in 32 bit.

1
2
3
4
5
6
7
8
' @Author - Alexander Bolte
' @ChangeDate - 2016-03-27
' @Description - Determines if a users operating system is of 64 bit or 32 bit.
' In a 32 bit windows operating system the environment variable "ProgramW6432" simply does not exist.
' @Returns true, if the default directory for 32 bit applications is C:\Program Files (x86), else false.
Function Is64bit() As Boolean
    Is64bit = Len(Environ("ProgramW6432")) > 0
End Function

Unfortunately thie below did not work for me as it returned "x86" no matter if I started it on a 64 bit or 32 bit os.

1
2
3
Sub testArchitecture()
    MsgBox Environ("PROCESSOR_ARCHITECTURE")
End Sub

 

The below snippet will provide you with a systems full user name.

Source Code

' @Author - Alexander Bolte
' @ChangeDate - 2014-10-13
' @Description - Returning the system user name.
Public Function getFullUserName() As String
    Dim WSHnet  As Object
    Dim UserFullName As String
    Dim userName As String
    Dim UserDomain As String
    Dim objUser As Object
   
    Set WSHnet = CreateObject("WScript.Network")
    userName = WSHnet.userName
    UserDomain = WSHnet.UserDomain
    Set objUser = GetObject("WinNT://" & UserDomain & "/" & userName & ",user")
    UserFullName = objUser.FullName
   
    Set WSHnet = Nothing
    Set objUser = Nothing
   
    getFullUserName = UserFullName
End Function

Referenced APIs

Since the source is using late binding it is not necessary to set a reference in the VBA Editor for a specific version of the referenced API named "WScript".