# Excel- DNA C# Script – for adding a missing “0” in Danish Social Security Number

Since I wrote this UDF in VBA; I have had plans for making a version written purely in C#- simply to make a higher performing function for large volume data. Excel-DNA UDF makes faster calculations than Excel  – some estimates say 40% faster calculation rate  than excel VBA. I have to test that claim !

Here it comes – my first attempt which I admit could be more smooth.

[ExcelFunction(Description = “My first .NET function”)]

public static object CorrectCPR(object CCR,object CCRU)

{

String InterN;

String BN;

String BZ;

BZ = “0”;

InterN = Convert.ToString(CCR);

BN = Convert.ToString(CCR);

if (InterN.Length == 9)

{

BN = BZ + InterN;

}

else

if (InterN.Length == 10)

{

BN = InterN;

}

else

if (InterN.Length <9)

{

BN = “too short”;

}

else

if (InterN.Length == 8)

{

BN = “too short”;

}

else

if (InterN.Length <8)

{

BN = “too short”;

}

else

if (InterN.Length>10)

{

BN = “too long”;

}

else

if (InterN.Length ==0)

{

BN = “Empty field”;

}

CCRU = Convert.ToString(BN);

return CCRU;

}

}

}

For further reading https://excel-dna.net/

# VB Count Number of appearances of a number in a column.

Count Number of appearances of a number in a column.

This function allows for looking number of appearances of a number – in whole column, parts either of a column or progressively (from here and downwards). Example: Here I have chosen to see how many times the number 3 appears in B2 to B5, which is one time.

Public Function CountAp(find As Variant, lookin As Range)

Dim Count As Integer

For Each cell In lookin

If (cell.Value = find) Then Count = Count + 1 ‘

Next

CountAp = Count

End Function

# VBE -demo code for popup menu and MessageBox

Public Function ADDER(I As Integer)
Dim ADL As Integer
Dim NDS As Integer
NDS = InputBox(“Give me some input”)
If I > 50 Then
MsgBox “I is too High”
End If
ADDER = ADL + I + NDS
End Function

# Windows 10 – error makes the machine unable to browse the internet – but still retains the possibility to send and receive mail.

Here is what I did when my Windows 10 Machine suddenly started to show the error described in the headline.

Open Command prompt as administrator
Click on “Start”
In the searchbar type “CMD”
Rightclick on the command prompt and select “Run as Administrator”
Enter the following command.
netsh int ip reset resetlog.txt –> Press Enter.

# Check if you have Router and Internet Connection.

Am I on the Internet?

During the last 15 years have I had some periods with a flaky internet connection. Both as an avid internet user and a professional, there is nothing more annoying than an unstable internet connection. So in order to be able to describe the problem in quantifiable terms i needed an application to check my internet connection. So I decided to make a simple application and decided to share it.

Using  the application.
When you start the application it will check the connection, then you will have to push the button the check the current status.

Please read the policy before using the application:

This SOFTWARE PRODUCT is provided by THE PROVIDER as is and with all faults.THE PROVIDER makes no representations or warranties of any kind concerning the safety, suitability, lack of viruses, inaccuracies, typographical errors, or other harmful components of this SOFTWARE PRODUCT. There are inherent dangers in the use of any software, and you are solely responsible for determining whether this SOFTWARE PRODUCT is compatible with your equipment and other software installed on your equipment. You are also solely responsible for the protection of your equipment and backup of your data, and THE PROVIDER will not be liable for any damages you may suffer in connection with using, modifying, or distributing this SOFTWARE PRODUCT -Jorgen Hede -2017 www.hede.org”

# Calculating age from Danish Social security number (CPR Nummer)

This VBA code is designed to calculate the age of a person in an indicated year. The danish social security number (CPR -nummer ) a  relatively complex 12 digit generated number representing the identity of an individual Danish Citizen.

In E.g Excel (TM Microsoft)  the problem about using the 5+6 number in order to figure out a persons age is that the following 4 digits places the age in the 19th,  the 20th century or  in the new millennium.
(for more information on the makeup of the number – please go to https://en.wikipedia.org/wiki/Personal_identification_number_(Denmark).

```Public Function CPRAGE(InCPR As String, InpYear As String)
Dim TAge As String
Dim IAge As Integer
Dim Placing As String
Dim Pla As Integer
Dim FinY As Integer
'converting
'extracting the right integers and converting integers into numbers
'year of birth
IAge = Mid(InCPR, 5, 2)
TAge = CInt(IAge)
'indicator of millenium of birth
PlacingA = Mid(InCPR, 7, 4)
Placing = CInt(PlacingA)
'converting year string into integer
FinY = CInt(InpYear)

If ((Placing >= 4000 And Placing <= 4999) And (TAge >= 0 And TAge <= 36)) Then
Pla = 2000
End If
If ((Placing >= 4000 And Placing <= 4999) And (TAge >= 37 And TAge <= 99)) Then
Pla = 1900
End If
If ((Placing >= 5000 And Placing <= 5999) And (TAge >= 0 And TAge <= 57)) Then
Pla = 1900
End If
If ((Placing >= 1 And Placing <= 999) And (TAge >= 1 And TAge <= 150)) Then
Pla = 1900
End If
If ((Placing >= 1000 And Placing <= 3999) And (TAge >= 1 And TAge <= 150)) Then
Pla = 1900
End If
If ((Placing >= 5000 And Placing <= 8999) And (TAge >= 0 And TAge <= 57)) Then
Pla = 2000
End If
CPRAGE = FinY - (Pla + TAge)
End Function
```

# how to send an Email via Default Email Client Using C#

This code creates an email and  fetches the email address in Form 1 – textBox3.

private void button2_Click(object sender, EventArgs e)
{
String MM;
MM = “Mailto:” + mailadr + “?subject= headlinetext& body=bodytext”;
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo.FileName = MM;
proc.Start();
} # Access sorting in dividable or non dividable

If you need a handy way of identifying dividable or non dividable numbers in a query- this may  well do the trick.

IIf((([kon2]) Mod 2)=0;”dividable”;”not-dividable”)

Enjoy!

# Excel- VBA Script – for adding a missing “0” in Danish Social Security Number

For Danish users of Excel ™ it often happens that when pasting 10 digit social security numbers – an initial “0” may be lost.

Below is a simple solution- this VBA – checks if the number has 9 digits- if so it adds a “0” by using a & in front of the number

Function Addone(cprin As String)
Dim INS As String
If Len(cprin) = 9 Then
INS = “0”& cprin