Excel Formula can't see variable in it -

what have:
1.) dictionary

set dictn= createobject("scripting.dictionary") arr = sheet3.range("d27", "h107").value = 1 ubound(arr)     dictn.item(arr (i, 1)) = cstr(arr (i, 5)) next 

2.) public function in thisworkbook returns value dictionary.

public function dictv(dict object, key string)     if instr(1, key, " flash") > 0 key= left(key, instr(1, key, " flash") - 1)     dictv = dict(key) end function 

3.) realy bad code, part of doesnt seem work

with sheet2         .range("ad2:ad" & numr).formular1c1 = "=test.xls!dictv(" & dictn & ",rc14)"         .range("ad2:ad" & numr) = .range("ad2:ad" & numr).value end 

what get:
1.) formula returns incorrect values, guess because cannot see variables in udf.
suggestions how correct it?
use variable in excel formula didnt me either.
upd:
run-time error '405', not worse

start option explicit , dim statement. latter assigns address in ram, "house" number, , associates name. name easier recognise, vba translate name give number because how finds things in ram. dim statement used "declare" variable.

if omit declarations, vba create variables name them, variants. option explicit option suppresses automatic declaring thing , save becoming bold prematurely.

by method use declare variable determine "scope" or "visibility". declared inside procedure visible within procedure. declarations on module level, not inside procedure, visible throughput module declared, , if declared public visible anywhere in project. here examples.

sub createdict()      dim dictn object     dim arr variant     dim long      set dictn = createobject("scripting.dictionary")     arr = sheet3.range("d27", "h107").value     = 1 ubound(arr)         dictn.item(arr(i, 1)) = cstr(arr(i, 5))     next end sub 

as can see, variable dictn declared within sub. visible code in sub, else. in next example, variable of same name declared outside procedure (preferably @ top of module).

    dim dictn object  sub setdict()      dim arr variant     dim long      set dictn = createobject("scripting.dictionary")     arr = sheet3.range("d27", "h107").value     = 1 ubound(arr)         dictn.item(arr(i, 1)) = cstr(arr(i, 5))     next end sub 

observe variable isn't declared in sub. exists because declared @ top of module, , sub uses it. dictn available throughout module declared, not in other modules. values assigned procedure setdict stay in dictionary (object) after sub has run. in fact, sub used load it.

in next example, dictionary returned function.

private function dict() object      dim fun object                   ' function return value     dim arr() variant     dim long      set fun = createobject("scripting.dictionary")     arr = sheet3.range("d27", "h107").value      = 1 ubound(arr)         fun.item(arr(i, 1)) = cstr(arr(i, 5))     next      set dict = fun end function 

the function private, meaning visible within module resides. if want call other modules must declare public. vba's default public. so, if omit word private public default.

you can write code mystring = dictn(key) anywhere on module provided module level variable dictn exists. can write code mystring = dict(key), anywhere on module function dict exists. difference function create dictionary on each call while variable dictn loaded once , being reused.

note, if have examples 1 , 2 in same module code fail compile because object dictn declared both @ procedure level , @ module level. can use name once. (bear in mind names used vba itself.)

your code (given without declarations) seems have structure of example #1. dictn declared @ procedure level , therefore not available in procedure. except, of course, may not have declared @ all, trusting vba job you. did. created name @ procedure level when first used , procedure level variable created. isn't vba's fault expect available elsewhere too. :-)