Excel Back In Black

No, not the classic rock album by the boys from AC/DC – Excel has a new black theme, and with a registry hack, you can give the Visual Basic Editor a black background – let’s take a look.

Office 365 Pro Plus Update

The Black Theme is only available for subscribers of Office 365. I’m using Office 365 Pro Plus and I had to jump through a few hoops to get the new black theme as well as the 6 new functions recently released for Excel:


  • Textjoin()
  • Concat()
  • Maxifs()
  • Minifs()
  • Ifs()
  • Switch()

I followed the steps listed on this site to set myself up for First Release through the Office 365 Admin Center. However, after 24 hours, I did not have the updates. I uninstalled Office 365 and reinstalled and voila! – update successful!

Office 365 Black Theme

To change the Office Theme:

ClickFileMenu

Click on the File Menu

OfficeAccount

Click on Account

OfficeTheme

Click on the Office Theme you like – I’m trying out the Black Theme. Giving a black background to the Visual Basic Editor is not as straight forward – it will require a bit of VBA with a registry hack.

VBE Black Background

The code below was posted by Belleye on reddit. You can see the original post here

Backup The Widows Registry

Below is some code that is changing Windows Registry settings. Before I start mucking around with the Registry, I’m going to create a backup in case things go awry.

Sub BackupRegistry()
'==========================================================================================================
'Author        : Belleye
'Link          : http://bit.ly/1Vkw8xg
'Modified by   : ws
'Backs up the VBA registry keys to C:\
'RegPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.0\Common\" ' Windows 10 Excel 2010
'RegPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.1\Common\" ' Windows 10 Office365 Pro Plus
'==========================================================================================================
    
Dim wsh As Object
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim RegPath As String
Dim BackupFile As String

Set wsh = VBA.CreateObject("WScript.Shell")
    
' User defined variables
    RegPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.1\Common\" ' Windows 10 Office365 Pro Plus
    BackupFile = "C:\VBA_" & Format(Now, "yyyymmddhhmmss") & ".reg"

    wsh.Run "regedit.exe /e " & Chr(34) & BackupFile & Chr(34) & " " & Chr(34) & RegPath & Chr(34), windowStyle, waitOnReturn ' Export the registry key
    wsh.Run "Notepad.exe " & BackupFile ' Open backup in Notepad to show the key has been backed up

End Sub

Display Current VBE Colors

First, let’s look at the current color setting for the VBE:

Sub DisplayVBEColors()

'Exports the VBA editors colour scheme to the Immediate Window
'RegPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.0\Common\" ' Windows 10 Excel 2010
'RegPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.1\Common\" ' Windows 10 Office365 Pro Plus

Dim myWS As Object
Dim RegPath As String

Set myWS = CreateObject("WScript.Shell")
RegPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.1\Common\" ' Windows 10 Excel 2010

Debug.Print "ForeG = " & Chr(34) & myWS.RegRead(RegPath & "CodeForeColors") & Chr(34)
Debug.Print "BackG = " & Chr(34) & myWS.RegRead(RegPath & "CodeBackColors") & Chr(34)

End Sub

Results :

ForeG = “0 0 5 0 1 6 14 0 0 0 0 0 0 0 0 0 ”
BackG = “0 0 0 7 6 0 0 0 0 0 0 0 0 0 0 0 “

I’ll see if I can find the same information by navigating through the Registry Editor:

Registry

Looks good. Those are the settings to use if I want a white background and black text in the foreground.

VBEWhite

Next, I’ll set the VBE background to black

Change the VBE Background To Black

Sub SetVBEBackgroundToBlack()
'==========================================================================================================
'Author        : Belleye
'Link          : http://bit.ly/1Vkw8xg
'Modified by   : ws
'Comments      :
'              : RegPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.0\Common\" ' Windows 10 Excel 2010
'              : RegPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.1\Common\" ' Windows 10 Office365 Pro Plus
'              : Changes the VBA colour scheme according to the variables Foreg and BackG
'              : Requires Excel to be restarted
'==========================================================================================================

    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    Dim RegPath As String
    Dim ForeG As String
    Dim BackG As String

    ' User defined variables
    RegPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\7.1\Common" ' Windows 10 Office365 Pro Plus no \ on the end

    ' Customise your colours here
    ForeG = "2 4 5 0 1 15 11 10 4 8 0 0 0 0 0 0 "
    BackG = "4 7 6 7 6 4 4 4 1 4 0 0 0 0 0 0 "

    wsh.Run "reg add " & RegPath & " /t REG_SZ /v CodeForeColors /d " & Chr(34) & ForeG & Chr(34) & " /f", windowStyle, waitOnReturn
    wsh.Run "reg add " & RegPath & " /t REG_SZ /v CodeBackColors /d " & Chr(34) & BackG & Chr(34) & " /f", windowStyle, waitOnReturn

End Sub

VBEBlack

Tidy Up

I have to admit – I’m not a fan. I switched back to the white background. But if that is your thing – go for it.

, , , , ,