No Clocks

When using native Excel functions inside of VBA, use Application. Instead of Application.WorksheetFunction. The application will not throw any run-time errors.

You can use the underscore character to continue a VBA statement on a new line. This will make your code easier to read. In the sample workbook we provide, the email function uses this method.

Use WITH to shorten your VBA code. This is commonly used only to type a range once, then use WITH to do multiple things with the range.

Use the locals window in VBA to keep track and view the data inside variables. This is especially helpful for viewing arrays. You just need to add a breakpoint in your code. Highlight a line, then click F9. Then when the code runs, it will stop there, and all variables above that line will show in the locals window.

Innovation in technology has disrupted nearly every industry creating mounting internal and external pressures on organizations to accelerate the adoption of their digital agendas. The COVID-19 pandemic further brought to light the need for technology to be adaptable, powerful, and scalable. Insurance companies have been trying to keep up with the pace of innovation, primarily focusing on adopting modern technology on the consumer experience front.

Many have modernized the front office processes that support application, underwriting, and claim handling. The significant benefits achieved through process optimization and the use of modern technology have been the topic of discussions at many insurance conferences and publications.

As the insurance industry leaps into the future, actuaries must not only proactively refine their roles and responsibilities within insurance companies, but also seek opportunities for improvements and optimization in their day-to-day work. This article will focus on how technology and actuarial process optimization will support the role of the future actuary as a leader, risk manager, and technologist.

Opportunities for APO

Actuaries are valuable and strategically important resources to insurance companies. We are trained both on the job and through an intensive education and exam curriculum to study and own insurance risk. As highly capable professionals, actuaries are often self-reliant, and are interested in owning all technology tools, data and processes that support their daily jobs. However, this hands-on approach can lead to performing many tasks that do not require actuarial expertise. By occupying ourselves with various high effort but low value tasks, we often neglect to focus on higher value tasks that are truly valuable to the future of our organization. Advancements in technology and the desire for lean operations have contributed to many insurers evaluating their strategic direction and the role of the actuary of the future, shifting their focus onto highest value tasks.

Unnecessarily complex and error prone ETL, production and reporting workflows. Time and resources wasted on resolving errors and tracing back complex process steps. Production and process errors that can result in misstatements and delays in reporting. Multiple unvalidated spreadsheets with overlapping functionalities. Multiple sources of information, but no single “source of truth.” Storage and processing time wastage.

There are popular misconceptions surrounding VBA and object-oriented programming (OOP), usually in 2 forms: VBA isn’t really OOP, so you can’t really use OOP principles with VBAOOP makes things too complicated; procedural programming is all you need anyway

Coming from a procedural mindset, that can feel like you’re dealing with several layers of lasagna. That does require a change in how you perceive the code.

Procedural design enables you to solve business problems quickly so that you can get on with other stuff. However, what if it’s so successful, that they come back for more; asking you for more features? How many changes do you have to make? With procedural programming, the upkeep is cumulative; first few feature requests are easy and put in action quickly. Next few, it takes more time and more tweaking. Some more, then it feels a bit harder and harder. But coding should not be like that! Adding a new feature should not scale on an exponential scale! That is what the OOP promises; by keeping a clean codebase, it is easy to describe the new feature and integrate it into the codebase with minimum change.

most programmers nowadays should be emphasizing writing refactor-friendly code. What do we mean by refactor-friendly? Basically, it is a codebase that is easy to change because you are able to change only pieces that actually needs to change and no more than that. That is very difficult to do in a purely procedural system.

The other important aspect to learn is that we want to make the wrong code look obviously and blatantly wrong.

Taking up on OOP principles can significantly help us with making wrong code look wrong which means it becomes easier for us to fix the code. You’ve probably had to deal with a giant hundreds-line procedure with the great wall of declarations and deeply nested code and had the thrill of debugging it and cursing while your minor change cascades into something catastrophic. Well, there’s a better way!

To reinforce the concepts, we will do a build-up starting with familiar approach and transforming it into a clean codebase that is very refactor-friendly. The benefit is that you end up with a codebase that is easy to read, understand and maintain. Because this assumes you are familiar with procedural procedure (e.g. writing small functions or routines that perform a complex task by breaking it down into small steps), we need to provide a good transition from procedural mindset to object-oriented mindset.

We will start with creating custom types, and doing work with it, then use it as a basis for our first class.

Creating your custom types You may have already used a user-defined type (UDT), which is a convenient way to create a structure of closely related properties together. You may have used it before especially if you’ve ever had to use certain API functions via the Declare statements. Let’s start with a Person UDT. We can create a new standard module and define a UDT within the module: Public Type Person  FirstName As String  LastName As String  BirthDate As DateEnd Type

One thing about a UDT is that it cannot have any methods.

when we read the code, it is easy to understand what it is doing because we separate the mechanics of the creation from the current context which just needs something created without knowing the particular details in the act of creation

There is no way for us to control the access. VBA does not allow us to create an UDT that cannot be edited once created. That is often referred to being “immutable”. So, when we pass around UDT, we are always trusting that everyone will follow the same convention we build around the type.

We saw that the UDT can be instantiated multiple times, enabling us to juggle more than one instances of same type at the same time. We wrote some procedures that interacts with the UDT to compensate for the shortcomings of the UDT such as making creation easy or managing some sensitive change such as changing person’s last name which may have additional constraints beyond just the code itself. We saw that an UDT does not really do a good job of managing the access to properties, which requires us to follow conventions that are not enforced by the compiler, which can make the coding around an UDT highly prone to errors or omissions.

The very first thing we want to do with our first class is to define the private data it needs to have to work correctly. We could start with nothing but public fields

Controlling access to methods via interfaces