I am currently updating the FastExcel profiler to run with 64-bit Excel. This involves the rather tedious conversion of a large number of Windows API statements to use conditional compilation, VBA7 and WIN64.
Whilst doing this I discovered the VBA ENVIRON function, which gives you an easy way to get some information about the, well, environment.
For example I wanted to show the size of the XLB file, which stores toolbar customisations even in Excel 2007 and later, and the QAT file which stores QAT customisations. The reason for this is that some poor coding practices cause the size of these files to ballon and become corrupt: then Excel starts crashing but does not tell you why!
You could do this by hardcoding the paths to the files in your code, but thats a bad idea because the paths are different for different versions of Windows.
Or you could do this by using Windows API calls to find the directories, and handle the 32-bit/64-bit coding etc.
Or you can use ENVIRON, which is MUCH easier!
The path to the XLB file under Windows 7 on my system is something like:
E:\Users\your username\Appdata\Roaming\Microsoft\Excel\Excelnn.xlb Using the ENVIRON function in a Windows and Excel version-independent way it looks like this:
strGetXLBPath = Environ(“AppData”) & “\Microsoft\Excel\Excel” & CStr(CLng(Val(Application.Version))) & “.xlb”
The path to the QAT file under Windows 7 on my system is something like:
And using ENVIRON in VBA:
strGetQATPath = Environ(“LocalAppData”) & “\Microsoft\Office\Excel.qat”
My XLB file is currently about 12KB and my QAT is less than 1KB, and I reckon anything over about 30KB is asking for trouble.
If they get corrupt you can delete or rename these files and Excel will happily recreate fresh copies (But of course you lose your customisations).
The easiest way to navigate to the directories is to enter %AppData% or %LocalAppData% in the Windows search programs and files box (Windows button).
Other things I use ENVIRON for include:
- Getting the path to the Temp Files folder using ENVIRON(“TEMP”)
- Getting the number of processors using ENVIRON(“NUMBER_OF_PROCESSORS”)
- Getting the computer name using ENVIRON(“COMPUTERNAME”)
If you want to see all the environment variables active on your system (in the VBE Immediate window) you can use this code which I found on StackOverflow
Sub EnumSEVars() Dim strVar As String Dim i As Long For i = 1 To 255 strVar = Environ$(i) If LenB(strVar) = 0& Then Exit For Debug.Print strVar Next End Sub