Working with VBA

Support
#1

Hi!
Help to write the macro, please.

Sub Test1()
Dim Link%, Cond%, Result%: Link = 5: Cond = 5
Result = Application.Sum(Link, Cond)
MsgBox Result
End Sub

Sub Test2()
' Temporarily make Async functions run synchronously
' https://seotoolsforexcel.com/working-with-vba/
Dim Link$, Cond$, Result$
Link = "https://twitter.com/fvirtanen"
Cond = "//p[@class='ProfileHeaderCard-bio u-dir']"
' Result = Application.XPathOnUrl(Link, Cond) '>> Run-time error '438'
Result = Evaluate("=XPathOnUrl(Link, Cond)") '>> Run-time error '13'
End Sub

Test1 is working, Test2 - Run-time error '438' or Run-time error '13'.
What am I doing wrong?

#2

Several things you're missing, first, evaluate takes the whole expression and you need to manually concatenate the formula with the Link and Cond variables, including the quotes. Second, Evaluate accepts an array because XPathOnUrl returns an array. Try this code:

Sub Test2()

    Dim arrResult() As Variant
    
    Dim Link As String, Cond As String
    
    Link = """https://twitter.com/fvirtanen"""
    Cond = """//p[@class='ProfileHeaderCard-bio u-dir']"""

    arrResult = Evaluate("=XPathOnUrl(" & Link & "," & Cond & ")")
    
    MsgBox arrResult(1)

End Sub
#3

diskborste, it works. Thank you!

#4

diskborste, I learned how to programmatically connect and disconnect SEOTools, but there are problems with Function TEST.
What am I doing wrong?

#5

Not sure, difficult to review the code at a glance. I recommend using breakpoints and stepping through the code while keeping an eye on the variables via the Locals window.

#6

diskborste, I tried, but did not find anything informative when debugging...

#7

You should be able to see which variable is not getting populated with a value from the formulas.

Also try this code to make it easier to catch bad requests:

        On Error Resume Next
        arrContent = Evaluate("formula")

        Do Until Application.CalculationState = xlDone
            DoEvents
        Loop

        If err.Number > 0 Then
            'error do stuff
        End If
#8

Replace error do stuff to Debug.Print Err.Number and didn't catch anything...

#9

If no error occurs then should be simple to save each request to a variable and see if it gets populated and with what.

#10

diskborste, I'm not so good in VBA, need help from the community.

#11

Try and change the requests to dummy values and you should be able to figure out where it goes wrong with stepping through the code.