vba - How to use a static member in UDF that is called by query? -

vba - How to use a static member in UDF that is called by query? -

i have query 1 of values returned udf:

select name,coord,convertcoord(coord) testtable;

convertcoord() uses regex , matchcollection objects homecoming value:

dim re new regexp dim mtch match dim matches matchcollection function convertcoord(str string) string re.pattern = "(find|this)pattern" set matches = re.execute(str) if matches.count > 0 set mtch = matches(1) convertcoord = mtch.value else convertcoord = "" end if end function

i'm trying speed query, , i'm wondering if there's way create 1 instance of re,mtch, , matches can referred every phone call convertcoord(). if understand correctly, every result row in query calls convertcoord(), constructs , destructs objects repeatedly, , object creation slows downwards query.

or static, , hence constructed once, because i've declared them outside of function?

you can utilize static key word when declare regexp. however, can utilize within procedure (function or subroutine). if seek utilize module-level variable, triggers compiler error.

i don't think need declare mtch , matches static because don't want preserve values 1 function phone call next. don't see why should module-level variables, made them local function.

class="lang-vb prettyprint-override">function convertcoord(str string) string static re regexp dim mtch match dim matches matchcollection if re nil debug.print "regexp nothing" set re = new regexp re.pattern = "(find|this)pattern" else debug.print "regexp active" end if ' insert code uses regexp end function

test similar function query. after confirm prints "regexp nothing" no more once, you'll want discard debug.print statements. :-)

vba ms-access static-members