How to pass cell reference to VBA function?

[alan.phillipson]

Hi, I have the following UDF and I wish to put this in column 3 of a
sheet for a 100 rows, but I can't get it to work. Any ideas?

Function quizzer(txt As String)
With CreateObject("VBScript.RegExp")
.Pattern = "([a-zA-Z]+\.)?\d+"
quizzer = .Execute(txt)(0)
End With
End Function

I have tried the following, but I can not get the cell reference to
change.

Sub test()

For x = 1 To 100
r = Range("c65536").End(xlUp).Row + 1
frml = "A" & r
Cells(r, 3).Formula = "=quizzer(frml)"
r = r + 1
Next x
End Sub

 

[Peter T]

This UDF worked for me for cell-ref or quoted string values -

Public goRegExp As Object ' set to nothing in say a deactivate event

Function quizzer(txt As Variant)
Dim oMatches As Object
If goRegExp Is Nothing Then
Set goRegExp = CreateObject("VBScript.RegExp")
End If
On Error GoTo errH
With goRegExp
.Pattern = "([a-zA-Z]+\.)?\d+"
Set oMatches = .Execute(txt)

If oMatches.Count Then
quizzer = oMatches(0)
Else
quizzer = "?"
End If
End With
Exit Function
errH:
quizzer = CVErr(xlErrNA)
End Function

Sub test()
s$ = "abc123"
MsgBox quizzer(s)
End Sub

That CreateObject in a 100 cells would be slow, hence the global ref to
RegExp

Regards,
Peter T

 

[alan.phillipson]

This UDF worked for me for cell-ref or quoted string values -

Public goRegExp As Object ' set to nothing in say a deactivate event

Function quizzer(txt As Variant)
Dim oMatches As Object
If goRegExp Is Nothing Then
Set goRegExp = CreateObject("VBScript.RegExp")
End If
On Error GoTo errH
With goRegExp
.Pattern = "([a-zA-Z]+\.)?\d+"
Set oMatches = .Execute(txt)

If oMatches.Count Then
quizzer = oMatches(0)
Else
quizzer = "?"
End If
End With
Exit Function
errH:
quizzer = CVErr(xlErrNA)
End Function

Sub test()
s$ = "abc123"
MsgBox quizzer(s)
End Sub

That CreateObject in a 100 cells would be slow, hence the global ref to
RegExp

Regards,
Peter T

Thanks for the reply.

Public goRegExp As Object ' set to nothing in say a deactivate
event ????

What do I do with this, please remember you are speaking to an idiot.

 

[Peter T]

This UDF worked for me for cell-ref or quoted string values -

Public goRegExp As Object ' set to nothing in say a deactivate event

Function quizzer(txt As Variant)
Dim oMatches As Object
If goRegExp Is Nothing Then
Set goRegExp = CreateObject("VBScript.RegExp")
End If
On Error GoTo errH
With goRegExp
.Pattern = "([a-zA-Z]+\.)?\d+"
Set oMatches = .Execute(txt)

If oMatches.Count Then
quizzer = oMatches(0)
Else
quizzer = "?"
End If
End With
Exit Function
errH:
quizzer = CVErr(xlErrNA)
End Function

Sub test()
s$ = "abc123"
MsgBox quizzer(s)
End Sub

That CreateObject in a 100 cells would be slow, hence the global ref to
RegExp

Regards,
Peter T

Thanks for the reply.

Public goRegExp As Object ' set to nothing in say a deactivate
event ????

What do I do with this, please remember you are speaking to an idiot.

It's probably harmless to do nothing, but to clean up the object reference
when not needed there are various events in which you could destroy it, eg

In the ThisWorkbook module -
Private Sub Workbook_Deactivate()
Set goRegExp = Nothing
End Sub

Whilst there look at the two drop downs, there are more events in the Sheet
modules.

Did the example work.

Regards,
Peter T