Language: Deutsch English















Last Update: 2021 - 03 - 21





Visual Basic for Applications

Table of Contents

  • Split phone number
  • Numbers only
  • VBA - Handling events from an indefinite number of event sources
  • The TaskbarList COM Component. - Using the raw COM API with VBA
  • How to convert string pointers to VBA Strings
  • 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
  • The Date/Time Data Type in VBA and Access
  • VBA Navigation History - Add-In
  • VBA-(SQL)-String Tutorial
  • The ParamArray Keyword in VBA / How to emulate the SQL Coalesce Function
  • A String.Format / printf implementation in VBA
  • How to format Date/Time for a specific language and country
  • VBA-Bug introduced with the latest (2019-08) Windows Update
  • VBA Class Module to access the clipboard
  • Recursion in VBA – Example: Listing the Outlook Folder Hierarchy



  • 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

    VBA - Handling events from an indefinite number of event sources

    Inspired by a question on a forum I wrote an extensive article on how to handle events from a whole collection of objects in VBA. The article is much(!) to long for this page, so please follow the link to read it.



    Back to the TOC

    The TaskbarList COM Component. - Using the raw COM API with VBA

    An rather innocent question about how to remove the taskbar button of the Access application windows from the taskbar inspired me to delve deep into the Windows COM+ API. In this article I describe how to use the TaskbarList component with the raw Windows COM API to achieve this goal.



    Back to the TOC

    How to convert string pointers to VBA Strings

    You will have to deal with string pointers in VBA only very rarely but if you ever do, you might run into some problems. The solutions are pretty straight forward, but maybe not so much, if you have to figure them out on your own. So, I wrote an in-depth text explaining how to convert a string pointers to VBA Strings.



    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 information and resources in this article you will be able to adapt your API-Declarations to 64-bit..



    Back to the TOC

    The Date/Time Data Type in VBA and Access

    While dates in Access and VBA seem to be simple and straightforward at first sight, there are actually quite a few pitfalls connected to handling dates. I provide comprehensive background information and give you some guidance to avoid common problems when working with dates in Access and VBA.



    Back to the TOC

    VBA Navigation History - Add-In

    I thought, it should be possible to create a navigation history experience in VBA Editor similar to Visual Studio. - Here we are. VBA Navigation History is finally available to the public.

    Are you annoyed by the Last Position command in the VBA Editor too? - Then VBA Navigation History is for you!



    Back to the TOC

    VBA-(SQL)-String Tutorial

    While developing applications with Microsoft Access you will sooner or later be confronted with the problem to work with string variables in VBA. Frequently, it is necessary to concatenate different partial strings with variables and the return values of functions. Frequently the strings in questions are SQL statements to executed to query data inside your database. There are a lot of pitfalls and hard to understand details for beginners.

    To make getting started with this whole topic easier for you, I created a video and an extensive text tutorial on how to work with SQL strings in VBA.



    Back to the TOC

    The ParamArray Keyword in VBA / How to emulate the SQL Coalesce Function

    The SQL Coalesce Function is a function you can pass any number of arguments to. This function can be emulated in VBA only using the not well known ParamArray Keyword. This article will explain the ParamArray and show an implementation of the Coalesce Function.



    Back to the TOC

    A String.Format / printf implementation in VBA

    The String.Format method from .Net or the printf function from C are further examples of procedures that can only be emulated in VBA using the not very well known ParamArray Keyword. This article will show how useful String.Format / printf is.



    Back to the TOC

    How to format Date/Time for a specific language and country

    In general I strongly advise to just use generic Access/VBA named formats for date formatting. However, when you need to format a date according to the rules of a specific target locale (language and country) this text will help you.



    Back to the TOC

    VBA-Bug introduced with the latest (2019-08) Windows Update

    I usually recommend keeping your Windows and Office installations up to date by installing the updates provided by Microsoft in a timely manner. Not this time though! If you install the latest 2019-08 Windows Update, you might receive an Invalid Procedure Call Error after installing the 2019-08 update.



    Back to the TOC

    VBA Class Module to access the clipboard

    There are many samples available shwoing how to retrieve text from the clipboard using VBA. However, there was none to find able to retrieve the content of a text file from the clipboard. So, I created a small VBA Class Module to access the clipboard that is able to do that.



    Back to the TOC

    Recursion in VBA – Example: Listing the Outlook Folder Hierarchy

    Recursion is a powerful programming technique. In this article I explain the general, basic concept of recursion and show how recursion is implemented in VBA by the example of reading the Microsoft Outlook folder hierarchy. The code was written in Microsoft Access, but it will work all the same in any VBA enabled application.



    Back to the TOC

    Subscribe to my newsletter

    *

    I will never share your email with anyone. You can unsubscribe any time.
    This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.



    © 1999 - 2021 by Philipp Stiefel - Privacy Policiy