GetRef not working if function is loaded at runtime using LoadFunctionLibrary

Question

I am trying to execute a function from a functional library (which I am loading at runtime). Everything works if I use Eval or Execute to run the function but I wanted to use GetRef as mentioned in this and this

QTP Code Snippet

Call LoadFunctionLibrary("/../Libraries/Tests.vbs")
Set objCon = CreateObject("ADODB.Connection")
objCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & "C:\_Work\WorkingFolder\TestList.xlsx" & "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1';"
Set objRS = objCon.Execute("Select * from [Sheet1$] Where [To Execute] = 'YES'")
Do While Not objRS.EOF
     'Eval( objRS.Fields(1).Value)
     'Execute objRS.Fields(1).Value

     Set tcFunc = GetRef(objRS.Fields(1).Value)
     tcFunc
    objRS.MoveNext
Loop

In /../Libraries/Tests.vbs file, I have this function

Sub foo()
  msgbox "hello"
End Sub

which is referenced in the Excel as below

When I try to use GetRefm QTP is throwing the below error

Invalid procedure call or argument: 'GetRef' Line (10): "Set tcFunc = GetRef(objRS.Fields(1).Value)".

If foo is defined in QTP's Action, then it works but when I import it from a functional library, it fails. Any idea how to make it work?

EDIT -- WORKAROUND SOLUTION

Instead of using LoadFunctionLibrary, use ExecuteFile to load the functional library.

Avaria

Would that not be pulling the headings?, so in effect, it's trying GetRef("Test Name") and failing (for multiple reasons, 1. procedure pointers can't contain spaces 2. it doesn't exist). Have you actually tried debugging what objRS.Fields(1).Value is returning rather than assuming? – Mauk

HDR=YES so it will treat headers correctly. Also, objRS.Fields(1).Value correctly prints the value (i used print just to verify) – Avaria

Why the loop through, you only want to call GetRef() once right? – Mauk

This is just a test code that I pasted ... in reality I am expecting multiple tests mentioned in the excel file and hence I would want to execute all of them if To Execute is set to True – Avaria

As a test move foo() out of the external vbs file and place it inside the test code, does it still fail? I suspect it might be to do with how LoadFunctionLibrary() loads the vbs, does it use Execute()? – Mauk

Surprisingly no ... it behaves weirdly if I am trying to GetRef from a different file/action. I also think it is some bug in QTP's LoadFunctionLibrary but not sure – Avaria

I think I have your answer, this is a duplicate question. See How to call a function (with parameters) which is in function library, taking the function name from a variable? – Mauk

Suspect that your LoadFunctionLibrary() procedure doesn't use ExecuteGlobal to execute the external vbs meaning it isn't available to global scope so GetRef() will never see it. – Mauk

Ahh .. yeah same question and unfortunately my suspicion is correct. QTP's fault - might have to go with Eval only. LoadFunctionLibrary is QTP's inbuilt function to import external code ... probably something fishy in there. Thanks. – Avaria

@Lankymart - Just used ExecuteFile instead of LoadFunctionLibrary and that did the trick. LoadFunctionLibrary is a recent development in QTP so assuming that it is not fully tested (which is ironic given QTP is a testing tool) – Avaria

Add an answer then, that way the question is resolved. It's perfectly fine to answer your own question as the question and answer are for everyone. – Mauk

@Lankymart - Can't answer because its not duplicate. Adding it in the question itself. – Avaria

Instead of adding to the question (as that will likely be edited out by someone) add your answer (maybe even give a little context) to the highlighted duplicate question. I think your answer would still be relevant there. – Mauk

Not getting the answer option when I am editing the question. Looks like i need to wait 48 hours before i can answer. Why don't you add the answer (if you can) and I will accept it so that future audience can see the working solution. – Avaria

You don't need to edit the question, just open the question link and at the bottom of the page you should see "Your Answer" with a text box. – Mauk

I can't see any answer box - looks like I forgot to check Answer my own question checkbox. – Avaria