Through programming, Excel can process batch tasks and workflow tasks faster while extending its existing functions. Currently, there are two ways to implement Excel scripting: Excel VBA and Python. This section will give suggestions on which to choose.
Why Perform Excel Scripting?
Excel has an aesthetically pleasing and user-friendly graphical user interface (GUI). The GUI encapsulates most of Excel’s functions, allowing users to issue commands via buttons, menu items, and shortcuts. With the GUI, users can complete many office tasks using a mouse and keyboard.
Since many office tasks can be easily completed with the GUI, why program? This is mainly because programming can better and faster complete tasks that are difficult or impossible to do manually. Specifically, it includes the following aspects:
Batch completion of repetitive office tasks (e.g., creating 100 worksheets, generating payslips for 100 colleagues).
More efficient completion of workflow-based office tasks (automating the entire process of "what to do first, what next").
Turning frequently used but Excel-default-unavailable functions into programs for on-demand use, thus extending Excel’s functionality.
The GUI only encapsulates most common functions; programming allows access to functions not encapsulated by Excel.
Choose VBA or Python?
VBA is a subset of VB, featuring simplicity and powerful functionality. Since the late 1990s, VBA has been used as a scripting language in most mainstream industry software, including office software (e.g., Excel, Word, PowerPoint), GIS software (e.g., ArcGIS, MapInfo, GeoMedia), CAD software (e.g., AutoCAD, SolidWorks), statistical software (e.g., SPSS), and graphic software (e.g., Photoshop, CorelDRAW).
Among these, ArcGIS and SPSS officially use Python as their built-in scripting language alongside VBA. For other software like Excel, Word, PowerPoint, and AutoCAD, various third-party Python packages can partially or fully replace VBA for scripting.
For Excel, there are currently two choices for scripting: VBA and Python. Which is better? It depends on the user. If you have a good foundation in VBA, use VBA; if you are proficient in Python, use Python. In terms of functionality, there is no significant difference between VBA and Python. With the xlwings package, most tasks achievable in VBA can also be done in Python, and Python has obvious advantages in data analysis and data visualization. Therefore, if you know neither language, it is recommended to learn Python; if you are familiar with VBA and pursue higher goals in data analysis, it is also recommended to learn Python.
If you already master one language, comparative learning is an effective way to quickly grasp the other. This book breaks down the grammar of VBA and Python, enabling "point-to-point" comparative learning of knowledge points to help readers quickly master the languages and Excel scripting.