VBA or Complex formulas?

• Download Excel spreadsheet Validator_CNP - 6.3 KB

Introduction

Microsoft Excel is widespread, easy to use and available under free versions (produced by OpenOffice, LibreOffice) data handling software based on programmable and customizable table sheets. As long as the tables contained by spreadsheets are fully customizable, the user has the opportunity to make calculations, to format the input and output data (as string, integer, currency), formulas are also available to aid the user to fulfill from simple to very complex arithmetical, logical and, not at least, financial calculations within the cells the user fills with desired data.

What is an Excel Formula?

A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel. Let's consider a simple adding formula as the picture below illustrates:

What Do We Read Above?

Two terms are being added, by inserting them, each one, in the cell A1 and A2. Using the addition formula, Excel will provide us the result, by prompting it into the cell A3.

But what should we do if we need to fulfill even more complex calculations by using the same Microsoft Excel spreadsheet software?

The answer is simple: we need to put all our data - inputs and outputs - into Visual Basic for Application (VBA) code. But how?

Sub adding_formula()
'Mind the cell we'll ask to provide us the answer (A3)
 Range("A3").Formula = "=SUM(A1:A2)"
End Sub

Every Excel user tends to consider that VBA code are just simple substitutes of Excel formulas. There are hundreds of formulae available in Excel spreadsheet software, and it's also possible to combine them when complex computings are needed to be made using Excel and the formulas provided. VBA Code extends the customization capabilities of Microsoft Excel, if we assume to work with external files and data files, e-mail. VBA code is also the best choice to make possible the work with API and Windows functions and capabilities - because these extra capabilities are not available through formulas.

VBA code is, finally, the best environment to consider security risks as to be expected. That's why I state that no formula is too complex to make us translate it into VBA code, just because VBA code looks more professional or if it can provide a more readable technical content, even though a VBA code proved to be more easy to learn and to write and to put the calculations in terms, than a single-line formula could help us to obtain the desired calculation results.

What's the Point of this Article?

This article covers the complex formula terms. Our sample aids us in calculating the Social Security Number, applying the computing and checking-for-errors algorithm provided by Romanian Identity Law. We'll use to this matter string manipulation, conditional and arithmetic arguments, all of them inside of a single-line sole formula.

The Romanian SSN is called "Cod Numeric Personal" (or Personal Numerical ID). It consists of 13 digits, read from left to right, the 13^th right of it is the check number (or control figure).

Background

To learn what the formula is talking about, please read: CNP - for English readers - https://en.wikipedia.org/wiki/Social_Security_number // for non-English readers - https://ro.wikipedia.org/wiki/Cod_numeric_personal

In a Nutshell

CNP (the Romanian Social Security Number) is the very unique identifier of a person and it's meant to be used as a unique key to access any available personal services across Romania. It consists of 13 digits, every digit having the following meaning:

The CNP looks like: SAALLZZJJNNNC  - or, translating the abbreviations in English: SSYYMMDDCCNNNF (where S - is the sex of the person; AA/YY - show the last 2 digits of the birthyear; LL/MM - the birthmonth of the bearer, like 01 for Jan, 02 for Feb...12 for Dec; ZZ/DD - the day in the bearer's birthday, from 01 to 31; JJ/CC - the county where the bearer was born, from 01 to 39 and 40-46 for Bucharest, NNN - a serial number from 001 to 999 provided by the issuer within the residence county; C/F - is the control or the final digit who tells us if the CNP is correct formed and computed).

How to Compute the CNP and How to Find Out if Our Calculations Are Correct or Not

Let's consider the CNP split by digits, as shown below:

S A A L L Z Z J J N N N C

2 7 9 1 4 6 3 5 8 2 7 9 C

Every digit behind the letters in italic-bold is multiplied by the number below every one of them, excepting the "C". Therefore, we have a 12-terms sum, this sum will be divided finally by 11. The sample CNP used in the workbook behind the link in the very top section of the article is 1831117152458. That means, the bearer:

• is a Male (sex digit is 1) - we multiply this digit by 1 (i.e., the formula is mid(B2,1,1)*2)
• is born in 1983 (the 2^nd and 3^rd digits are 83) in the month of November (the next 2 digits are 11), in the day of 17^th of Nov (the 6^th and 7^th digits are 17).
• is living in Dambovita county (15th county in ascending alphabetical order)
• is registered as the 245^th person in his county.
• applying the multiply and then sum algorithm in the formula below, we get therefore 8 as control final digit which proves to us that the CNP is correctly formed and computed.

Using the Code

The code below, consisting in complex formula, is meant to be used by Excel users who operate personal data in fields like accounting, petitions, and banking.

The formula needed to check a CNP if it's valid stands below:

=IF(OR(MOD(MID(B2,1,1)*2+MID(B2,2,1)*7+
MID(B2,3,1)*9+MID(B2,4,1)*1+MID(B2,5,1)*4+MID(B2,6,1)*6+MID(B2,7,1)*3+MID(B2,8,1)*5+
MID(B2,9,1)*8+MID(B2,10,1)*2+MID(B2,11,1)*7+MID(B2,12,1)*9,11)-MID(B2,13,1)=0,
AND(MOD(MID(B2,1,1)*2+MID(B2,2,1)*7+MID(B2,3,1)*9+MID(B2,4,1)*1+MID(B2,5,1)*4+
MID(B2,6,1)*6+MID(B2,7,1)*3+MID(B2,8,1)*5+MID(B2,9,1)*8+MID(B2,10,1)*2+MID(B2,11,1)*7+
MID(B2,12,1)*9,11)-10=0,MID(B2,13,1)-1=0)),"OK","GRESIT") 

History

This is my first article on SSN calculation using Excel. No previous versions of this proof of concept are available.