Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    International Dialing Codes (VBA (Excel))

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: International Dialing Codes (VBA (Excel))

    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.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: International Dialing Codes (VBA (Excel))

    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

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: International Dialing Codes (VBA (Excel))

    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.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: International Dialing Codes (VBA (Excel))

    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)

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: International Dialing Codes (VBA (Excel))

    An additional point about worksheet functions in VBA - if you use:
    <code>MyVar = Application.WorksheetFunction.VLookup(Range("A1"), Range("D1:G100"), 4, True)</code>
    and the function returns an error, you will get a run-time error. However, if you use:
    <code>MyVar = Application.VLookup(Range("A1"), Range("D1:G100"), 4, True)
    </code>
    where <code>MyVar</code> is a variant, you can then just use:
    <code>If IsError(MyVar) Then</code>
    type syntax since you get an error returned to the variable rather than a run-time error raised.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: International Dialing Codes (VBA (Excel))

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •