MS Office and VBA

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

Rarely I've come across the following problem in Excel VBA. 

Some source code is definitely correct, but it just won't compile. I could one time confirm different Excel versions to be the root cause of the issue. Even though I slightly adjusted the source code in the target version of Excel, the compiler still showed funny error messages.

The trick that did solve the issue in the end was to cut the whole source code, compile and save the empty source code objects (modules, classes, etc..), paste the source code into target objects again and then compile as well as save in the target version of Excel.

From my understanding the file still held bits of the older version of Excel. However, I experienced this also using the same version of Microsoft Access on the same machine. Cutting and then pasting the source code with saving in between did the trick again. 

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".