Have you ever wondered how health data from devices like Smartwatch, Smart Band, etc., can be displayed in visually appealing charts such as bar charts, line charts, etc., creating an aesthetically pleasing and user-friendly experience?

We all know that there are various methods to create them. Today, I will share a simple way for everyone to generate reports containing colorful charts and data information: VBA (Visual Basic for Applications) and its applications in daily life!

Visual Basic for Applications (VBA)

Firstly, let's briefly revisit the concept of what is VBA? Visual Basic for Applications (VBA) is a macro programming language integrated into most applications within the Microsoft Office, such as Excel, Word, PowerPoint, and more. VBA is an extension of Microsoft Visual Basic, enabling users to automate tasks, create programs, interact with data, and perform functions within Microsoft Office automatically, but it can only run within applications (such as PowerPoint, Word, Excel, etc.) and cannot run as a standalone program.

https://www.freepik.com/free-vector/spreadsheet-laptop-desktop-icons_24800197.htm#query=spreadsheet&position=0&from_view=search&track=sph
Source image: www.freepik.com

How can we create those colorful chart reports?

So, how can we create those colorful chart reports? From what I've learned, when devices like Smartwatch, Smart Band, or even medical devices like ultrasound machines, heart rate monitors, SPO2 monitors, etc., measure data, they are designed to export the collected data to files such as CSV, Excel, etc., and VBA can handle the rest.

We just need to implement code snippets with the properties of the VBA language (VBA syntax is quite similar to VB), and we can display them in charts to monitor sleep on a weekly, monthly, yearly basis, and more.

Now, if you need to read data from PDF files, how do you go about it? The common and simplest method is to use the pdftotext tool provided for free by XpdfReader. This tool converts text in a PDF file to a .txt file, and we can use VBA to transfer the data to Excel, PowerPoint, Word, etc. However, this tool is not efficient with PDF files that are scanned from images. To handle scanned image-based PDF files, I will introduce in a future blog post!

Copying Excel sheets, copying slides from one PowerPoint file to another embedded with code without needing to open them is also achievable with VBA. Practically, most functions in our Office tools can be executed using VBA.

Limitation

However, VBA also has some weaknesses, such as:

  • Limitations in handling large datasets, which can result in decreased performance.
  • Lack of flexibility in memory management, partly because it is a macro runner within Microsoft Office, which can pose challenges for resource-intensive applications.
  • Limitations in handling graphics and UI.
  • Limited scalability, as VBA is primarily used within Microsoft Office applications and is not an independent programming language.
  • Lack of modern features and standardization compared to some modern programming languages.

Despite these limitations, VBA remains a powerful, flexible, and comprehensive tool for users in the Office environment, optimizing and automating daily tasks efficiently.

 

  • Source image: https://www.freepik.com/free-vector/spreadsheet-laptop-desktop-icons_24800197.htm#query=spreadsheet&position=0&from_view=search&track=sph
  • Featured image: https://www.freepik.com/free-vector/web-application-optimization-programming-analytics-vector-illustration_10603780.htm#query=chart%20microsoft%20office&position=22&from_view=search&track=ais&uuid=3d3a6fb3-97a1-4226-b72e-17f165bd573d

Leave a comment

*