HypRetrieve not retrieving correct status code

Question

Background

I have two databases that I need to connect to. One is in Hyperion and the other one is in ESS. I have imported the smartview.bas as stated by the documentation and I am attempting to use the functions within it. I have dummy sheets (SavedLogHyperion and SavedLogESS) for each enviroment to make sure the users logs in before running all the code. I want to retrieve the proper error code if the user closes the window without logging or other things that may prevent the successful login.

Problem

The HypRetrieve only acknowledge for the first result: if the user was able to log to Hyperion environment, but if ESS login window is cancelled or provided with non-valid credentials and then closed, it detects the code as 0 ("Ok"), thus detecting a successful login for the second environment when it was not.

Code

I wrote a function to retrieve the number, I thought that it could be a time thing and that is why I made it (so for the main code could resolve on time), but it seems like it is not.

Function Return_NumCodeSVHypRetrieve(VarTxtSheetToLogin As Variant) As Long
Dim NumCodeHypRetrieve As Long
    NumCodeHypRetrieve = HypRetrieve(VarTxtSheetToLogin)
    Return_NumCodeSVHypRetrieve = NumCodeHypRetrieve
End Function

This function is called in my main sub

Sub Main()
Dim NumCodeConnectionSheet1 As Long
Dim NumCodeConnectionSheet2 As Long
NumCodeConnectionSheet1 = Return_NumCodeSVHypRetrieve("SavedLogHyperion")
NumCodeConnectionSheet2 = Return_NumCodeSVHypRetrieve("SavedLogESS") 'If I log in "SavedLogHyperion", this variable becomes 0 too, or any other error code that variable had
End Sub

Question

How can I make the correct code according to the sheet attempted to log be correctly saved? I am clueless on what may be the approach

Answer 1

Solution:
The problem seems to be on how the function works; I noticed that when the function is applied, it activates the sheet, which lead me to believe that there was a problem on timing events, I came with the following solution, which has been basically to provide the scenario that I saw the function is expecting to, also I noticed that if I set the NumCode to retrieve as long as the direct result, it does not behave as expected, my approach was to declare it a variant and then cast it to a long instead.

Function Return_NumCodeSVHypRetrieve(VarTxtSheetToLogin As Variant) As Long
Dim VarNumCode As Variant
    'It seems the function relies on the sheet being activated and if the Retrives does it, it takes miliseconds to do, which are not sync with excel life cycle, thus causing missreadings
    Sheets(VarTxtSheetToLogin).Visible = True: Sheets(VarTxtSheetToLogin).Select: DoEvents
    VarNumCode = HypRetrieve(VarTxtSheetToLogin)
    Sheets(VarTxtSheetToLogin).Visible = False
    Return_NumCodeSVHypRetrieve = CLng(VarNumCode)
End Function