Language: Deutsch English















Last Update: 2017 - 07 - 09





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



  • 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

    Subscribe to my newsletter

    *

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



    © 1999 - 2017 by Philipp Stiefel