VBA - Back End Applications

When developing VBA applications for a considerable high amount of users, you should be able to control every user code from a centralized module. Think about a company with 500 users using an Excel application you built. The customers use Excel as a database to store monthly data. Every day they run an application that organizes new fields to the respective sheet. One day, you notice a bug that affects every user. Grabbing a fresh copy is not an option, every user will lose months of work. Your only option is to go computer by computer doing the manual update in their specific workbook or, creating an application that goes to everyone's computer, finds the workbook, open it, and modify the code. This last option is very, very risky and I would not recommend it at all. You could encounter a lot of problems while doing it. On the contrary, if everyone's Excel application could point to a centralized workbook where the code to run the procedures and functions is located, you will only need to modify that centralized file and everyone's bug would be fixed.

Think of a centralized workbook as a Back End server for your Front End Excel workbooks. Imagine having a button in the main Workbook, and the only code in that workbook to be the code to open the centralized file with the rest of the code that will perform the actual operation. Something like this:

Even though the code looks kind of long, if you read it, you will see that it's pretty straightforward. I added some comments to the code for you to understand what we are trying to achieve. 

The rest is pretty simple. Just need to have a workbook with all the information (as specified in the front end workbook code it would be for the back end) and attached all the procedures needed to transform the front end.

 Something like:

 Having centralized code is a very handy and easy way to control from a single point what the rest of the users are going to be interacting with. By operating your applications from a central source of code, you ensure that every user receives the same outcome without having to update their respective code individually. This is especially useful for developers designing applications that are going to be used for a vast amount of users.

 TIp: Always remember to have you centralized code protected so no one but you, the developer, can modify the code.

 That's all folks... Cheers,

Comments

Post a Comment

Popular posts from this blog

VBA - Using JSON in VBA

Image
A very important skill you will need to know in VBA if you want to write professional applications is to handle JSON text and convert it into VBA objects. JSON is a very popular text-based file format used to transmit data objects. It's very common right now for APIs to return requests using JSON text. If you learn how to handle it, you can make some good use out of it. To put in an example (very trending right now by the way), imagine you have a Cryptocurrency portfolio you built in Excel where you store all your transactions and the number of Bitcoins you have. To know how much your Bitcoins are worth, you need to go and check the web for the current Bitcoin price. Or, instead, you can have Excel to automatically call an API to request this information. Parse out into an Object in VBA and display the price in the Worksheet to compare how much you Bitcoins are worth right now. Another example: let's say you want to know the list of the Best Sellers according to...
Read more

VBA - Copy Sheet from One Workbook to Another

Sometimes we need to insert a Sheet from one Workbook into another one. The solution is really easy. Follow bellow code: That was really simple. Just a few comments: Always make sure to define the workbooks that are going to be used (Source & Target). This will make the code more legible and it will make things easy to understand in the future. You should apply this rule everywhere in your coding experience. Variables are not only valuable when you have no other choice, they can be used to make your code clearer. That being said, note in the code how we are inserting the copied Sheet after the last Sheet in the Workbook Target. That's all folks... Cheers!
Read more

VBA - Change IF Statements for Reference Table

Image
If Statements are a crucial part of the daily VBA Development process. You can't live without conditional statements since they are, in core, the most fundamental decision derivative expression of programming. However, in Excel, where you have to deal with a lot of conditions, it might get tricky to use them. Imagine having 7 different conditions and for each condition 2 sub-conditions where 3 of them don't result in final "ELSE" condition. Hard to see, right? How many lines do you need to code to accomplish this? How about if in the future, you need to add more conditions, will you know where to place them? A lot of If Statements can be replaced for a table that drives all possible conditions. This will help with readability and will also help you to avoid modifying or adding more code in the future. Check this example where you have to return a fruit type based on the arguments passed to a function. (DO NOT PAY ATTENTION TO DESCRIPTIONS, THEY ARE VERY R...
Read more