Count number of occurrence in a string using Excel VBA.

By -
The following code snippet allows you to get the number of text occurrences in a string using VBA in Excel.

Function StringCountOccurrences(strText As String, strFind As String, _
                                Optional lngCompare As VbCompareMethod) As Long

Dim lngPos As Long
Dim lngTemp As Long
Dim lngCount As Long

    If Len(strText) = 0 Then Exit Function
    If Len(strFind) = 0 Then Exit Function
    lngPos = 1

    Do
        lngPos = InStr(lngPos, strText, strFind, lngCompare)
        lngTemp = lngPos
        If lngPos > 0 Then

            lngCount = lngCount + 1
            lngPos = lngPos + Len(strFind)

        End If

    Loop Until lngPos = 0
    StringCountOccurrences = lngCount

End Function

Comments

Post a Comment

Popular posts from this blog

Send Email with Excel VBA via CDO through GMail

By -
Image
If you're working on a project or having a numerous reports in excel to be sent out to your boss or clients. And what you usually do is save the workbook, compose a new email, copy the contents or attach it on your email client. That's a time consuming task! What we wanted to do is automate the tasks from within the Excel Workbook you're working with. The SendEmail() Function below will do the task for you. Function Definition: Function SendEmail(ByVal Username As String, _                    ByVal Password As String, _                    ByVal ToAddress As String, _                    ByVal Subject As String, _                    ByVal HTMLMessage As String, _                    ByVal SMTPServer As String, _       ...
Read More »

How to Create a Configuration.INI Files in VB6

By -
Image
In this example shows only a simple form that saves and retrieves value from the CONFIG.INI file. After reading this, you can use your own creativity on how you're going to implement this on your project. To start with, On your Form add the following controls and arrange them according to the image above. (1) TextBox - Change the Name property to "txtSetting" (2) Command Button - Change the Caption and Name Properties to the following:                                        Save:          Change the Name property to "cmdSave"                                        Retrieve:    Change the Name property to "cmdRetrieve" (5) Label - For the two labels we'll be using to retrieve the values. Remove the default caption and change...
Read More »

Customized Progress Bar in VB6 using Image Control

By -
Image
In order to implement a progress bar in VB6 you need to enable the component " Microsoft Windows Common Controls #.# " before you can add this control on your form. In this example, you won't need any extra Controls that will require another libraries when running the app on another system. What you will need is a standard Label, Image, Timer controls and the Sleep API declaration to implement a smooth progress of the Progress Bar. CODE: Option Explicit ' ################################################################# ' # By: Cromwell Bayon (omelsoft@gmail.com) ' # Description: Progress Bar Using Custom Image ' # Date: Friday 12th of April, 2013 ' ################################################################# ' DECLARE PROGRESS BAR VARIABLES Public iMax As Long, _ iMin As Long, _ u_Val As Long, _ xMinVal As Long, _ xMaxVal As Long, _ xPBar As Long Priv...
Read More »