Language: Deutsch English

Last Update: 2017 - 09 - 05

Visual Basic for Applications

Table of Contents

  • Split phone number
  • Numbers only
  • Create ZIP-Archives with the Shell32 library
  • Introduction to Classes and Objects in VBA
  • The 'Better VBA' video series
  • How to convert Windows API declarations in VBA for 64-bit

  • Split phone number

    With these functions you can split a phone number, which is passed into the function in the format CountryCode(AreaCode)SubscriberNumber (Example: +49(069)555666), into its parts.

    Each of these functions needs the full number as argument and will return the extracted part of the number.

    Function SubscriberNumber(ByVal fullPhoneNumber As String) SubscriberNumber = Trim$(Right$(fullPhoneNumber, _ Len(fullPhoneNumber) - InStr(1, fullPhoneNumber, ")"))) End Function Function AreaCode(ByVal fullPhoneNumber As String) AreaCode = Trim$(Mid$(fullPhoneNumber, _ InStr(1, fullPhoneNumber, "(") + 1, _ InStr(1, fullPhoneNumber, ")") _ - InStr(1, fullPhoneNumber, "(") - 1)) End Function Function CountryCode(ByVal fullPhoneNumber As String) CountryCode = Trim$(Left$(fullPhoneNumber, _ InStr(1, fullPhoneNumber, "(") - 1)) End Function

    Back to the TOC

    Numbers only

    To filter/search effectively for all kinds of numbers that are entered with formatting (e.g. phone numbers) in a database table, it might be a sensible approach to remove everything from the entered number except the pure numerals.

    That is what this function does. It uses the ASCII codes of the digits 0 to 9 to strip everything else from the formatted number.

    Function NumbersOnly(strInput As String) As String Dim x As Integer Dim strNumber As String For x = 1 To Len(strInput) If (Asc(Mid(strInput, x, 1)) > 47 And Asc(Mid(strInput, x, 1)) <= 57) Then strNumber = strNumber & Mid(strInput, x, 1) End If Next NumbersOnly = strNumber End Function

    Back to the TOC

    Create ZIP-Archives with the Shell32 library

    A very long and extensive article on how to create a ZIP-Archive with VBA and the Shell32-Library included with Windows. The article covers extractig files from the archive as well and features working sample code, you can just copy and paste to your own application.

    Back to the TOC

    Introduction to Classes and Objects in VBA

    Advance your VBA programming skills with this introductory guide to Objects and Class Modules in the VBA programming language. Learn the basics about object oriented programming in VBA.

    Back to the TOC

    The 'Better VBA' video series

    I publish a video series on YouTube showing methods I consider useful to write better VBA code.

    Do you want to write better VBA code?

    Join me on my way to explore, study, learn and, of course, write better VBA code!


    Back to the TOC

    How to convert Windows API declarations in VBA for 64-bit

    Converting your API Declarations to 64-bit sounds more daunting than it actually is. With the content covered in this article you will be able to adapt your API-Declarations to 64-bit..

    Back to the TOC

    Subscribe to my newsletter


    I will never share your email with anyone. You can unsubscribe any time.

    © 1999 - 2017 by Philipp Stiefel