You may create a report you wish to share outside of your organization on the Internet. Today, I’ll take a look at the steps to publish your report to the Internet so that the report has all of the same interactivity as the report you created with Power BI Desktop and published within your organization on the Power BI Service.

edit: This article was written on February 29, 2020. All elements contained within are subject to change with the current monthly release cycle the Power BI team is following.

edit: Make sure you only publish information to the Internet that you have cleared through proper channels within your organization.

Publish From Power BI Desktop To Power BI Service.

You cannot publish directly from Power BI Desktop to the Internet (web). You first must publish from Power BI Desktop to Power BI Service. Go back and check this post if you need a refresher how to publish from Power BI Desktop to Power BI Service.

Open The Report In Power BI Service

Log into the Power BI Service, navigate to your workspace, and open the report you wish to publish to the web. Here, I have a screen shot of the Filled Map Report I have been working with in my last few posts. This shows the population change by state from 2010 to 2019 where a darker color indicates a greater percentage change, while a lighter color indicates a smaller or even negative increase.

File Menu…Publish To Web

In Power BI Service, click on the File Menu. From the File Menu drop-down, click on Publish to web

Create Embed Code

You will receive a dialog window to create an embed code…click the button “Create embed code”

Next, you will receive a warning that you are publishing a report on the Internet that anyone can see. If you are sure you want to proceed, click on the button “Publish”

Generate HTML Code

After you click the Publish Button, you should receive the Dialog Window below indicating that everything was successful and you now have the html code you need to embed the report into a blog or website. You also have a link you can send in an email or paste into a browser to see what the report looks like

Here, I just pasted the link in a browser to see what it looks like:

The Embedded Report

Without further ado, here is the report, embedded in this blog, no more screen shots. This report is live. If I make any changes to the report in Power Bi Service, the change will also be captured here. You may use your mouse scroll wheel to zoom in/out on the map. You can hover on a state with your mouse to see the tooltip for each state. If you do not have this functionality, it is because you have enabled many security extensions on your browser that are blocking the correct functionality of the map. Try another browser such as Microsoft Edge

Admin/Tenant Settings

If you cannot publish your report to the web, it is because your admin for your organization has disabled Publish to web. Ask your admin to enable this feature.

Tidy Up

That’s it for today. Stay tuned for whatever is next….wit? rancor? rant? sarcasm? bramble?…most likely the latter

, , , , ,

Today, I’ll take a look at the process to publish a report from Power BI Desktop to Power BI Service so you can share your reports within your organization.

Power BI Desktop Version

I’m using version 2.78.5…. released ~ 2/8/2019. If you are using a different version, your results may vary.

Power BI Desktop Report To Publish

Open the report you want to publish to your workspace on the Power BI Service. Here I have a Filled Map based on population change by state from 2010 to 2019. Go back to this post if you need to review how to create the Filled Map.

Click To Publish

Click on the publish icon in the Share Group on the Home Ribbon in Power BI Desktop.

Sign Into the Power BI Service

If you are not already signed into the Power BI Service, you will be prompted to sign in. Enter your email address

and your password

Select Workspace

Once you successfully log into the Power BI Service, you will be prompted to choose a workspace to publish the report to

Replacing Dataset

If you already have a report with the same name already published to the workspace, you will receive a warning that you are about to replace the original with the new – be careful if that is not what you intend to do.

Publishing…Success

You should receive a message that the report was successfully published to the Power BI Service.

Power BI Service

Next, I’ll check that the report is indeed published to my workspace in the Power BI Service. I need to log into my Power BI account – powerbi.microsoft.com

and password:

Navigate To The Workspace

Navigate to the workspace you published your Desktop Report to. I published to “My workspace”, so I’ll select that.

After you navigate to your workspace, click on the Reports Menu at top

In the list of reports, find the report you published from Power BI Desktop

Check the report to make sure it looks just the report you created in Power BI Desktop. I tried Firefox and Chrome Browsers and the Conditional Formatting did not render correctly. Most likely because I have several extensions enabled for both of those browsers. I then tried to open the report in the Microsoft Edge Browser where the report rendered as expected with the correct conditional formatting.

Tidy Up

That’s it for today. In the next post in this series, I’ll see if I can publish to a public web page so that the report is live and interactive.

, , , , , ,

Office Scripts Preview is available for Excel Online only as of February 21, 2020 – if you have an E3 or E5 License. I’m using Office Pro Plus which is a flavor of an E3 license (not all services that come with E3 license). Office Scripts is using TypeScript which is a superset of Javascript. TypeScript was created by Microsoft in 2012.

Admin Access To Office Scripts

Your administrator can grant access to Office Scripts

Point your browser to admin.microsoft.com. At the username dialog enter the admin username (email address)

At the password prompt, enter the admin password

If the username and password are correct, you are now logged into the Admin Center

Show All

Click on the Show All Tab to reveal additional options

Settings…Settings

Click on Settings chevron to expand the menu. Then click on Settings below Settings (confusing)

Services…Office Scripts

On the Services Menu, scroll down to Office Scripts

Tick the box “Let users automate their tasks in Office on the web” and click on the button “Save changes”

Automate Tab

Go to office.com, login click on the Excel App and click on New blank workbook

Note the new tab above the Ribbon “Automate”

When you click on the Automate Tab you will see the Record Actions (Macro Recorder) and the Code Editor Icons. Here, I clicked on the Code Editor. Quite different from the VBE (Visual Basic Editor).

Microsoft Docs Reference

Good reference material for Office Scripts on Microsoft Docs to get you started.

What Do Office Scripts Mean For VBE (Visual Basic Editor) ?

My best guess is that once Office Scripts is rolled out for the desktop, the development will take place in a different IDE (Integrated Development Environment) such as Visual Studio or Visual Studio Code

What Does It Mean For Python?

Python as a scripting language for Excel (Office) is the #1 upvoted item on Excel User Voice at 6,342 votes as of February 22, 2020. As of February 22, 2020, Python is the #3 programming language in the world, JavaScript is #7 according to the Tiobe Index. Maybe Office Scripts will help to propel JavaScript? Or maybe one day we will see Office Scripts/TypeScript as a separate entry on the Tiobe Index?

Tidy Up

That’s it for today. Post inspired by Bill Jelen’s (aka Mr Excel) Podcast. Make sure you check it out.

, , , , , , ,

Well said by Jim Rohn. Today, I’ll look at at how to format a filled map (Choropleth) in Power BI.

Power BI Desktop Version

I’m using version 2.76.5…. released ~ 12/18/2019. There are no newer updates as of 2/15/2020. If you are using a different version, your results may vary.

Report Window

From the Data Window click on the Report icon on the left. This will stitch the view from the Data Window to the Report Window and expose the Visualization Pane

Create The Filled Map

Click on the Filled Map Visualization Icon in the Visualization Pane. The Filled Map Visualization will appear on the Report Canvas.

Click on the Filled Map on the Report Canvas. This will expose the Location Fields at the bottom of the Visualization Pane. Once the fields are exposed, drag the State Field from the Data Pane to Location. Lastly drag the Pcnt Change Field from the Data Pane to Tooltips in the Data Fieelds at the bottom of the Visualization Pane.

You should now have a filled map that looks something like the image below.

Conditional Formatting

Now I need to add divergent colors to the filled map so I can quickly see which states have the greatest percentage change in population from 2010 to 2019.

  1. Click on the Filled Map on the Report Canvas
  2. Click on the Format Button Below the Icons in the Visualization Pane
  3. Click on the drop-down for Data colors
  4. When you mouse over Default Color notice that a vertical elipses is visible
  5. Click on the vertical elipses
  6. Click on the fx Conditional Formatting pop-up

The Default Color – Data color dialog opens. The State is the default field for Based On Field – click on the drop-down and change to Pcnt Change.

Change the Colors for Minimum and Maximum to 2 different contrasting colors. I’ll use a very light grey and black.

Interpret the Visualization

Even at this small zoom level, it is easy to see some differences in population increases and decreases. It appears as though West Virginia, Illinois and Mississippi had negative to very low population increase. On the other hand; Nevada, Texas and Utah had large population increases.

The only one I found surprising was Utah at 16% increase from 2010 to 2019. I had heard of all of the others through social media and or news outlets over the past few years.

Divergent Color

You can add a 3rd color to the Conditional Formatting if you so choose. Tick the checkbox for Diverging and you will receive a middle value input for the middle color as well.

Custom Color – HEX Only, No RGB

I come from Excel where I am used to using RGB (Red, Green, Blue) values for creating custom colors. RGB is not an option in the Default Color Dialog – you have to use hexadecimal values.

Hexadecimal Resources

I found a good resource for working with hexadecimal colors at color-hex. Make sure you check it out.

Tidy up

That’s it for today. Grab the workbook from my OneDrive. Post inspired by this post at PowerBI tips by Mark Carlo and Seth Bauer.

, , , , , , , ,

Another classic quip from Mark Twain (Samuel Clemens). Perhaps not one of his better known, but apropos for displaying numbers.

Power BI Desktop Version

I’m using version 2.76.5…. released ~ 12/18/2019. There are no newer updates as of 2/15/2020. If you are using a different version, your results may vary.

Load Some Data

I grabbed some population data from Wikipedia to look at US Population percent change by state from 2010 to 2019. I had to clean the data a bit because it included Totals, Region Totals, and Territories – I just want the 50 US States.

I’ll see what happens if I try to change the Data type of the Pcnt Change column in the Data Model

  1. Click on the Data icon in the x pane
  2. Click on a cell in the Pcnt Change column
  3. On the Modeling Ribbon, click on the drop-down for the Data type.
  4. Change the Data Type from Text to Decimal Number

You will receive a Data type change warning. Go ahead and click on the Yes button to confirm the change

Note the difference in the appearance of the data in the Pcnt Change Calculated Column. Before the change, the data is left-aligned, after the change, the data is right-aligned.

Format as Percentage (%)

Now that I have a decimal value in the Calculated Column, I’ll change it to a percentage. On the Modeling Ribbon, in the Formatting Group

  1. Click on the % icon
  2. In the decimal places drop-down, I changed from 2 decimal places to 1 decimal place

Formatted Tooltip

Now the tooltip is nicely formatted and easy to ready without 9 decimal places.

Tidy up

That’s it for today. Grab the workbook from my OneDrive. Post inspired by this post at PowerBI tips by Mark Carlo and Seth Bauer.

, , , ,

There is an update for Power BI Desktop available this morning, Tuesday, February 18, 2020. Download is here. No official announcement on the Power BI Blog as of this morning.

, ,

In Part I of this series, I demonstrated how to use Range Objects, Arrays and VBA to Filter and Delete data from a table. Today, I’ll use Power Query to Filter data from Table A based on criteria in Table B.

Load Data

I’ll begin by loading data from an Excel Workbook to Power Query in Excel. Here is the data I created in Part I of the series where I grabbed NFL winning percentages for all 32 NFL Teams. I replicated the data 9 more times giving me a total of 320 records.

Just like in Part I, I want to filter out the 4 teams in the NFC East. So, if everything works correctly, I should have 280 records when I am done.

Load Data Into Power Query

In Excel, click on the data menu (1). In the Get & Transform Data Group A(2), click on the Get Data icon (3).

In the pop-up menu, click on From File (4). From the next pop-up menu, select your file type. I’m using an Excel Workbook as my data source, so I’ll select Excel Workbook.

From the Import Dialog Window, navigate to the file that has your data, highlight the file (6) and click on the import button (7).

In the Navigator Dialog Window, tick the box next to “Select Multiple Items” (8). Next, tick the box next to the name of each worksheet you wish to import into Power Query (9). In this instance, there are 2 worksheets and I want to import both worksheets. Lastly, click on the Transform Data Button (10).

The data is now loaded into Power Query. There are two tables showing in the queries pane. The table with all records is named “Data” (Table A) and the table with the 4 teams I wish to filter out of the data are in the table named “crit” (Table B).

Now that I have all data that I need loaded to Power Query, I want to perform any transformations on the data and/or tables that are needed before I can use Table B as a filter on Table A.

Transformations

Power Query added a few transformations when I imported the worksheets into Power Query. The other transformation I need, is to convert the criteria table (Table B) into a List.

  1. Click on the crit Table in the Queries Pane
  2. Click on the Transform menu across the top of the Power Query Window
  3. Click on the Convert To List icon in the Any Column Group

The crit table (4) has been converted to a list (5). Note the different icon in the queries panel.

Filter Manually

Let’s take a look at what happens when I deselect the 4 teams that I want to remove from the filter drop-down in the Data Table. in the Text Filters drop-down (1), I removed the tick marks for the 4 NFC East teams (2)

A new Applied Step is added to the Applied Steps Pane (3). There are now 280 records (4) which is what I was after.

All well and good, however I want to use the list as the list of items to exclude, I don’t want to manually deselect teams. It is easy enough for this example, but imagine if I have a much larger table with many more items I need to exclude. Let’s take a look at the expression created when I manually deselected the 4 teams:

= Table.SelectRows(#"Changed Type", each ([Team] <> "Dallas Cowboys" and [Team] <> "New York Giants" and [Team] <> "Philadelphia Eagles" and [Team] <> "Washington Redskins"))

The expression is telling Power Query to return all Rows except those where the team is Dallas Cowboys, New York Giants, Philadelphia Eagles, or Washington Redskins. Those are the 4 teams I have listed on my criteria list “crit” so I need to replace

"Dallas Cowboys" and [Team] <> "New York Giants" and [Team] <> "Philadelphia Eagles" and [Team] <> "Washington Redskins"

with

List Contains

Also note in the expression, that Power Query is using the Applied Step Name #”Changed Type” in the place of the Data Table, so I will want to use the same Applied Step in my revised expression:

= Table.SelectRows(#"Changed Type", each List.Contains(crit,[Team])=false)

That is much easier to read. Note that I used =false to exclude the teams from the crit list. Here is the data table with the revised expression showing the 280 records as expected.

List Changes – Data Updates

The idea is to pass a dynamic list to filter the data table so I’ll check to see what happens to the data table when the list changes. I added the Cincinnati Bengals to the data source, saved and closed the workbook.

I returned to the Power Query Editor and refreshed all to update the list and the table. I added a fifth team so the list should return the 5 teams and the record count in the data table should now be 270.

I now have a List (Table B) that is filtering a Table (Table A). The expression now refers to a a list so that the filter is dynamic – as the data source expands and/or contracts, I can refresh all and the Data table will show more or fewer records depending on the teams listed in the data source.

Power BI Desktop

The steps are the same if you are using Power BI Desktop.

Tidy Up

That’s it for today. Grab both files from my OneDrive

Stay tuned for Part 3 in the series.

, , , , , , , ,

See the announcement from Joe McDaid, Program Manager, Excel. I think a few of the limitations of VLOOKUP that Joe points out are easily overcome with well structured data and strongly typed formulas, but XLOOKUP is a great addition nonetheless.

, , , , ,

Recently, I was working with Oracle Business Intelligence Enterprise Edition (OBIEE) using the Smart View Add-In for Excel. I noticed that at times after ZoomIn to bottom level (Zoom Level =2) Child Level account from a Top Level Parent Account, I was left with a few Sub-total Accounts that I did not want in my final table. I decided that the best approach was to filter the data for the unwanted G/L’s and delete the visible rows. This is a good dynamic approach so that if the list expands or contracts in the future, the user can easily maintain the list on a worksheet that is read into the array that will be passed to the filter as the criteria.

The Data –

I grabbed some NFL team data from Wikipedia for the current 32 teams in the NFL:

And I added a second sheet to use to list the criteria I want to load into the array to be used to filter the first sheet

I listed the 4 teams from the NFC East (Least). I’ll remove these teams from the list since they were all terrible teams in 2019.

Code

The full code is at the bottom, but there are a few code blocks I want look at specifically

Load Criteria Array from Worksheet Range

The Range in this case is very straightforward. It is a list that begins in A1 and continues down column A with no blanks. Therefore, I can use the CurrentRegion property of the Range Object to load the worksheet range to the criteria array. If you have a more complex range, you will need to update this piece of code.

'Transfer criteria range to array
'Use CurrentRegion Property of Range Object
    Criteria_2D = wsCrit.Range("A1").CurrentRegion

Since I transferred a worksheet range to an array- the array now has 2 dimensions where the rows are the 1st dimension and the columns are the 2nd dimension. Let see what happens when I try to use this array as the criteria for the filter:

'Call the filter function
'Pass the array as the filter criteria
     x = GetFilterDeleteRows(ws:=wsData, _
                             FilterCriteria:=Criteria_2D, _
                             ColNumber:=ColumNumber)

There are still 31 teams in the table. I have 4 teams listed on the criteria sheet, so there should only be 28 teams showing. With the 2D array the only team deleted was the Dallas Cowboys, the first team listed on the criteria sheet. To get all of the teams on the criteria sheet, I am going to have to convert the array from 2 dimensions to 1 dimension.

Make the 1D Array the Same Size as the 2D Array

I’ll start the conversion process by making sure the 1D array is the same size as the 2D array for the rows part of the 2D array. Use the ReDim statement:

 'Now that we know the size of the 2D array
 'We can resize the 1D array
        ReDim Criteria_1D(LBound(Criteria_2D) To UBound(Criteria_2D))

Load the 2D Array Into the 1D Array

Now that the 1D array is the same size as the 2D array, I can safely load the contents of the 2D array into the 1D array:

    'Load 2D array into 1D array
        For i = LBound(Criteria_2D, 1) To UBound(Criteria_2D, 1)
            Criteria_1D(i) = Criteria_2D(i, 1)
        Next i

Filter and Delete Data Based on Criteria Array

Now that I have the criteria loaded into the 1D array, I can pass the array to my function to filter the data and delete the visible rows after the filter:

    'Call the filter function
    'Pass the array as the filter criteria
        x = GetFilterDeleteRows(ws:=wsData, _
                                FilterCriteria:=Criteria_1D, _
                                ColNumber:=ColumNumber)

Filter Data

This code chunk will filter the data based on the criteria array. I see that the team name I am filtering for is in Column A (Field Number 1) so I passed “1” to the function. There are other, better ways to find the position of the field to make the code more dynamic in case the column containing the team name were to move:

 'Filter range based on criteria array
        rng.AutoFilter _
            Field:=ColNumber, _
            Criteria1:=FilterCriteria, _
            Operator:=xlFilterValues

Delete Visible Rows Except Header Row

This bit of code will delete the visible rows based on the criteria I filtered for. Note that I use the Resize Property of the Range Object so that the header row is not deleted. Again, simply passing Field Number as “1”.

 'Filter range based on criteria array
        rng.AutoFilter _
            Field:=ColNumber, _
            Criteria1:=FilterCriteria, _
            Operator:=xlFilterValues

Now that I have the array with 1 dimension as the filter criteria, I am getting the correct expected results. The 4 teams from the NFC East have been deleted leaving 28 teams.

Multiple Instances

My data only has 1 unique instance of team. What if there is more than 1 instance that I want to delete? No problem, the filter will show all instances of the criteria and once visible, the SpecialCells Method of the Range Object with the xlCellTypeVisible enumeration will handle all instances of the given criteria.

I made 9 additional copies of of the data for a total of 10 instances of each team giving a total of 320 records in the table.

There are 10 instances of each team and I want to delete 4 teams for a total of 40 records. When all is done and said I should have 280 records remaining.

The final data after running the procedure again to filter and delete all instances of the 4 teams. 280 records remain expected – sweet succeess!

The Main Procedure

Here’s the main procedure

Option Explicit

Sub FilterOutUnwantedValues()

    'Developer      :   Winston Snyder
    'Created Date   :   January 23, 2020
    'Purpose        :   Remove rows of data the user does not want in the final dataset
    'Comments       :

    'Objects
        Dim wb As Workbook
        Dim wsData As Worksheet
        Dim wsCrit As Worksheet

    'Variables
        Dim x As Long
        Dim r As Long
        Dim i As Long
        Dim Criteria_2D() As Variant
        Dim Criteria_1D() As String
        
    'Constants
        Const ColumNumber As Long = 1

    'Initialize
        Set wb = ThisWorkbook
        Set wsData = wb.Worksheets("Data")
        Set wsCrit = wb.Worksheets("crit")

    'Transfer criteria range to array
    'Use CurrentRegion Property of Range Object
        Criteria_2D = wsCrit.Range("A1").CurrentRegion

    'Now that we know the size of the 2D array
    'We can resize the 1D array
        ReDim Criteria_1D(LBound(Criteria_2D) To UBound(Criteria_2D))

    'Load 2D array into 1D array
        For i = LBound(Criteria_2D, 1) To UBound(Criteria_2D, 1)
            Criteria_1D(i) = Criteria_2D(i, 1)
        Next i

    'Check contents of 1D Array
        For i = LBound(Criteria_1D) To UBound(Criteria_1D)
            Debug.Print i, Criteria_1D(i)
        Next i

    'Call the filter function
    'Pass the array as the filter criteria
        x = GetFilterDeleteRows(ws:=wsData, _
                                FilterCriteria:=Criteria_1D, _
                                ColNumber:=ColumNumber)

    'Tidy up
        Erase Criteria_1D
        Erase Criteria_2D
        Set wsData = Nothing
        Set wsCrit = Nothing
        Set wb = Nothing

End Sub

The Filter and Delete Function

Here is the function to filter and delete the data

Option Explicit

Public Function GetFilterDeleteRows(ws As Worksheet, _
                                    FilterCriteria As Variant, _
                                    ColNumber As Long) As Long


    'Developer      :       Winston Snyder
    'Date           :       January 23, 2020
    'Purpose        :       Filter a range based on criteria in array
    '                       Once filtered, delete all visible rows except header row
    '
    'Paramers       :       ws              :   A worksheet object
    '               :       FilterCriteria  :   A one dimensional array of values to filter the data for
    '               :       ColNumber       :   The Column Number where the filter is to be applied
    '===================================================================================================

    'Objects
        Dim rng As Range
        Dim rngDelete As Range
        
    'Variables
        Dim i As Long
        
    'Initialize
        With ws
            Set rng = .Range("A1").CurrentRegion
        End With

    'Filter range based on criteria array
        rng.AutoFilter _
            Field:=ColNumber, _
            Criteria1:=FilterCriteria, _
            Operator:=xlFilterValues

    'Create a range object of all visible rows
    'Do not include the header row
        Set rngDelete = rng.Offset(1, 0) _
                           .Resize(rng.Rows.Count - 1, rng.Columns.Count) _
                           .SpecialCells(xlCellTypeVisible) _
                           .EntireRow

    'Delete all visible rows
    'Do not include the header row
        rngDelete.Delete

    'Remove the filter
        ws.ShowAllData
        ws.AutoFilterMode = False

    'Destroy objects
        Set rng = Nothing
        Set rngDelete = Nothing

    'Return
        GetFilterDeleteRows = 0

End Function

Tidy Up

That’s it for today. You can download the workbook from my OneDrive. Stay tuned for Part II. I’ll demonstrate some other options for filtering and deleting data.

, , , , ,

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 = &amp;amp;quot;HKEY_CURRENT_-- USER\SOFTWARE\Microsoft\VBA\7.0\Common\&amp;amp;quot; ' Windows 10 Excel 2010
'RegPath = &amp;amp;quot;HKEY_CURRENT_-- USER\SOFTWARE\Microsoft\VBA\7.1\Common\&amp;amp;quot; ' Windows 10 Office365 Pro Plus
'==========================================================================================================</pre>
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(&amp;quot;WScript.Shell&amp;quot;)

' User defined variables
RegPath = &amp;quot;HKEY_CURRENT_-- USER\SOFTWARE\Microsoft\VBA\7.1\Common\&amp;quot; ' Windows 10 Office365 Pro Plus
BackupFile = &amp;quot;C:\VBA_&amp;quot; &amp;amp; Format(Now, &amp;quot;yyyymmddhhmmss&amp;quot;) &amp;amp; &amp;quot;.reg&amp;quot;

wsh.Run &amp;quot;regedit.exe /e &amp;quot; &amp;amp; Chr(34) &amp;amp; BackupFile &amp;amp; Chr(34) &amp;amp; &amp;quot; &amp;quot; &amp;amp; Chr(34) &amp;amp; RegPath &amp;amp; Chr(34), windowStyle, waitOnReturn ' Export the registry key
wsh.Run &amp;quot;Notepad.exe &amp;quot; &amp;amp; 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 = &amp;amp;quot;HKEY_CURRENT_-- USER\SOFTWARE\Microsoft\VBA\7.0\Common\&amp;amp;quot; ' Windows 10 Excel 2010
'RegPath = &amp;amp;quot;HKEY_CURRENT_-- USER\SOFTWARE\Microsoft\VBA\7.1\Common\&amp;amp;quot; ' Windows 10 Office365 Pro Plus

Dim myWS As Object
Dim RegPath As String

Set myWS = CreateObject(&amp;amp;quot;WScript.Shell&amp;amp;quot;)
RegPath = &amp;amp;quot;HKEY_CURRENT_-- USER\SOFTWARE\Microsoft\VBA\7.1\Common\&amp;amp;quot; ' Windows 10 Excel 2010

Debug.Print &amp;amp;quot;ForeG = &amp;amp;quot; &amp;amp;amp; Chr(34) &amp;amp;amp; myWS.RegRead(RegPath &amp;amp;amp; &amp;amp;quot;CodeForeColors&amp;amp;quot;) &amp;amp;amp; Chr(34)
Debug.Print &amp;amp;quot;BackG = &amp;amp;quot; &amp;amp;amp; Chr(34) &amp;amp;amp; myWS.RegRead(RegPath &amp;amp;amp; &amp;amp;quot;CodeBackColors&amp;amp;quot;) &amp;amp;amp; 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 = &amp;amp;quot;HKEY_CURRENT_-- USER\SOFTWARE\Microsoft\VBA\7.0\Common\&amp;amp;quot; ' Windows 10 Excel 2010
' : RegPath = &amp;amp;quot;HKEY_CURRENT_-- USER\SOFTWARE\Microsoft\VBA\7.1\Common\&amp;amp;quot; ' 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(&amp;quot;WScript.Shell&amp;quot;)
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 = &amp;quot;HKEY_CURRENT_-- USER\SOFTWARE\Microsoft\VBA\7.1\Common&amp;quot; ' Windows 10 Office365 Pro Plus no \ on the end

' Customise your colours here
ForeG = &amp;quot;2 4 5 0 1 15 11 10 4 8 0 0 0 0 0 0 &amp;quot;
BackG = &amp;quot;4 7 6 7 6 4 4 4 1 4 0 0 0 0 0 0 &amp;quot;

wsh.Run &amp;quot;reg add &amp;quot; &amp;amp; RegPath &amp;amp; &amp;quot; /t REG_SZ /v CodeForeColors /d &amp;quot; &amp;amp; Chr(34) &amp;amp; ForeG &amp;amp; Chr(34) &amp;amp; &amp;quot; /f&amp;quot;, windowStyle, waitOnReturn
wsh.Run &amp;quot;reg add &amp;quot; &amp;amp; RegPath &amp;amp; &amp;quot; /t REG_SZ /v CodeBackColors /d &amp;quot; &amp;amp; Chr(34) &amp;amp; BackG &amp;amp; Chr(34) &amp;amp; &amp;quot; /f&amp;quot;, 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.

, , , , ,