Worksheet function which calls VBA TOO slow!

[alibongo]

Hello,

I have a Workbook which consists of a monthly worksheets of different types.

The Worksheets are loaded from a user form and when they are created they are named e.g. "mscOctober".

From the data which is in these sheets, pivot tables are created, as well as named ranges on those pivot tables.

The named ranges are "mscOctober_element" and "mscOctober_day".

OK, now here is the problem. Each of the data elements I load are in the form CKP.msc.org.cdrs or CKP.cabbages.oranges.apples for that matter.
CKP stands for checkpoint.

As I have to reconcile many different 'checkpoints' and this may change over time.

I have decided that the user should enter the checkpoint names themselves and I therfore need a formula that populates the rest of the sheet with data so I can run trend analysis etc...

I have devised a way of working this using the following VBA function, which extracts "msc", where A1 is the month(could be any month).

Function getStrItem(strData, iPosition, strDelim) As String

'Return the data in position iPosition of the string
'where the delimiter between entries is strDelim

Dim iLength As Integer
Dim iItemNumber As Integer
Dim iItemStart As Integer
Dim strItem As String
Dim iDelimLength As Integer
Dim i As Integer

Application.Calculation = xlCalculationManual

'Get the length of the string
iLength = Len(strData)
iDelimLength = Len(strDelim)

'Initialise counters and return string
strItem = ""
iItemNumber = 1
i = 1

Do While i <= iLength And iItemNumber <= iPosition

If iItemNumber = iPosition Then
strItem = strItem & Mid(strData, i, 1)
End If

i = i + 1

If Mid(strData, i, iDelimLength) = strDelim Then
iItemNumber = iItemNumber + 1
i = i + iDelimLength 'Move past the delimiter
End If

Loop

getStrItem = strItem

Application.Calculation = xlCalculationAutomatic

End Function 'getStrItem

The worksheet formula is:
=INDIRECT(ADDRESS(MATCH($A5,INDIRECT(getstrItem($A5,2,&quot;.&quot & $A$1 & &quot;_element&quot,0),8+MATCH(C$3,INDIRECT(getstrItem($A5,2,&quot;.&quot & $A$1 & &quot;_day&quot,0),4,TRUE,getstrItem($A5,2,&quot;.&quot&$A$1))

I know this is really ugly.
This works fine but if I want to create more sheets with pivots it takes ages for excel to run through my getstrItem function.

Does anybody know any way of doing this quickly or in a more efficient manner. Iam sure there must be a way but I can't find a worksheet function which does the same as my getstrItem VBA.

Thanks for any help and let me know if you need clarification on this tricky problem?

Regards, Alistair