How to pass cell reference to VBA function?
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 |
How to pass cell reference to VBA function?
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 wrote in message oups.com... 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 |
How to pass cell reference to VBA function?
On 9 Mar, 12:41, "Peter T" <peter_t@discussions wrote:
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. |
How to pass cell reference to VBA function?
wrote in message
ups.com... On 9 Mar, 12:41, "Peter T" <peter_t@discussions wrote: 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 |
All times are GMT +1. The time now is 04:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com