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 File Extension
  • 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.

VBE 전환하기: 리본바 버튼으로 실행
Toggle VBE: Run with Ribbon Bar Button

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.

VBE 설정
VBE settings
  • 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.

VBE 화면 구성
VBE screen configuration
  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 font example

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

D2Coding 글꼴 예시
D2Coding font examples

▲ 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.

VBE 글꼴 설정
VBE font settings

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 >>

Leave a Reply

Your email address will not be published. Required fields are marked *