International Dialing Codes (VBA (Excel))

Topic

New Reply

Does anybody know how to convert an international dialing code into the name of the country it refers to? This sounds easy but I can’t figure it out. I’m sure it is a combination of the first 1, 2, 3 or 4 digits (excluding the international access code) and the length of the number.) An example would be a number like 007841291234 which is a number in Penza Russia. The code 7 after the 00 is Russia. However, the code 78 after the 00 can also be interpreted as Kazakhstan.

I have looked around and there are plenty of sites where you can enter a telephone number and gat back the contry it refers to but nowhere can I find am explanation of the algorithm.

Anybody know?

Cheers,
Kevin Bell

Reply | Quote

Replies

I don’t think there *is* an algorithm. Country code 1 is used by over 20 countries including the USA and Canada, and 7 is used by Russia and Kazakhstan. All other country codes are unique to a country, as far as I know. See List of ITU-T Recommendation E.164 Assigned Country Codes (Position on 1 May 2005).
You’d have to get hold of a list of area codes for country codes 1 and 7 to know to which country a phone number belongs.

Reply | Quote

Thanks Hans and Stuart. I have a file containing country and area codes that I have used to construct a lookup table. I have written a Function to search through it starting with the first 7 (The longest country code + area code is 7 characters) significant characters of the phone number and working backwards. I’ve attached the file in case anybody is interested.

The problem with this approach when using Excel is that with the resulting table containing over 8000 rows the function is SLOOOOOOWWWWWWWWW.

Anyway, onward and upward.

Cheers,
Kevin

By the way, how do you call VLOOKUP from within a VBA function? When I tried it the compiler gave me Unknown Function or Sub.

Reply | Quote

The VBA way to call (some) worksheet functions is Application.WorksheetFunction.FunctionName. The arguments must be specified as usual in VBA, not as in worksheets.

For example:

MyVar = Application.WorksheetFunction.VLookup(Range(“A1”), Range(“D1:G100”), 4, True)

Reply | Quote

An additional point about worksheet functions in VBA – if you use:
MyVar = Application.WorksheetFunction.VLookup(Range("A1"), Range("D1:G100"), 4, True)
and the function returns an error, you will get a run-time error. However, if you use:
MyVar = Application.VLookup(Range("A1"), Range("D1:G100"), 4, True)

where MyVar is a variant, you can then just use:
If IsError(MyVar) Then
type syntax since you get an error returned to the variable rather than a run-time error raised.
FWIW.

Reply | Quote

Once upon a time, many years ago, I was involved in investigating a performance problem for a telephone billing system for one of the multinational Telcos.

The root cause turned out to be the lack of an algorithm for identifying the local exchange from a UK phone number. The number of digits you have to lookup is not defined so the algorithm they used was to look for the whole number in a lookup table, then recursively remove the last digit and look again! This was very slow if you had to look up millions of numbers per hour! We achieved significant performance improvement by replacing the lookup table with a binary tree – but it still wasn’t very fast.

StuartR

Reply | Quote

Kevin

I needed a bit of practice for an upcoming project, so here is my solution. It’s too big to attach, so you can grab it here – Dialling.zip.

The Construct_Sheets module generates two worksheets from the text file you supplied. First run FileToCols() then ColsToRows(). This is harder than it ought to be because XL is (still!) limited to its legendary 256 columns. This has already been done in the workbook.

The Lookup userform works by you typing in the start of the phone number. It resolves as you type, first finding the country code and displaying the country, then finds the area code and displays that. e.g. As I type 613, the display progressively shows:

6
61 Australia,
613 Australia, Central East Australia

The userform is very basic, so you could probably improve.

Alan

Reply | Quote