ModNames Function

This function produces as array of Module names that match Sheet names.

See our White Paper concerning the ‘Dynamic Icon Bar’

Function ModNames() As String()

' Function written by Instant Pages Ltd (2002)

' Visit us at www.instantpages.ltd.uk

' This credit MUST stay intact for use

Dim strModArr() As String

Dim strVBCArr() As String

Dim strShtArr() As String

Dim intVBCcnt As Integer

Dim intVBDcnt As Integer

Dim intShtcnt As Integer

Dim intModcnt As Integer

Dim intLC1 As Integer

Dim intLC2 As Integer

‘ Establish number of modules in Project

intVBCcnt = ThisWorkbook.VBProject.VBComponents.Count

' Build as array of module names

intVBDcnt = 0

For intLC1 = 1 To intVBCcnt

If ThisWorkbook.VBProject.VBComponents(intLC1).Type = vbext_ct_StdModule Then

intVBDcnt = intVBDcnt + 1

ReDim Preserve strVBCArr(intVBDcnt)

strVBCArr(intVBDcnt) = Right(ThisWorkbook.VBProject.VBComponents(intLC1).Name, 

Len(ThisWorkbook.VBProject.VBComponents(intLC1).Name) - 3)

End If

Next intLC1

' Build an array of sheet names

intShtcnt = ThisWorkbook.Sheets.Count

ReDim strShtArr(1 To intShtcnt)

For intLC1 = 1 To intShtcnt

strShtArr(intLC1) = ThisWorkbook.Sheets(intLC1).Name

Next intLC1

' Build an array of module names that have sheet name equivalents

intModcnt = 0

For intLC1 = 1 To intShtcnt

For intLC2 = 1 To intVBDcnt

If strShtArr(intLC1) = strVBCArr(intLC2) Then

intModcnt = intModcnt + 1

ReDim Preserve strModArr(1 To intModcnt)

strModArr(intModcnt) = "bas" & strShtArr(intLC1)

End If

Next

Next

‘ Return array of module names

ModNames = strModArr()

End Function