PDA

View Full Version : Solved: User Defined Function



Slicemahn
01-07-2008, 02:14 PM
Hello Everyone!

I am trying to use this UDF in order to create a unique key that will upload to a database table.

I keep getting a Run-time error 5 Invalid call or argument error.

The function will look at a cell1, look at all the characters following the ":" and then by the SELECT CASE statements find the corresponding number that would be the first portion of the string.

Cell1 & Cell2 are then put together (concatenated) in order to yield the unique ID.
Public Function CreatePeopleID(cell1 As Variant, Cell2 As Variant) As Variant
Dim cellholder As Variant
Select Case Mid(cell1, InStr(cell1, ":"), Len(cell1) - InStr(cell1, ":"))
Case Bloor
cellholder = 1
Case "Erin Mills"
cellholder = 2
Case Burnaby
cellholder = 3
Case Calgary
cellholder = 4
Case Kitchener
cellholder = 5
Case Moncton
cellholder = 6
Case Montreal
cellholder = 7
Case Ottawa
cellholder = 8
Case "Richmond Hill"
cellholder = 9

End Select

CreatePeopleID = cellholder & Cell2.Value

End Function

Bob Phillips
01-07-2008, 02:35 PM
Public Function CreatePeopleID(cell1 As Variant, Cell2 As Variant) As Variant
Dim cellholder As Variant
Select Case Mid(cell1, InStr(cell1, ":") + 1, Len(cell1) - InStr(cell1, ":"))
Case "Bloor": cellholder = 1
Case "Erin Mills": cellholder = 2
Case "Burnaby": cellholder = 3
Case "Calgary": cellholder = 4
Case "Kitchener": cellholder = 5
Case "Moncton": cellholder = 6
Case "Montreal": cellholder = 7
Case "Ottawa": cellholder = 8
Case "Richmond Hill": cellholder = 9
End Select

CreatePeopleID = cellholder & Cell2

End Function

rlv
01-07-2008, 10:57 PM
I find that it makes a UDF easier to debug if I add a few intermediate variables.

Public Function CreatePeopleID(Cell1 As Variant, Cell2 As Variant) As Variant
Dim CellHolder As Variant
Dim SPos As Long, SLen As Long, SStr As Variant

Cell1 = Trim(Cell1)
SLen = Len(Cell1)
SPos = InStr(Cell1, ":")
SStr = UCase(Mid(Cell1, SPos + 1, SLen - SPos))

Select Case SStr
Case "BLOOR"
CellHolder = 1
Case "ERIN MILLS"
CellHolder = 2
Case "BURNABY"
CellHolder = 3
Case "CALGARY"
CellHolder = 4
Case "KITCHENER"
CellHolder = 5
Case "MONCTON"
CellHolder = 6
Case "MONTREAL"
CellHolder = 7
Case "OTTAWA"
CellHolder = 8
Case "RICHMOND HILL"
CellHolder = 9
End Select

CreatePeopleID = CellHolder & Cell2

End Function

mikerickson
01-07-2008, 11:07 PM
CellHolder = Application.Match(SStr, _
Array("BLOOR", "ERIN MILLS", "BURNABY", "CALGARY", "KITCHENER", "MONCTON", _
"MONTREAL", "OTTAWA", "RICHMOND HILL"), 0)

Bob Phillips
01-10-2008, 02:33 AM
If you are using Application.Match, you should add error handling, else it collapses in a heap.

Slicemahn
01-11-2008, 05:12 AM
Thanks guys. This is solid. I like the intermediate variables idea.