Category Archives: Other

excel character map

Excel Character Codes and using CHAR / CODE functions

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Often we look to certain symbols in Excel that are hard to locate on the keyboard. Or the opposite – we look to find the Excel character codes for certain characters. In such cases we must usually revert to using the CHAR Excel function or the CODE Excel function. Let us explore these functions and how to use them both in Excel and VBA today.

Excel Character Codes

First let us start with exploring the Excel Character codes.

What is a Character Code? Each character is encoded as a number within a specific character map e.g. in ANSI or UTF8 standard

The table below represent the full default encoding in Excel:

On the right side is the Excel character (CHAR) and on the left you have the numeric representation (CODE) of the character.

Converting Characters to Codes (Excel CODE function)

The Excel CODE function allows you to easily obtain the numeric code for the provided character (1 character string).

Syntax

The syntax for the CODE function in VBA is:

CODE( string )

Parameters

string
A single character string.

Example usage

Excel

Below example of using the Excel CODE function:
excel code function

VBA

Below VBA equivalent of the Excel CODE Funtion – the Asc function

Converting Codes to Characters (Excel CHAR function)

The Excel CHAR function allows you to easily obtain the character represented by the numeric code.

Syntax

The syntax for the CHAR function in Excel is:

CHAR( number)

Parameters

number
A number from 0-255.

Example usage

Excel

Below example of using the Excel CHAR function:
excel char function

VBA

Below VBA equivalent of the Excel CHAR Funtion – the Chr function:

vba option explicit

VBA Option Explicit – explicit variable declaration

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

The VBA Option Explicit statement is such useful feature in Excel VBA, it is strange, this is the first time I am writing a post about it. It proves incredibly useful in preventing typos and other stupid mistakes when developing Visual Basic for Applications macros. But what does it actually do?

What does Option Explicit do?

The VBA Option Explicit statement can either:

  • Forces an explicit declaration of all variables in a VBA Module
  • Allow the implicit declaration of variables in VBA Modules

However, the first question most people have is…

What is explicit and implicit variable declaration?

Explicit variable declaration in VBA is the declaration of a variable with the use of the VBA Dim statement:

Dim x as Long 'Explicit declaration
x = 1

Implicit variable declaration on the other hand is the opposite – the usage of a variable without any preceding declaration. The VBA Compiler will create a variable that has been used even if it was not preceded with an explicit declaration:

x = 1 'Implicit declaration

Option Explicit usage

Now that we know what implicit and explicit declarations we can ponder on a second on why we would want to force explicit variable declaration. Well, for a number of reasons:

  • To prevent variable name typos
  • Clean up VBA Code
  • Optimize VBA performance
By default all implicit variables are Variant types. Variants are much less efficient than other data types (Longs, Integers, Strings etc.). Read more on VBA Performance here

Using Option Explicit

Option Explicit 'Declare that all variables are to be explicitly declared

Sub Main
 Dim x as Long
 x = 1 'OK!
 y = 10 'ERROR!
End Sub

option explicit errorWhat happens when you try to declare a variable implicitly. Believe me, it is better to fix Variable not defined errors than scramble to find variable name typos.

Turn on Option Explicit by default

For me the Option Explicit statement is a must requirement. I turn it on by default in all my macros. Even when using Variant variables.

turn on option explicitHow to turn on Option Explicit by default for all VBA Code Modules? Go to Tools->Options.... In the Options window select Require variable declaration. That is it! Similarly you can turn off the addition of the Option Explicit statement in all your VBA Code Modules by unchecking this option.

MsgBox vs InputBox vs UserForm

VBA MsgBox vs InputBox vs UserForm

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

One of the most common mistakes in VBA is using incorrect forms/dialogs for usually simple tasks. The VBA UserForm is one of the most exploited VBA modules I have ever seen (as opposed to VBA Class modules which are underutilized ;)). VBA developers and Excel users seem to think often that every dialog box and form needs to have that personal touch. The effect is often the opposite – with users being confused or experiencing errors with custom UserForms without proper error handling.

Today I want to make it easier for all those VBA users out there.

The form/dialog decision tree

Use the guide below when making your decision on whether you need that UserForm or if you are using that Messsage Box or InputBox in the right case:
VBA Message Box is

Decision Tree: MsgBox vs InputBox vs UserForm
Decision Tree: MsgBox vs InputBox vs UserForm

When to use Message Boxes?

Use the VBA MsgBox in the following situations:

  • Short message / information / warning – you want to present the user with a simple message be it an error, message or a warning
  • Yes or No decisions – ask the user if he/she agrees to your questions/ask or if the macro is to proceed etc.

Don’t use the VBA MsgBox in the following situations:

  • Long text messages – when wanting to display long messages or text

When to use the InputBox?

Use the VBA InputBox in the following situations:

  • Simple single variable input – you want the user to input a simple variable such as age, length etc.

Don’t use the VBA InputBox in the following situations:

  • Yes or No / Boolean variables – don’t ask the user to type in yes or no, 1 or 0 or similar boolean variables. Use a MsgBox instead.
  • Enumerations – don’t ask the user to input an item from a discrete list of values. Use a UserForm and a dropdown or listbox instead

When to use the UserForm?

Use the VBA UserForm in the following situations:

  • Complex input / select / interaction – use the UserForm when other options fail and simply a MsgBox or InputBox won’t do the trick

Don’t use the VBA UserForm in the following situations:

  • Simple single variable input – need the user to input an age or length value? Why not use the InputBox instead?
  • Yes or No / Boolean variables – use the MsgBox instead

Agree or disagree? Let me know in your comments!

Short VBA Code

Shorter VBA code!

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 4.00 out of 5)
Loading...

Today let’s expand on how to create shorter VBA code that is easier to read and manage. Writing VBA code that works is usually the tip of the iceberg for most. However, more importantly it is useful to be able to write VBA code that is clear and concise. Shorter and concise blocks of code are usually easier to maintain and debug, hence allowing you to located errors more quickly than in blocks of code that are long and chaotic.

Wrapping variable declaration

Variables should always be declared at the beginning of the current scope – whether it’s the scope of a procedure, class or module. VBA allows you to merge your variable declarations to a single row, thus making you code more concise. Remember to use the Option Explicit statement if you want the compiler to throw errors if a variable was not declared – a very useful best practice. Remember not to wrap too many variable declarations – so they will require scrolling to the right afterwards!

Instead of this:

Dim i As Integer
Dim str As String
Dim dat As Date

Do this:

Dim i As Integer, str As String, dat As Date

Wrapping lines of codes

The “:” character can be used in most cases to consolidate several lines of code into 1.

Instead of this:

i = 10
str = "Hello"
dat = Now

Do this:

i = 10: str = "Hello": dat = Now

Consolidating rows is especially useful when wanting to declare and define a variable in 1 line as we are used to in most other programming languages, as such:

Dim i as Integer: i = 10

The VBA With Statement

The With statement defines a scope which refers to a single object and allows to interact directly with properties of that object without having to reference the object every time. The “.” character is used directly to reference properties within that object.

Instead of this:

Cells(1, 1).Font.Color = RGB(10, 20, 30)
Cells(1, 1).Font.Bold = True
Cells(1, 1).Font.Size = 20

Do this:

With Cells(1, 1).Font
  .Color = RGB(10, 20, 30)
  .Bold = True
  .Size = 20
End With

Although this may not save you lines of code, but allows you to remove the redundant references to your object and makes sure each line reference the same object.

Use Iif instead of If-Else when possible

The Iif function is defined as follows:

IIf ( [boolean expression] , [return if true] , [return if false] )

The Iif function returns either value depending on the evaluation of the boolean expression. It can easily replace a single If-Else statement in scenarios where a variable needs to be defined. You can also nest several Iif functions if needed to replace If-ElseIf-Else statements.

Instead of this:

Dim i as Integer, str as String

If i = 10 Then
  str = "=10"
Else
  str = "<>10"
End If

Do this:

Dim i as Integer, str as String

str = IIf(i = 10, "=10", "<>10")

Reference Named Ranges directly

Named Ranges can be referenced directly by escaping the Named Range name with square brackets “[ ]”.

Instead of this:

ThisWorkbook.Names("MyNamedRange").RefersToRange.Value = "Hello there!"

Do this:

[MyNamedRange] = "Hello there!"

Encapsulate reusable objects

Often you will find the need to reuse certain variables or sets of variables within your code. It is a best practice to encapsulate such properties/variables into objects such as a VBA Class, VBA Enum or VBA Type object.

Often we find variables that are strongly associated, or describe properties of a single object. In the example below notice how the code is illegible:

'So many variables... all describing obviously 3 different vehicles
Dim car1Type as String, car1FuelLeft as Double, car1Miles as Long, car1License as String, car1Miles as Long
Dim car2Type as String, car2FuelLeft as Double, car2Miles as Long
Dim car3FuelLeft as Double, car3License as Double, car3Miles as Long
car1Type = "Sedan"
car2Type = "Hatchback"
car3Type = "Sedan"
'...What a mess...

Now let’s rewrite the code again:

Enum CarType
    Sedan
    Hatchback
    SUV
End Enum

Type Car
    myType as CarType    
    myLicense As String
    myMiles As Long
    myFuel as Double
End Type
'...
Dim car1 as Car, car2 as Car, car3 as Car
car1.myType = CarType.Sedan
car2.myType = CarType.Hatchback
car3.myType = CarType.Sedan
'... So much better...

Notice the second approach is so much easier to read and maintain – when we encapsulate the Car properties into a Car Type object and the CarType enumeration into an Enum object. It might not look shorter at first glance. But believe me it will definitely translate to less cluster and fewer bugs. Enumerations are especially useful as VBA “Intelisense” will hint the possible values within the enumeration.

Similarly you might also want to learn how to use VBA Class modules to encapsulate not only variables but also object methods (Functions and Procedures).

Rule of Thumb – Entire VBA procedure visible without scrolling

Shortening your code makes sense when you are optimizing the amount of space used by VBA code in a smart way – in order to reduce the need for scrolling (in both the horizontal and the vertical dimensions) which is simply a waste of your coding time. You are advised to used the suggestions for shortening your VBA code wisely. However, above all you need to learn…

Rule of the Thumb: An entire Procedure should be visible w/o scrolling

This is one of the most important rules I learned early on – the scope of an entire Procedure should be visible without scrolling on your screen. Ok, but what if my procedure is too long? Split it into separate procedures!
vba_screen
Why use this Rule of Thumb?:

  • Easier to read – you can more easily understand what the procedure is meant to do if you can see what it does end-to-end
  • Encourages code re-usability – group blocks of code that can be reused frequently into separate procedures
  • Easier debugging – when you have divide one huge procedure into smaller consecutive parts debugging takes less time as you can quickly jump over whole logical blocks of code (F8 vs SHIFT+F8)

Example: Smart procedure split

Let’s use our newly learned Rule of Thumb in a simple exercise. See the procedure below obstructed partially by the bottom panel of our VBA Project view:

Obstructed VBA Procedure
Obstructed VBA Procedure

Code:

Sub SomeProcedure()
    'Load file
    Dim fileName As String, fileNo As Long, textData As String
    fileName = "..."
    fileNo = FreeFile
    Open fileName For Input As #fileNo
    Do While Not EOF(fileNo)
       Line Input #fileNo, textRow
       textData = textData & textRow
    Loop
    Close #fileNo
    
    'Save file
    fileName = "..."
    fileNo = FreeFile
    Open fileName For Output As #fileNo
    Print #fileNo, textData
    Close #fileNo
End Sub

What we need to do is split this procedure so that our code is visible within a single screen. It makes sense to group blocks of code that provide some logical feature. In the example above it’s obviously 2 separate blocks – the 1st one reading in a text file, and the second one which dumps the text into a separate file:

Sub NewSomeProcedure()
    Dim textData As String
    
    'Load file
    textData = LoadFile("...")

    'Save file
    Call SaveFile("...", textData)
End Sub

Function LoadFile(fileName As String)
    Dim fileNo As Long, textData As String
    fileNo = FreeFile
    Open fileName For Input As #fileNo
    Do While Not EOF(fileNo)
       Line Input #fileNo, textRow
       textData = textData & textRow
    Loop
    Close #fileNo
    LoadFile = textData
End Function

Sub SaveFile(fileName As String, textData As String)
    Dim fileNo As Long
    fileNo = FreeFile
    Open fileName For Output As #fileNo
    Print #fileNo, textData
    Close #fileNo
End Sub

Much better! Notice each procedure is now easy to read and does not require scrolling. What is more our new procedures – LoadFile and SaveFile can well be reused in future cases which will definitely translate ultimately into less lines of code in your VBA Project.

Summary

Got any more tips? Do share in the comments!

programming languages

Programming languages analysts should learn

1 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 4.63 out of 5)
Loading...

Programming is a skill often associated only with Developers and not so much with analysts or data scientists. Why learn to code when you have tools such as Excel with it’s PivotTables, filtering and formulas. On the other hand, when thinking about analytic tools it seems like Tableu, ClickView, SAS or IBM SPSS is the way to go.programming languagesThese tools are great when working with Big Data. But most of us work with data sets counting not more than a few hundred thousand records. So why use a sledgehammer to crack a nut? Programming languages such as VBA, SQL or R let you squeeze much more of Excel and traditional tools .

VBA (Visual Basic for Applications) (MS Office)

USEFULNESS:
EASY TO LEARN:

Excel is the entry-level tool for most analysts and data scientists, allowing for easy data crunching tasks. In most cases formulas and PivotTables suffice in everyday analytics tasks. However, VBA extends Excels capabilities almost without limits – allowing for creating complex algorithms, extraction of data from various data sources, web scraping and load others. VBA owns it popularity to Excel beings its programming and testing environment at the same time. Write a piece of code and see it run in seconds.
If you want to progress as an analyst / data scientist – learn VBA.

One other perk to learning Visual Basic for Applications (VBA) is that the syntax is identical to VBscript. If you are not familiar – VBscript is a Windows scripting language working alongside Windows Batch Scripting (used in BAT files). VBscript can be used to write Windows scripts that don’t need Excel or MS Office installed on the workstation. VBA code can often be copied identically and used to create VBscript (with some differences like VBscript cannot include data types e.g. “Dim str as String” would raise an exception).

Example uses of VBA

programming languages vbaWhen VBA can prove useful?

Well there are ample examples to consider below. One of the most often proven ways to start is by using the Record Macro feature on the Developer tab.

  • Automate mundane Excel tasks e.g. formatting, editing etc.
  • Creating new custom Excel functions (UDF)
  • Implementing algorithms or complex formulas e.g. complex computations
  • Extending capabilities e.g. working with files, connecting with Internet resources
  • Creating Excel-based apps with User Forms and interaction

Code example

Here is a simple VBA script to show a simple message box with Hello World!

Sub HelloWorldProcedure()
  MsgBox "Hello World!"
End Sub

Where to start?

SQL (Structured Query Language) (RDBMS)

USEFULNESS:
EASY TO LEARN:

SQL lets you unleash the potential of database development. While Visual Basic (VBA is an implementation of Visual Basic) is a general-purpose scripting programming language, SQL is a special-purpose programming language- aimed at running queries and CRUD (Create, Read, Update, Delete) operations on relational databases such as MySQL, MS SQL, Oracle, MS Access etc. SQL is ubiquitous in the data world.

What is often not appreciated about SQL is that it supported by MS Office. Microsoft developed a database engine (JET.OLEDB and the newer ACE.OLEDB), that supports SQL, and can be used to query several Microsoft products such as MS Excel and MS Access (but also other formats like text files). Why is this important? SQL let’s you carry out many operations much easier and quicker e.g. merging Excel worksheets, filtering Excel worksheets on multiple complex conditions, linking Excel Workbooks with other Workbooks or Access databases. If you wanna learn more check out my Excel SQL Add-In

When will you want to reach out for SQL? Whenever you are analyzing/processing large data sets. Excel will typically start falling apart when processing 1000k record databases with the use of formulas, PivotTables etc.

Feel free to check-out this SQL Quick start learning video for Beginners:

Example uses of SQL

programming languages sqlSQL currently rules the world of data, even though NoSQL databases have enriched the database mix. Although many have announced the end of SQL times to be near, recent developments in approaches to programming such as micro-services and the overall ease of use and integrity of SQL database seem to negate these forecasts. Most datasets sit on SQL databases (with MySQL in the lead) so as an analyst sooner or later you will benefit from knowing how to query these datasets directly looking for insights and when building reports.

But does SQL come in handy when you are not dealing with large datasets or structured relational databases? Sure both in Access and Excel SQL can and should be used when dealing with data tables. Here are some of the main aspects SQL is valued for:

  • CRUD: SQL enables Create, Update, Update, Delete operations on relational databases (relations of tabular data sets)
  • Queries: SQL allows you to create queries based on multiple tables of data
  • Performance: SQL is fast and efficient for processing databases including large amounts of data

Code example

List all people at the age above 30, and with red eyes from a hypothetical People table :

SELECT * FROM People
WHERE Age > 30 AND EyeColor = "Red"

Where to start?

R programming language

USEFULNESS:
EASY TO LEARN:

R is a powerful programming languages for statistical computing and graphics. It’s one of the top programming languages used by data scientists (along with e.g. Python) out there (at least on Kaggle as I am aware). R has a moderately steep learning curve as its not a typical programming language, and was historically created by statisticians not developers, although it certainly is worth the effort. R focuses on operations on data frames (tables) and its whole design is oriented around this. What is more important in the case of R is its vast CRAN library of packages that provide additional statistical, graphics and machine-learning capabilities. R is also the go to language when processing so called Big Data. It make slicing and dicing data easy and pleasant.

Another nice thing about R is that it comes with a software environment (RStudio) similarly like you have the Visual Basic environment packed together with MS Office applications. This makes starting to work with R much easier, especially for beginners.

Here is a nice zero-to-hero learning series on R:

Example uses of R

Typically R is considered a great tool for the below. However, keep in mind that you can almost extend R with any capability by downloading the right package for R’s CRAN library e.g. Web Scraping etc.

  • Data exploration e.g. plotting statistical data relationships
  • Statistical data analysis
  • Data cleansing – correcting, formatting series of data e.g. CSV and text files
  • Machine-learning algorithms

Code Example

A simple function that introduces itself:

hello <- function( name ) {
  sprintf( "Hello, %s", name );
}
hello("World")

Just how easy it is to load CSV/text file data with headers:

data <- read.csv("data.csv", header = TRUE)
x <- c(1,2,3,4,5,6)   # Vector of numbers
y <- x^2  #Result: c(1, 4, 9, 16, 25, 36)

Where to start?

Here are some useful links to get you started with the R programming language:

Other programming languages worth mentioning

Now for some other honorable mentions worth exploring:

Python

USEFULNESS:
EASY TO LEARN:

Python is one of the most popular script programming languages out there currently. Especially valued for its ease of use and for being applicable to many environments and scenarios. Python is great for writing quick scripts to process files, data or automate easy tasks. It doesn’t have the overhead of object oriented programming languages and is very flexible. I personally use Python most often for Web Scraping or for automating Linux/UNIX tasks.

If you already know VBA and Excel is your primary working environment, Python might be of less value to you.

C#

USEFULNESS:
EASY TO LEARN:

C# is Microsoft’s flagship programming language and a rival to the all-popular Java. C# is a typical Object Oriented programming language and it’s primary software development environment is Microsoft’s Visual Studio. C# is very useful when making efficient and optimal solutions. Visual Studio is a big asset of C# as programming in VS is pleasant and easy. When to use C#? When you need a Graphical User Interface to put a face on your solution and when you want to “click out” most of your code instead of writing it.