VBA – Easy Way to Handle TRUE/FALSE Tests

Using a boolean variable

Let’s say you have VBA code that handles a budget and a forecast. There is a cell B2 on the Input sheet that contains the word Budget or Forecast. Based on that cell the macro with do different things. You may need to test for Budget/Forecast a few times within the code. There is an easy and flexible way to handle this.

Japanese Yen Format in Excel

Applying it quickly

To apply the Japanese Yen format can take quite a few mouse clicks.

The macro that does it, on the other hand, is quite simple. Select the range, then run the macro.

Sub JapaneseYen()

Selection.NumberFormat = "[$¥-411]#,##0.00"

End Sub

If you are unsure how to use macros, see the link below.

How to use macros


VBA AutoComplete

When you are typing code in the VBA code window you can press

Ctrl + Space Bar

to have Excel finish the word for you. Eg type


And then press Ctrl + Space Bar to have Excel finish the word Application.


Activating a sheet in VBA

Here’s the problem. You have a number of sheets that are named after Department codes. Those sheets contain the details for each department. You have reports throughout the model that refer to these department codes. You want to be able to select a cell that contains a department code and click a button that will take you to that department’s sheet.