Excel VBA and the Internet

Have you ever needed to get data over the Internet into your VBA program? * Maybe your automation process require that your VBA application update another system using Internet protocols? * Today we explore and demonstrate how to integrate Internet-based systems with your VBA program

Why Internet with Excel VBA?

I remember back in the 1990’s, the biggest question in the corporate IT scene was: Will the Internet ever be of value to businesses and penetrate the holy sanctuary of the LAN?

How things become absurd with time, just like the law stating that it’s illegal for a woman to...

Continue Reading...

Power BI or Excel VBA?

What is the purpose and difference between Power BI and Excel VBA * When to use what * Or maybe you can use both? * Here is my take on the role Power BI and Excel VBA can play in your setting.

Lucien, one of the professional Excel VBA experts following my Blog and courses approached me this week with this question.

He noted a shift from Excel to Power BI in his workplace and asked about my point of view on the two.

Lucien mentioned that he is more of an “Excel/VBA person” and that the Power BI reports seem slow and not interactive.

What a great question to call for a Blog post!

...
Continue Reading...

SQL Statements to Alter Table from Excel VBA to MySQL Database

Send any SQL statement for execution by a remote Database Server from your VBA program * DDL statements allow you to create and alter the schema from your program * Today we write a generic VBA function for adding a column to a table in the Database

This Blog post continues the current series dealing with building robust business applications with Excel VBA and MySQL Database.

If you have not read the previous posts about preparing MySQL Server and Excel for communications and connecting and sending SQL Select statements, read them first.

What are DDL and DML statements?

In the Databases...

Continue Reading...

Write Your Own User-Defined Function in Excel

Excel offers hundreds of functions out-of-the-box * SUM(), VLOOKUP(), NPV() – to name a few * What if you need a function to do something unique, not included in the book? The answer: write it yourself! Here is how.

Functions in Excel

One of the first things you learned in Excel was calling built-in functions.

The basic concept of a function can be summarized in three points:

  • It is called and optionally provided with arguments by the caller.
  • It implements some logic – its purpose.
  • It returns a single data object as a result.

Lets take the LEFT() function to better understand...

Continue Reading...

Constructing a Dynamic File Name with Excel VBA

Are you automating annual Workbooks, monthly PDF reports, or producing any other dynamic files with Excel? Your program needs to automatically save each file in a folder. Here is how you can construct the full path and filename with a couple of VBA functions

What is a dynamically constructed file name?

When you save a file, you need to point to a particular folder and give it a name.

Your 2022 P&L Workbook may have the following path and file name: \\Shared-Server\Financials\P&L\2022.xlsm

Instead of you manually browsing to the destination folder and typing a filename when you...

Continue Reading...

Staging Data in a Dynamic Report with Excel VBA

When preparing a dynamic report in Excel, you need to arrange the data in the report structure. In this Blog post I demonstrate how to clear the report range and re-populate it with data.

Types of reports

Reports can take many forms, as far as presenting data is concerned.

A report is any artifact created from data for end use, or as required by another system as input.

A report can be a single page summarizing information about something (see the certificate example of last week’s Blog post), it can be an order for an employee to do something (such as in a production order) and it...

Continue Reading...

Creating a PDF report from Excel Worksheet with VBA

Take your Excel model one step further beyond calculations. Prepare and publish the final report as a slick PDF file with a click of a button. Here is how…

Why PDF

PDF has become to be the standard file format for immutable documents.

Immutable, for those not computer programmers yet, means it cannot be changed once it is created.

PDF is highly popular for its quality, compactness, and the ability to sign electronically to render a formal document accepted by private and public institutions.

Preparing the report

What I always do, is dedicate a Worksheet for staging the report. This...

Continue Reading...

Automation of Excel Templates with Multiple Workbooks and Worksheets with VBA

Do you need to generate Excel reports based on a template on a regular basis? Do you need to copy the template Worksheet to another Workbook, or from another Workbook? Here are some approaches with VBA code

Possible use cases for Excel templates

  • Your monthly report is based on an Excel Worksheet you prepared, and each month needs to be stored as a separate Workbook with its specific calculations for the month.
  • You are preparing and sending your customers a standard letter with calculations / charts that are changing every time. Maybe a quote, or a model calculation. You want to send them...
Continue Reading...

If You Love Excel – Here is Why You Need to Learn VBA

You are good with Excel. You love it. It gets the job done. You even heard about Macros, or VBA, but pushed it aside. It is not for you, you said to yourself. It is too complicated, you thought. I am here to tell you: it is exactly what you need to do next. Here is why.

The plain motivation at sight

Would you skip learning how to drive a car had I told you it can get you to your destination hundreds and thousand times faster than your bicycle?! I don’t think so.

Operating a vehicle may sound difficult, if not frightening, if all you’re used to is riding your bike. Still –...

Continue Reading...

Recursive Functions with Excel VBA

Sometimes writing a short, simple function, can hide in-depth processing that resolves quickly – by calling… itself * What is a recursive function, how does it work, what to be aware of and how it is implemented in Excel VBA – read on…

What is a Recursive Function?

Technically speaking, a recursive function calls itself as part of its program flow.

Yes, you read it correct. This is the general idea:

Function CalcSomething(…) as Long

     …

     X = CalcSomething(…)

     …

End...

Continue Reading...
1 2 3 4 5
Close

50% Complete

Two Step

Once you submit your details, you'll receive an email with a confirmation link. That's it! you're subscribed!