VBA : Excel : Some Useful Methods

[!] NOTIFICATION: These examples are provided for educational purposes. Using this code is under your own responsibility and risk. The code is given ‘as is’. I do not take responsibilities of how they are used.

  1. Opening a new sheet:
    Public Sub OpenNewSheet(Optional sheetName As String = vbNullString)
        Dim ws As Worksheet
        Set ws = Sheets.Add
        If (sheetName <> vbNullString) Then
            ws.Name = sheetName
        End If
        Cells(1, 1).Select
    End Sub
  2. Knowing if a string is alphanumeric:
    Function isAlphanumeric(str As String) As Boolean
        Dim i As Integer
        isAlphanumeric = True
        For i = 1 To Len(Trim(str))
            Select Case Mid$(Trim(str), i, 1)
                Case "A" To "Z", "a" To "z", "0" To "9"
                Case Else
                    isAlphanumeric = False
                    Exit For
            End Select
        Next i
    End Function
  3. Display references + Remove missing references + Add references by GUID:
    Private Const REFERENCE_ALREADY_IN_USE = 32813
    Private Const REFERENCE_ADDED_SUCCESSFULLY = vbNullString
    Private Const Reference_Word As String = "{00020905-0000-0000-C000-000000000046}"
    Private Const Reference_MSComCtl2 As String = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}"
    Private Const Reference_MSForms As String = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}"
    Private Const Reference_Office As String = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
    Private Const Reference_stdole As String = "{00020430-0000-0000-C000-000000000046}"
    Private Const Reference_Excel As String = "{00020813-0000-0000-C000-000000000046}"
    Private Const Reference_VBA As String = "{000204EF-0000-0000-C000-000000000046}"
    Private theRef As Variant
    
    '
    ' AddReferences Macro
    '
    Sub AddReferences()
        addReferenceByGUID (Reference_Word)
        addReferenceByGUID (Reference_MSComCtl2)
        addReferenceByGUID (Reference_MSForms)
        addReferenceByGUID (Reference_Office)
        addReferenceByGUID (Reference_stdole)
        addReferenceByGUID (Reference_Excel)
        addReferenceByGUID (Reference_VBA)
    End Sub
    
    Private Function addReferenceByGUID(strGUID As String)
    
        On Error Resume Next
    
         removeMissingReferences
    
        'Clear any errors so that error trapping for GUID additions can be evaluated
        Err.Clear
    
        'Add the reference
        ThisWorkbook.VBProject.References.AddFromGuid _
        GUID:=strGUID, Major:=1, Minor:=0
    
        Select Case Err.Number
        Case Is = REFERENCE_ALREADY_IN_USE
             Debug.Print strGUID & " Reference Already in use"
        Case Is = REFERENCE_ADDED_SUCCESSFULLY
              Debug.Print strGUID & " Reference Added"
        Case Else
            MsgBox "A problem was encountered trying to" & vbNewLine _
            & "add or remove a reference (" & strGUID & ") in this file" & vbNewLine & "Please check the " _
            & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
        End Select
        On Error GoTo 0
    
    End Function
    Private Sub removeMissingReferences()
        Dim i As Long
        For i = ThisWorkbook.VBProject.References.count To 1 Step -1
            Set theRef = ThisWorkbook.VBProject.References.item(i)
            If theRef.isbroken = True Then
                ThisWorkbook.VBProject.References.Remove theRef
            End If
        Next i
    End Sub
    
    Public Sub displayReferencesInUse()
        Dim theRef As Variant, i As Long
        For i = ThisWorkbook.VBProject.References.count To 1 Step -1
            Set theRef = ThisWorkbook.VBProject.References.item(i)
            Debug.Print "Reference:" & theRef.Name & " " & theRef.GUID
        Next i
    End Sub
  4. Fill ComboBox (or ListBox) with sheet names + Add string item into ComboBox (or ListBox):
    Public Sub fillComboBoxWithSheetNames(cmbBox As Variant, defaultItem As String)
        cmbBox.Clear
        Dim i
        For i = 1 To Sheets.count
            addItemIntoComboBox cmbBox, Sheets(i).Name, defaultItem
        Next i
    End Sub
    
    Public Sub addItemIntoComboBox(cmbBox As Variant, item As String, Optional defaultItem As String)
        cmbBox.AddItem item
        If defaultItem <> vbNullString Then
            If InStr(item, defaultItem) <> 0 Then
                cmbBox.Value = item
            End If
        End If
    End Sub
  5. Add Columns:
    Public Sub addColumns(sheetName As String, columnNames As String)
    
        Dim ColArray() As String
        Dim oWS As Worksheet
        Dim lastColumn As Long
        Dim j As Long
    
        On Error GoTo Disp_Error
    
        Set oWS = Sheets(sheetName)
    
        ColArray = Split(columnNames, ",")
        For j = LBound(ColArray) To UBound(ColArray)
            lastColumn = getLastColumn(oWS) + 1
            oWS.Columns(lastColumn).Insert
            Cells(1, lastColumn) = Trim(ColArray(j))
        Next
    
    Disp_Error:
        If Err <> 0 Then
            MsgBox Err.Number & " - " & Err.Description, vbExclamation, "Error Adding Column"
            Resume Next
        End If
    
    End Sub
  6. Get last column + Get last row:
    Public Function getLastColumn(oWS As Worksheet) As Long
        getLastColumn = oWS.Cells(1, Columns.count).End(xlToLeft).Column
    End Function
    
    Public Function getLastRow(oWS As Worksheet) As Long
        getLastRow = oWS.Cells(Rows.count, 1).End(xlUp).Row
    End Function
  7. Find out is column exist:
    Public Function doColumnExist(sheetName As String, searchColumnName As String)
        Dim oWS As Worksheet
        Set oWS = Worksheets(sheetName)
        Dim columnNamesRange As range
        Set columnNamesRange = oWS.range(oWS.Cells(1, 1), oWS.Cells(1, getLastColumn(oWS)))
        Dim i
        For i = 1 To getLastColumn(oWS)
            If searchColumnName = columnNamesRange.Columns(i).Text Then
                doColumnExist = True
                Exit Function
            End If
        Next i
        doColumnExist = False
    End Function
Share

Notes: Operative Systems – Part 1

(Operative Systems – Part 2) Next >

NOTIFICATION: These notes are published for educational purposes. Using these notes is under your own responsibility and risk. These notes are given ‘as is’. I do not take responsibilities for how you use them.

PDF Content:

  • Software and Hardware structure
  • Application Binary Interface (ABI)
  • Application Programming Interface (API)
  • Memory hierarchy
  • Hard drives
  • Interrupt processing
  • What is an Operating System (OS)
  • CPU privilege levels
  • Process
  • Memory layout of a typical process
  • Multiple processes sharing main memory
  • Process creation
  • Process hierarchy tree
  • Exec(), wait(), fork(), waitpid(), sleep(), and exit() functions
  • Orphan process
  • Zombie process
  • Possible process states
  • Kernel-level data structure
  • Process management
  • Memory management
  • File management
  • System call
  • Inter-process Communication (IPC)
  • Semaphores, signals, shared memory, sockets, pipes
  • Parent-child communication using pipes
  • read() and write() functions
  • Error handling
  • Handling signals
  • SigChild
  • CPU scheduling
  • Process life-cycle
  • CPU-bound process
  • I/O-bound process

Operative_Systems_1

 

(Operative Systems – Part 2) Next >

Share

Notes : C++ – Part 3

< Previous (C++ – Part 2) | (C++ – Part 4) Next >

NOTIFICATION: These notes are published for educational purposes. Using these notes is under your own responsibility and risk. These notes are given ‘as is’. I do not take responsibilities for how you use them.

PDF Content:

  • Const correctness (continued)
  • Const member function
  • Pointers
  • Pointers and Arrays
  • Array of pointers
  • Pointers vs. Arrays
  • Allocating a memory
  • Data type class pointer
  • Passing to function by reference using pointers
  • Memory leaks
  • Pointers with increase and decrease operators
  • Pointers to pointers
  • Void pointers
  • Null pointer
  • Pointers to functions
  • Pointers to members
  • Pointer to member operators .* and ->*
  • Casting pointers to members
  • Difference between .* and ->* operator
  • Convert a pointer-to-member-function to void
  • Maps

Cplusplus_3

 

< Previous (C++ – Part 2) | (C++ – Part 4) Next >

 

 

Share

MySQL Tutorial – Part 3

< (MySQL Tutorial – Part 2) Next | (MySQL Tutorial – Part 4) Next >

MySQL Tutorial : Intermediate

Hurray! Intermediate level! Now, we are talking!
Before continue, I would recommend you to review part 1 and part 2 of this tutorial.

MySQL have many functions for different things such as:

  • String functions
  • Control flow functions
  • Numeric functions
  • Precision mathematics functions
  • Date and time functions
  • Search functions
  • XML functions
  • Bit functions
  • Encryption and compression functions
  • Information functions
  • Miscellaneous functions
  • and more…

In this part of the tutorial, we are going to talk about two groups most commonly used: aggregate functions and scalar functions.

The different between them is that aggregate functions return a single value calculated from values in a column while scalar functions return a single value based on the input value.

Commonly used aggregate functions:

  • MAX(): Returns the largest value in a column
  • MIN(): Returns the smallest value in a column
  • SUM(): Returns the sum of all values in a column
  • AVG(): Returns the average value of all values in a column
  • COUNT(): Returns the number of rows
  • FIRST(): Returns the first value in a column. (NO EXISTENT in MySQL)
  • LAST(): Returns the last value in a column (NO EXISTENT in MySQL)

Commonly used scalar functions:

  • NOW(): Returns the current system date and time based on the input value.
  • ROUND(): Rounds a numeric field to the number of decimals specified based on the input value.
  • LENGTH(): Returns the length of a text field based on the input value.
  • UCASE(): Converts a field to upper case based on the input value.
  • LCASE(): Converts a field to lower case based on the input value.
  • MID(): Extract characters from a text field based on the input value.
  • LEFT(): Extract character from the left side of a text field up to a point indicated by an input value
  • RIGHT(): Extract character from the right side of a text field up to a point indicated by an input value
  • FORMAT(): Formats how a field is to be displayed based on the input value.

Lets begin by looking at the most commonly used aggregate functions:

MAX() Function

The MAX() function will return always the largest value of the selected column.

In the previous part of this tutorial we used the function MAX() as follow:

mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
|  1 | Martin     | Agustin     | Pachecho  |
|  2 | Matias     | NULL        | Delgado   |
|  3 | Jose       | George      | Pachecho  |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT first_name, last_name FROM db_examples.tbl_users WHERE first_name='Jose' OR last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Martin     | Pachecho  |
| Jose       | Pachecho  |
+------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT first_name, MAX(last_name) FROM db_examples.tbl_users WHERE first_name='Jose' OR last_name='Pachecho';
+------------+----------------+
| first_name | MAX(last_name) |
+------------+----------------+
| Martin     | Pachecho       |
+------------+----------------+
1 row in set (0.00 sec)

In our last selection, we ask MySQL to provide us a result table displaying the ‘first_name’ column and the ‘last_name’ column. The rows to show should first match the first name ‘Jose’ or the last name ‘Pachecho’.
By using the function MAX(), we obtain only one of those results.

As you can see, this example is very confusing.
Lets create a new table which allow us to work with this function and make it more clear how its work:

mysql> CREATE TABLE `db_examples`.`tbl_orders` (
    -> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `customer_name` VARCHAR( 25 ) NOT NULL ,
    -> `price` INT NOT NULL ,
    -> `date_sale` DATE NOT NULL ,
    -> PRIMARY KEY ( `id` )
    -> );

Now lets upload information to this new table so we can work:

mysql> LOAD DATA LOCAL INFILE '/home/acarlstein/Documents/example_orders.csv'
    -> INTO TABLE db_examples.tbl_orders
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> (customer_name, price, @temp_variable)
    -> SET date_sale = str_to_date(@temp_variable, '%Y-%d-%m');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM tbl_orders;
+----+---------------+-------+------------+
| id | customer_name | price | date_sale  |
+----+---------------+-------+------------+
| 14 | Diego         |   800 | 2009-02-01 |
| 15 | Alejandro     |  1200 | 2008-11-12 |
| 16 | Maria         |  1600 | 2010-04-03 |
| 17 | Maria         |   300 | 2011-03-04 |
| 18 | Diego         |   500 | 2007-06-06 |
| 19 | Diego         |   100 | 2008-03-08 |
| 20 | Alejandro     |   100 | 2009-09-08 |
+----+---------------+-------+------------+
7 rows in set (0.00 sec)

Before continuing, you may notice that we are using the function str_to_date(). We first indicate to LOAD DATA to fill up the customer_name, price and a temporary variable (instead of using the date_sale column).
LOAD DATA will fill up the row using the first two columns (customer_name and price); however, the value its read from the file for the date will go to a variable which will be use later (within str_to_date function) to obtain a date that can be use for the purpose of filling the column ‘date_sale’.

Now that we have the information inside the table ‘tbl_orders’, lets play with it using MAX().

As we explained before,  the MAX() function will return the largest value of the selected column:

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT MAX(price) FROM tbl_orders;
+------------+
| MAX(price) |
+------------+
|       1600 |
+------------+
1 row in set (0.00 sec)

 

The MIN() Function

The MIN() function will return the smallest value of the selected column:


mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT MIN(price) FROM tbl_orders;
+------------+
| MIN(price) |
+------------+
|        100 |
+------------+
1 row in set (0.00 sec)

 

The SUM() Function

The SUM() function returns the total sum of a column. The column must be numeric.

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT SUM(price) FROM tbl_orders;
+------------+
| SUM(price) |
+------------+
|       4600 |
+------------+
1 row in set (0.00 sec)

 

The AVG() Function

The AVG() function returns the average value of all values in a column.

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT AVG(price) FROM tbl_orders;
+------------+
| AVG(price) |
+------------+
|   657.1429 |
+------------+
1 row in set (0.00 sec)

The COUNT() Function

The COUNT() function returns the number of rows.

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT COUNT(price) FROM tbl_orders;
+--------------+
| COUNT(price) |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

The FIRST() Function and LAST() Function

The FIRST() function returns the first value in a column while the LAST() function returns the last value in a column.

This sounds wonderful if wasn’t the fact that these two functions do not exist in MySQL.

The reason because they don’t exist is that the data is stored in b-trees; therefore, there are no definitions of how the data is being stored in a table.

However, we can emulate FIRST() and LAST() functions by using ORDER BY (together with ASC or DESC), and LIMIT.
Emulating the function FIRST():

mysql> SELECT price FROM tbl_orders ORDER BY price ASC;
+-------+
| price |
+-------+
|   100 |
|   100 |
|   300 |
|   500 |
|   800 |
|  1200 |
|  1600 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT price FROM tbl_orders ORDER BY price ASC LIMIT 1;
+-------+
| price |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

Emulating the function LAST():

mysql> SELECT price FROM tbl_orders ORDER BY price DESC;
+-------+
| price |
+-------+
|  1600 |
|  1200 |
|   800 |
|   500 |
|   300 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT price FROM tbl_orders ORDER BY price DESC LIMIT 1;
+-------+
| price |
+-------+
|  1600 |
+-------+
1 row in set (0.00 sec)

 

Lets continue by looking at the most commonly used scalar functions:

 

The NOW() Function

The NOW() functions returns the current system date and time based on the input value.

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2012-02-05 19:11:36 |
+---------------------+
1 row in set (0.01 sec)

For example, we could use the function str_to_date() to format the input, NOW(), and update the date of one record.

mysql> SELECT STR_TO_DATE(NOW(), '%Y-%m-%d');
+--------------------------------+
| STR_TO_DATE(NOW(), '%Y-%m-%d') |
+--------------------------------+
| 2012-02-06                     |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM tbl_orders;
+----+---------------+-------+------------+
| id | customer_name | price | date_sale  |
+----+---------------+-------+------------+
| 14 | Diego         |   800 | 2009-02-01 |
| 15 | Alejandro     |  1200 | 2008-11-12 |
| 16 | Maria         |  1600 | 2010-04-03 |
| 17 | Maria         |   300 | 2011-03-04 |
| 18 | Diego         |   500 | 2007-06-06 |
| 19 | Diego         |   100 | 2008-03-08 |
| 20 | Alejandro     |   100 | 2009-09-08 |
+----+---------------+-------+------------+
7 rows in set (0.02 sec)

mysql> UPDATE tbl_orders SET date_sale=STR_TO_DATE(NOW(), '%Y-%m-%d') WHERE id=14;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM tbl_orders;
+----+---------------+-------+------------+
| id | customer_name | price | date_sale  |
+----+---------------+-------+------------+
| 14 | Diego         |   800 | 2012-02-06 |
| 15 | Alejandro     |  1200 | 2008-11-12 |
| 16 | Maria         |  1600 | 2010-04-03 |
| 17 | Maria         |   300 | 2011-03-04 |
| 18 | Diego         |   500 | 2007-06-06 |
| 19 | Diego         |   100 | 2008-03-08 |
| 20 | Alejandro     |   100 | 2009-09-08 |
+----+---------------+-------+------------+
7 rows in set (0.00 sec)

The ROUND() Function

The ROUND() function rounds a numeric field to the number of decimals specified based on the input value.

Lets say we wish to round the result of a average value obtained from all the orders:

mysql> SELECT AVG(price) FROM tbl_orders;
+------------+
| AVG(price) |
+------------+
|   657.1429 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(AVG(price)) FROM tbl_orders;
+-------------------+
| ROUND(AVG(price)) |
+-------------------+
|               657 |
+-------------------+
1 row in set (0.00 sec)

 

The LENGTH() Function

The LENGTH() function returns the length of a text field based on the input value.

mysql> SELECT
    -> customer_name,
    -> LENGTH(customer_name) AS 'Number of Characters'
    -> FROM tbl_orders;
+---------------+----------------------+
| customer_name | Number of Characters |
+---------------+----------------------+
| Diego         |                    5 |
| Alejandro     |                    9 |
| Maria         |                    5 |
| Maria         |                    5 |
| Diego         |                    5 |
| Diego         |                    5 |
| Alejandro     |                    9 |
+---------------+----------------------+
7 rows in set (0.00 sec)

Notice that I had introduce something new, the AS keyword. The AS keyword is an Alias. In this case we are saying that ‘Number of Characters’ is an alia for LENGTH(customer_name).
In the next part of these tutorial, we will go in more detail.

 

The UCASE() Function

The UCASE() function converts a field to upper case based on the input value.

mysql> SELECT customer_name FROM tbl_orders;
+---------------+
| customer_name |
+---------------+
| Diego         |
| Alejandro     |
| Maria         |
| Maria         |
| Diego         |
| Diego         |
| Alejandro     |
+---------------+
7 rows in set (0.00 sec)

mysql> SELECT UCASE(customer_name) FROM tbl_orders;
+----------------------+
| UCASE(customer_name) |
+----------------------+
| DIEGO                |
| ALEJANDRO            |
| MARIA                |
| MARIA                |
| DIEGO                |
| DIEGO                |
| ALEJANDRO            |
+----------------------+
7 rows in set (0.00 sec)

 

The LCASE() Function

The LCASE() function converts a field to lower case based on the input value.

mysql> SELECT customer_name FROM tbl_orders;
+---------------+
| customer_name |
+---------------+
| Diego         |
| Alejandro     |
| Maria         |
| Maria         |
| Diego         |
| Diego         |
| Alejandro     |
+---------------+
7 rows in set (0.00 sec)

mysql> SELECT LCASE(customer_name) FROM tbl_orders;
+----------------------+
| LCASE(customer_name) |
+----------------------+
| diego                |
| alejandro            |
| maria                |
| maria                |
| diego                |
| diego                |
| alejandro            |
+----------------------+
7 rows in set (0.00 sec)

 

The MID() Function

The MID() function extract characters from a text field based on the input value.

mysql> SELECT MID('12EAS678P', 2, 4);
+------------------------+
| MID('12EAS678P', 2, 4) |
+------------------------+
| 2EAS                   |
+------------------------+
1 row in set (0.00 sec)

 

The LEFT() Function

The LEFT() function extract characters from the left side of a text field to a point indicated by input value.

mysql> SELECT LEFT('12EAS678P', 3);
+----------------------+
| LEFT('12EAS678P', 3) |
+----------------------+
| 12E                  |
+----------------------+
1 row in set (0.00 sec)

 

The RIGHT() Function

The RIGHT() function extract characters from the right side of a text field to a point indicated by input value.

mysql> SELECT RIGHT('12EAS678P', 3);
+-----------------------+
| RIGHT('12EAS678P', 3) |
+-----------------------+
| 78P                   |
+-----------------------+
1 row in set (0.00 sec)

 

The FORMAT() Function

The FORMAT() function formats how a field is to be displayed based on the input value.
The field is formatted to a format like '#,###,###.##'. Numbers are rounded to D decimal places. Then, the result is returned as a string.
If D decimal places is 0, the result has no decimal point or fractional part.

mysql> SELECT customer_name, price FROM tbl_orders;
+---------------+-------+
| customer_name | price |
+---------------+-------+
| Diego         |   800 |
| Alejandro     |  1200 |
| Maria         |  1600 |
| Maria         |   300 |
| Diego         |   500 |
| Diego         |   100 |
| Alejandro     |   100 |
+---------------+-------+
7 rows in set (0.00 sec)

mysql> SELECT customer_name, FORMAT(price, 2) FROM tbl_orders;
+---------------+------------------+
| customer_name | FORMAT(price, 2) |
+---------------+------------------+
| Diego         | 800.00           |
| Alejandro     | 1,200.00         |
| Maria         | 1,600.00         |
| Maria         | 300.00           |
| Diego         | 500.00           |
| Diego         | 100.00           |
| Alejandro     | 100.00           |
+---------------+------------------+
7 rows in set (0.00 sec)

 

< (MySQL Tutorial – Part 2) Next | (MySQL Tutorial – Part 4) Next >

Share

Introduction to Network Security – Part 9

NOTIFICATION: These examples are provided for educational purposes. The use of this code and/or information is under your own responsibility and risk. The information and/or code is given ‘as is’. I do not take responsibilities of how they are used. You are welcome to point out any mistakes in my posting and/or leave a comment.

Some of the mathematical tools (known as number theory) use in network security are prime numbers, greatest common divisor (GCD), Fermat’s theorem, Euler Totient function and theorem, primality testing and Miller Rabin algorithm.

Prime Number

A prime number is an integer p greater than 1 which can only be divided by 1 and itself.

Formally speaking:

“A prime number (or prime integer, often simply called a “prime” for short) is a positive integer p>1 that has no positive integer divisors other than 1 and p itself. ” <http://mathworld.wolfram.com/PrimeNumber.html>

An example of prime numbers: 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, …

An example of numbers that are not prime: 1, 4, 6, 8, 9, 10, 12, 14, 15, 16, 18, 20, 21, 22, 24, 25, …

In the website Math.com you can find a prime number calculator that can tell you if a number is prime or not: <http://www.math.com/students/calculators/source/prime-number.htm>

One of the ways we can use prime numbers is to factorize a number in a unique way:

Any number a greater than 1 can be factored in a unique way using prime p numbers while each consecutive prime number is greater than the previous prime number and their consecutive exponents b are one greater that then previous:

a = p1^b1 * p2^b2 * ... * pn^bn while p1 < p2 < ... < pn and (b1, b2, ..., bn) > 0

For example:

  1. a = 91 = p1 ^ b1 * p2 ^ b2 = 7 ^ 1 * 13 ^ 1 = 7 * 13
    Therefore, P1 = 7 and P2 = 13
  2. a = 3600 = p1 ^ b1 * p2 ^ b2 * p3 ^ b3 = 2^4 * 3^2 * 5^2
    Therefore, P1 = 2, P2 = 3, and P3 = 5

Greatest Common Divisor (GCD)

The greatest common divisor (gcd) is a number in which you can divide two positive numbers a and b and at the same time is common in a and b.

Formally Speaking:

“The greatest common divisor … of two positive integers a and b is the largest divisor common to a and b. ” <http://mathworld.wolfram.com/GreatestCommonDivisor.html>

There are different ways to obtain the greatest common divisor such as using prime factorizations, Euclid’s algorithm, and others <http://en.wikipedia.org/wiki/Greatest_common_divisor>.

Approach 1:

A way to determine the greatest common divisor of two integers a and b is by
comparing the prime factorization of a and b and using their least powers.

Lets assume we wish to obtain the greatest common divisor of 12 and 30, gcd(12, 30).

For 300, we obtain the following prime numbers:

30 = 2^1 * 3^1 * 5^1

For 12, we obtain the following prime numbers:

12 = 2^1 * 3^1 = 2^1 * 3^1 * 5^0

In both cases, we have that 2 and 3 are the prime numbers used in a and b.
Also, we have that 2^1 and 3^1 are their least powers. Therefore,

gcd(12, 30) = 2^1 * 3^1 = 6

Approach 2:

The follow is a more friendly approach to obtain the greates common divisor.
Lets assume we wish to know the greatest common denominator of 7 and 160, gcd(7, 160)

  1. Write down this formula:
    (Dividend) = (Divisor) * (Quotient) + (Remainder)
  2. The greatest number will be the dividend:
    Dividend = 160
    (160) = (Divisor) * (Quotient) + (Remainder)
  3. The other number will be the divisor:
    Divisor = 7
    (160) = (7) * (Quotient) + (Remainder)
  4. Multiply the divisor with a quotient that would get you a number as close as possible to the dividend
    If 160/7 = 22.8571429 then use 22 for the quotient
    (160) = (7) * (22) + (Remainder)
  5. The remainder will be the number that you need to reach 160. Since 7 * 22 is 154 the remainder is 6
    (160) = (7) * (22) + (6)
  6. Now the Divisor became the new dividend and the remainder became the new divisor:
    New dividend = 7 (previous divisor)
    New divisor = 6 (previous remainder)
    (7) = (6) * (Quotient) + (Remainder)
  7. Repeat the process, get a quotient that would multiply the divisor (6) as close as possible to the dividend (7)
    (7) = (6) * (1) + (Remainder)
  8. The remainder would be 1. This remainder is the greatest common divisor between 7 and 160
    gcd(7, 160) = 1

You can find a gcd calculator here:
<http://britton.disted.camosun.bc.ca/gcdlcm/jbgcdlcm.htm>

Fermat’s Theorem

Before going over the Fermat’s theorem, let review an old concept related with this topic, modular arithmetic.

Modular arithmetic (also known as clock arithmetic), is a system in which numbers “wrap around” after reaching a certain value. Of this system, we use the congruence relation on integer known as modulus.

Formally speaking:
“For a positive integer n, two integers a and b are said to be congruent modulo n, (a = b mod n),
if their difference a − b is an integer multiple of n. The number n is called the modulus of the congruence” <http://en.wikipedia.org/wiki/Modular_arithmetic#Congruence_relation>

For Example:

  1. Lets assume we have a = 100, b = 86, and n = 7 such that 100 = 86 (mod 7).
  2. 100 - 86 = 14 in which 14 has 7 as a divisor.
  3. If we divide 100 by 7,
    we find out that the quwootient is 14 and remainder is 2.
  4. Just coincidence, if we have 100 = 2 (mod 7),
    we also find out that the remainder (b) is two too.

Another way to see the previous example is as follows:

100 = 86 (mod 7)

Means that 100 and 86 leave the same remainder when you divide by
7; or, equivalently, that their difference is a multiple of 7.

Here is a link in which you can find the quotient and remainder of a division:
<http://www.analyzemath.com/Calculators_3/quotient_remainder.html>

Another example:

  1. For a = 0 mod 5, If a = 0 mod 5 then a^4 = 0^4 = 0 mod 5
  2. For a = 1 mod 5, if a = 1 mod 5 then a^4 = 1^4 = 1 mod 5
  3. For a = 2 mod 5, if a = 2 mod 5 then a^4 = 2^4 = 16 = 1 mod 5
  4. For a = 3 mod 5, if a = 3 mod 5 then a^4 = 3^4 = 81 = 1 mod 5
  5. For a = 4 mod 5, if a = 4 mod 5 then a^4 = 4^4 = 256 = 1 mod 5

Now that we have an idea about modulus, we can begin talking about Fermat’s Theorem.

Fermat’s theorem also known as “Fermat’s little theorem” (do not confuse with “Fermat’s last theorem”), establish that:

  1. If p is a prime number and for any integer a lower than the prime number p, a<p is a positive integer not divisible by the prime number p.

  2. Or, if p is a prime number then for any integer a, a^p – a will be evenly divisible by p.
    a^p \equiv a \pmod{p}.\,\!
  3. Or, if p is a prime and a is an integer relatively prime to p, then [a^(p−1)] − 1 will be evenly divisible by p.
    a^{p-1} \equiv 1 \pmod{p}.\,\!

(<http://en.wikipedia.org/wiki/Fermat’s_little_theorem>)

For example:

  1. Lets assume p = 3 and a = 2Fermat's little theorem establish that
  2. Then a^(p-1) = 2^(3 - 1) = 2^2 = 4
  3. So, 1 = a^(p-1) mod p = 4 mod 3 = 1

In network security, Fermat’s little theorem is used in public key and primality testing.

Euler Totient Function ø(n)

The Euler Totient function is represented by ø(n) or φ(n) depending the author.

The Euler Totient function establish that:

  1. ø(n) is the number of possitive integers less than n and coprime (also known as relatively prime) to n
  2. Or the number of positive integers less or equal to n that are relatively prime to n, where 1 is counted as being coprime (relatively prime) to all numbers.
  3. Or, ø(n) returns the number of integers less than n (including 1) that are relatively prime to n.

Note: we say that two integers a and b are relatively prime if a and b have no common positive factor other than 1 or, if their greatest common divisor is 1. a is relatively prime to b if gcd(a, b) = 1.

A list of Euler’s Totient Function Values For n = 1 to 500, with divisor lists can be found in the following URL address: <http://primefan.tripod.com/Phi500.html>

For example:

  1. Lets n = 37 so ø(n) = ø(37) = 35. 37 can be divided by 1 and by 37.
    Therefore, all integers from 1 to 36 are relatively prime to 37
  2. Lets n = 35 so ø(n) = ø(35) = 24. 35 can be divided by 1, 5, 7, and 35.
    Therefore, integers 1, 2, 1, 2, 3, 4, 6, 8, 9, 11, 12, 13, 16, 17, 18, 19, 22, 23, 24, 26,
    27, 29, 31, 32, 33, 34 are relatively prime to 35.

You may ask, I did you found these values (such as ø(35) = 24)?

To find these values:

  1. (Case 1) For n = p (a prime), we have that ø(p) = p – 1.
    For Example: Lets p = 7 so ø (p) = ø(7), then ø (7) = 7 – 1 = 6.
    Therefore ø(7) = 6
  2. (Case 2) When n = p^a (power of a prime).
    The numbers with common factor with n are: p, 2p, 3p, . . . ,p^(a-1) * p
    since there are p^(a-1) of them.
    For example: Lets assume we have a prime number p = 5 and a is 2:
    ø(p^a) = p^a – p^(a-1) = (p^(a-1)) * (p – 1) = (p^a) * (1 – 1/p)
    ø(5^2) = 5^2 – 5^(2 -1) = (5^(2 – 1)) * (5 – 1) = (5^2) * (1 – 1/5)
    ø(25) = 5^2 – 5 = 5 * (5 – 1) = (5^2) * (1 – 1/5) = 20
    ø(25) = 20 and all integers relative prime to 25 are
    1, 2, 3, 4, 6, 7, 8, 9, 11, 12, 13, 14, 16, 17, 18, 19, 21, 22, 23, 24.

As a general case for the Euler’t Totient function ø(n), we have that:

  1. Let p be a prime integer dividing n integer so the integers divisible by p are:
    p, 2p, 3p, 4p, . . . , (n/p)(p) where there are n/p number of integers.
  2. Then, the number of integers not divisible by p are: n – n/p = n * (1 – 1/p)
  3. Let q be another prime number dividing n.
    If we wish to find the number of integers divisible by neither p or q,
    then deduct the n/q multiples of q such that q, 2q, 3q, . . . , (n/q)(q)
  4. In case some elements of p and q are common, the n/pq multiples of pq are:
    pq, 2pq, 3pq, . . . , (n/pq)(pq) so n/q – n/pq = (n/q)(1- 1/p)
  5. Therefore the total of integers not divisible by p or q is:
    n(1 – 1/p) – (n/q)(1 – 1/p) = n(1 – 1/p)(1 – 1/q)

General formula for Euler’s Totient Function ø(n):

ø(n) = n * (1 – 1/p1) * (1 – 1/p2) * . . .  * (1 – 1/pm), where p1, p2, . . . , pm are prime factors of n and m is the total number of prime numbers.

Example:

Lets n = 60, p1 = 2, p2 = 4,  p3 = 5 and ø(n= n * (1 – 1/p1) * (1 – 1/p2) * (1 – 1/p3) then

ø( 60) = 60 * (1 – 1/2) * (1 – 1/3) * (1 – 1/5)
ø(60) = (60 – 60/2) * (1 – 1/3) * (1 – 1/5)
ø(60) = (60 – 30) * (1 – 1/3) * (1 – 1/5)
ø(60) = (30) * (1 – 1/3) * (1 – 1/5)
ø(60) = (30 – 30/3) * (1 – 1/5)
ø(60) = (30 – 10) * (1 – 1/5)
ø(60) = (20) * (1 – 1/5)
ø(60) = (20 – 20/5)
ø(60) = (20 – 4)
ø(60) = 16

All integers relative prime to 16 are:
1, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 49, 53, 59

Euler’s Totient Function ø(n) using two Prime Numbers:

Let have two prime number p and q such that p ≠ q so ø(pq) = ø(p)*ø(q) = (p-1)*(q-1) in which

  1. The set {1, 2, …, pq -1} of integers is less than pq
  2. The integers in the set {1, 2, …, pq – 1} are not relatively prime to pq:
    {p, 2p, …, (q – 1)p} and {q, 2q, …, (p – 1)q}
  3. ø(pq) = (pq – 1) – [(q-1) + (p-1)]
    ø(pq) = pq – p – q +1
    ø(pq) = (p-1) * (q-1)
    ø(pq) = ø(p)*ø(q)

For example:

ø(n) = ø(pq) = (p-1) * (q-1)
ø(21) = ø(3 * 7)
ø(21) = (3 – 1) * (7 – 1)
ø(21) = 2 * 6
ø(21) = 12

(<http://home.earthlink.net/~usondermann/eulertot.html>)

Euler’s Theorem

Euler’s Theorem (also known as Fermat-Euler theorem) establish that for every positive integer a relatively prime to n then

For example:

  1. Lets a = 3 and n = 10 then
    ø(n) = ø(pq) = (p-1) * (q-1)
    ø(10)
    = ø(2*5) = (2 – 1) * (5 -1) = (1) * (4) = 4
    a
    ^ø(n) mod n = 1
    3^ø(10) mod 10 = 1
    3^4 mod 10 = 1
    81 mod 10 = 1
  2. Lets a = 2 and n = 11 then
    ø(n) = ø(p – 1)
    ø(11)
    = ø(11 – 1) = 10
    a
    ^ø(n) mod n = 1
    2
    ^ø(11) mod 11 = 1
    2^10 mod 11 = 1
    1024 mod 11 = 1

Primality Testing

  1. Very large prime numbers selected at random are necessary for most of cryptographic algorithms.
  2. Naïve Algorithm: The objective of this algorithm is to divide a number a by all the numbers in turn that are less than the square root of a. This kind of algorithm works for small numbers, but it inefficient for large numbers.

Miller Rabin Algorithm

Miller Rabin algorithm (also known as Miller-Rabin Primality Test) is an algorithm that return a true or false value depending a given value n. Normally, If it outputs true, then n is “probably prime”, else then n is definitely composite.

Approach 1:

[Split Off Power of]: Lets n > 3 and n be odd, k > 0, and  q odd so

  1. [Random Base] Choose a random integer a with 1 < a < n.
  2. [Odd Power] Set b = a^n (mod m) so if b = ±1 (mod n) then output true and terminate.
  3. [Even Powers] For any r with 1 <= r <= k – 1, if b^2^r = -1 (mod n) then output true and terminate else output false

(< http://modular.math.washington.edu/edu/2007/spring/ent/ent-html/node26.html>)

Approach 2:

  1. Lets n > 3 and n be odd, k > 0, and  q odd so .
    Divide (n – 1) by 2 until the result is an odd number.
  2. Let a be an integer 1 < a < n where n > 2 so
    Check which of the following two conditions is true:
    (a) ( ) == true ?
    (b) There exist 1 <= j <= k such that == true ?
  3. If any of the previous conditions (a and b) are true, then n may not be a prime number.

Example:

  1. Lets n = 2047 such that 2047 = 23 * 89.
  2. If so n – 1 = 2^1 * 1023 then
  3. 2^1023 mod 2047 = 1
  4. However, 2047 is not a prime

Approach 3: (Similar to approach 1)

  1. Check if n integer value is prime or not
  2. If n is prime then find integers k > 0, q being odd, such that is true.
  3. if is true then output is true (n maybe prime) else
  4. Check for every value of j going from 1 to k if is true.
    If true then output true (n maybe prime) else
  5. return false (n is not prime)

Probabilistic Consideration

For an odd no prime number n and a randomly chosen integer a where 1 < a < n -1,
we can expect a probability of failure in detecting  that n is not a prime number of less than one quarter of the probabiblities.

If we repeat the Millan Rabin algorithm with different values of a, there is a chance that we find a “maybe” prime number n after trying a t number of tests:

Probabilities of finding a “maybe” prime number n after t test are:
Pr(n maybe a prime number after t tests) = (1/4)^t

For example:
Lets assume we wish perform t = 10 tests using different values of a, we have less than 10^-6 probabilities to find an n that maybe a prime number.

Extra Examples

Lets have a = b mod p such that we wish to know a, b = 5^6 and p is 23 so a = 5^6 mod 23

How can we solve this? Using Modulus Arithmetic. One of the properties say that C^(ab) mod p = (C^a mod p)^b mod p

So,

5^6 mod 23 => 5^(2*3) mod 23 => (5^2 mod 23)^3 mod 23 => (25 mod 23)^3 mod 23

The remaider of 25 mod 23 is 2 (23 = 0, 24 = 1, 25 = 2) or you could divide 25 by 23: 23 * 1 = 23 => 25 – 23  = 2 remainer

(25 mod 23)^3 mod 23 => (2)^3 mod 23 => 8 mod 23 = 8 (is less than 23 so is inside the modulus)

Share