Excel VBA Course (5): Excel File Extension, VBE, Font Settings

This article examines the differences in Excel file extensions, VBE (Visual Basic Editor), and recommended font settings.

This is a continuation of the previous article.

Excel VBA Course (4): Working with Excel Object Model

2.6. Excel File Extension

Versions prior to Excel 2007 had only one extension, '.xls'. Since Excel 2007, it has been subdivided into '.xlsx', 'xlsm', and 'xlsb', and the data types that can be saved for each extension are different.

• Excel 2007 and below (XP, 2000, 2003)
  • .xls: data + macro storage available, Binary file format
• Excel 2007 and later (2007, 2010, 2013, 2016, 2019, MS-365)
  • .xlsx
    • Only data can be saved (macro cannot be saved)
    • XML file format
  • .xlsm
    • Data + macro storage available
    • XML file format
  • .xlsb
    • Data + macro storage available
    • Binary file format
    • Saving as .xlsb can reduce file size if you have a lot of data

We recommend saving files with macros as .xlsm and large files as .xlsb. Depending on the case, saving a 10MB .xlsx file as .xlsb reduces its size to around 2MB.

2.7. Visual Basic for Application Editor (VBE)

It is an editor for writing VBA code. Let's take a look at how to switch from Excel sheet editing state to VBE, settings, and screen composition.

2.7.1. Toggle VBE: Execute with Shortcut

hotkey Alt + F11 switch to It is a toggle key that, when pressed once more, switches from VBE to the Excel sheet editing state.

2.7.2. Toggle VBE: Run with Ribbon Bar Button

Click the Visual Basic button on the development tool ribbon bar to switch.

2.7.3. VBE settings

It is recommended to change the 'Auto Syntax Check' and 'Require Variable Declaration' settings in VBE > Tools > Options > Editor tab.

• Automatic Syntax Check: Unchecked (Initial Value: Checked)
  • Hide unnecessary syntax error message boxes
• Require variable declaration: checked (initial value: unchecked)
  • Only declared variables can be used (Automatically add Option Explicit at the top of each module)

2.7.4. VBE Screen Composition

The VBE screen consists of 5 areas as shown in the following figure.

1. project explorer
   • Check, add, and manage the list of files, sheets, forms, modules, and classes
2. properties window
   • Check and set the properties of the selected object
3. Object & Event
   • A list of objects and a list of events, procedures, and functions for each object
4. Code Editor
   • Writing event handlers, procedures, functions, etc.
5. debugging tool
   • Watch expression, local variable, direct execution, etc.

2.7.5. Recommended monospaced fonts that are easy to read

The default font for VBE is caliber. While looking for a better font than Dotum font, I found out about 'Nanum Gothic Coding' published by Naver and used it. Since then, I have been using 'D2Coding' additionally published by Naver.

The two fonts are similar, but there are differences.

▲ Nanum Gothic Coding https://github.com/naver/nanumfont v2.5 (October 24, 2016) has been released.

▲ D2Coding https://github.com/naver/d2codingfont v1.3.2 (June 1, 2018) has been released.

D2Coding is recommended over Nanum Gothic Coding. D2Coding is good in terms of readability, such as relatively wide line spacing and support for Ligature.

I recommend the following article for light reading related to fonts.

Font story: Serif (serif) / Sans-Serif (sans serif) / variable width / fixed width (for coding)

To change the font of the VBE code editor, you can change the font in “Tools > Options > Editor Format”. The following figure is an example of setting with D2Coding.

---

Above, we looked at the contents of Excel file extension and VBE. Next, I will explain the basic syntax of the VBA language.

---

<< Related article list >>

• Start the Excel VBA course. (Lecture notice, feat. Why we recommend Excel VBA)
• Excel VBA Course(1): Overview of Excel VBA
• Excel VBA Course (2): Excel VBA Basics
• Excel VBA Course (3): Excel Object Model
• Excel VBA Course (4): Working with Excel Object Model
• Excel VBA Course (5): Excel File Extension, VBE, Font Settings
• Excel VBA Course (6): Excel VBA Language Basics - Variables
• Excel VBA Course (7): Excel VBA Language Basics - Syntax
• Excel VBA Course (8): Excel VBA Language Basics - Data Types, Data Structures
• Excel VBA Courses (9): Excel VBA How-To
• Excel VBA Course (10): Tools developed and used with Excel VBA
• Full Table of Contents for Excel VBA Courses