A Reverse Find function for Excel

Sometimes you need to find the last instance of a string found in another string, you might need the integer that represents the character number of the beginning of that string.   This function is a great example of using a reverse for (step -1).  Check out the code below:

Public Function revfind(findwhat As String, infield As String) As Integer
revfind = 0
For i = Len(infield) - Len(findwhat) To 1 Step -1
    Debug.Print Mid(infield, i, Len(findwhat))
    If Mid(infield, i, Len(findwhat)) = findwhat Then
        revfind = i
        Exit For
    End If
Next
End Function

'You need to test this function, here is an implementation

Public Sub testrevfind()
Dim fw As String
Dim infield As String
fw = "-"
infield = "test-text-need-to-find-last-word"
t = revfind(fw, infield)
Debug.Print t
Debug.Print Left(infield, t - 1)
End Sub

'You should get this 

' 28 

' test-text-need-to-find-last

The Mysterious ListObjects and VBA

List objects are really cool, they provide the ability to load these objects on Excel from data types outside your spreadsheet, like Text Files, Comma Delimited, XML, HTML, JSON, Direct Database Connections, websites, and other Excel files.    The ListObjects when created have a name and can be referenced on the spread sheet by just using a vlookup formula on that object.   They also look nice and have built in template color schemes, plus the added benefit of an auto query feature, nothing gets out of order and it's very professional.  The question in my simple mind is... how can I get to this object to muck about with it in my program.    How can I load these tables, and then use them as a source for form objects, like pull downs and radio buttons?

here are some great sources of information:
Microsoft Excel Documentation  - General Microsoft documentation, no good examples
Spreadsheet Guru - Fantastic examples of code
CodeVBA
https://stackoverflow.com/questions/49625994/excel-listobject-table-listrows-count-vs-range-rows-count
https://stackoverflow.com/questions/50076973/vba-populate-listbox-from-multiple-listobjects


Private Sub Load_bttn_Click()
'load productmfg table
    Dim lo As ListObject
    Dim i As ListRow
    Set lo = ActiveWorkbook.Worksheets("cat_ref").ListObjects("Vendor_List")
    Me.product_mfg_list.Clear
    For Each i In lo.ListRows
        Me.product_mfg_list.AddItem i.Range.Formula
    Next
   
End Sub


Private Sub product_mfg_list_Change()
Dim s As String
Dim r As ListRow
    s = product_mfg_list.value
    GetRange("Output_ProductManufacturer").value = s  ' Me.Range("L28").Value
    Me.sub_product_list.Clear
    For Each r In ActiveWorkbook.Sheets("cat_ref").ListObjects("Vendor_Ref_Table").ListRows
        If r.Range(1, 1).value = s Then
            Me.sub_product_list.AddItem r.Range(1, 2)
        End If
    Next
End Sub

Exporting the current sheet as a CSV File Macro

The process of saving a workbook and then exporting a sheet as one name and then exporting the next sheet as a different name is time consuming.   I created a quick macro which I tied to a menu on my "Quick Access Toolbar" that does just that.

'==============Start of Code =======================
'Exports the Current worksheet
Public Sub export_worksheet()
Dim sh As Worksheet
Set sh = Excel.ActiveSheet
Dim fn As String
fn = "\" & sh.Name & ".csv" 'uses the worksheetname as the filename
Write_Sheet fn, sh.Name
End Sub

'-----------------------------
' This will write a worksheet to a CSV
'-----------------------------
Public Sub Write_Sheet(file_name As String, sheet_name As String)
Dim epath As String
epath = Excel.Workbooks(1).Path & file_name
Dim fso As Scripting.FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
Dim ofile As Scripting.TextStream
Set ofile = fso.CreateTextFile(epath)

Dim wks As Worksheet
Set wks = Worksheets(sheet_name)

Dim mc As Integer
Dim mr As Integer
mc = wks.Range("A1").End(xlToRight).Column  'This only works if there are no blanks to the right
mr = wks.Range("A1").End(xlDown).Row     'This only works if the data set is consistent down

For lr = 1 To mr
    For lc = 1 To mc
        If lc = mc Then
            ofile.WriteLine """" & wks.Cells(lr, lc).Value & """"
        Else
            ofile.Write """" & wks.Cells(lr, lc).Value & ""","
        End If
    Next
Next
 
'finish close all connections
ofile.Close
Set fso = Nothing
Set ofile = Nothing
Exit Sub
'if error handler
handler:

End Sub
'========================== End of Code =====================

This code uses the scripting .filesystemobject and writes out a text stream to a file while adding the commas and the quotes.   It's a very basic script, there can be a lot done to improve it.

Turning Inches into Feet and Inches -- even fractions of an inch...

Turning inches into feet is simple math... but why not have a function that does it and the output looks better than if I did it by hand.   Given an integer that represents inches this little Excel VBA function will output feet and inches.

=====================code below===============================
Public Function IntoFeet(ininches As Double) As String
Dim feet As Integer
Dim inches As Double
    feet = Int(ininches / 12)
    inches = ininches - (feet * 12)
    IntoFeet = feet & "' " & Trim(Excel.WorksheetFunction.Text(inches, "#-?/?")) & """" 'formats thte output including fractions
End Function
=================================================================


I'm sure there are a lot of things I can do to improve this function, but if you have some ideas, drop me a comment!

Thanks-

Super Tools for Learning Regular Expression

Today I'm not going to give you any function code, rather I'm going to give you a list of resources.  Places I've collected that are a good reference for learning and using regular expression.


Sites on Regular Expressions
regular-experssions.info 
RegExp


User Guides
Wikipedia Overview - Good Starting place to learn anything
Microsoft on Regular Expression
Grymoire Navigation
Oracle Java on Regular Expression
zytrax Regular Expression User Guide

Guide to Regular Expression
Excel and Regular Expression
Mozilla and Regular Expressions Mostly Java but the principals are the same


Learning
code project 30-minute tutorial
W3C Schools  - They teach it in Java, but the most of the principles apply to Excel and VBA



Tools for Generating RE
The best Regular Expression Generating tool online - I love this tool,  put in the text expression - and it will help you break it down into it's parts, while generating the code!   Very cool!


Testing your Regular Expression:
https://regex101.com/
Desinger 215 RegExp Tester
RegEx Evaluator

Find Using Regular Expression in Excel - New Fuction

I've found that regular expression is one of the most useful tools in data manipulation. Being able to pass a regular expression rule and return the first instance of that expression is a powerful function.    I'm not going to get into depth on how regular expression works, there are tons of great resources on line that can help you learn how to write the expressions.    I might to into some depth later on other tools I use online, but here I'm just going to give you the VBA function that will expose the power of regular expression into your excel spreadsheet.  

Here is the code:
'-----------------------------------------------FRE---------------------------------------
Public Function FRE(StringIn As String, strPattern As String) As String
  Dim r As RegExp
    Set r = New RegExp
      With r
            .Global = True
            .MultiLine = False
            .IgnoreCase = True
            .Pattern = strPattern
        End With
   Set m = r.Execute(StringIn)
   FRE = Trim(m(0))
End Function
'------------------------------------------------------------------------------------------------

I named this function FRE - simply because I use it a ton, and didn't want to type some totally expressive name, - FRE is Find using Regular Expression.

There are two parts - the Stringin - which is the string your searching in, and strPattern which is the regular Expression Pattern.   If this function finds anything, it's only going to return the first instance.   I'm sure we can write better functions for returning arrays, making ranges, etc.   This is just dirt simple. 

Here is an example of how you could use this function -  you have a cell that has a lot of text mixed with phone numbers, but you just want to return the phone numbers from that cell, this function will return the first phone number.

Lets Say A1 = "Joe Smith (456) 456-8456 josmith@google.com"
to return the phone number, the function would look like this FRE(A1,"(\(\d{3}\).*\d{3}.*\d{4})")

And the return would be - (456) 456-8456


Super Simple.    I hope this will help somebody out there!

Join Function for mutlple cells

I've found that joining a range of cells together into a single delimited field is really useful.  Excel in the Data tab has the ability to take delimited text in a single column and move it into multiple columns, but this little function goes the other way.

Here is the code:
===========================================================
Public Function Jointext(r As Range, del As String) As String

Dim b As String
For a = 1 To r.Count
    If IsEmpty(r.Value2(1, a)) = False Then
    b = b & r.Value2(1, a) & del
    End If
Next
Jointext = Left(b, Len(b) - Len(del))
End Function
==============================================================

It's very simple,  I'm iterating a range array and putting the values into a single string delimited by the del string.  Then after that, I'm removing the last delimiter.

Simple