0

I feel obliged to preface this by stressing not a Visual Basic expert. Indeed, my grasp of Visual Basic is rudimentary at best.

That said, I have an Excel file with a single column containing a series of URL addresses. Next to that column I've created an additional column "Link Check", wherein I would like to dynamically return the HTTP status of each URL address.

I've attempted to create a simple function using Visual Basic that will do just that. Here is that function as currently written:

Public Function CheckURL(url As String)
    Dim request As Object
    Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
    On Error GoTo haveError
    With request
        .Open "HEAD", url
        .Send
        CheckURL = .Status
    End With
    Exit Function
haveError:
    CheckURL = Err.Description
End Function

As written, any attempt to utilize this function returns a #VALUE! error.

Anyone have any suggestions as to how I might change the code to produce a workable result?

iamthelabhras
  • 351
  • 2
  • 12
  • 1
    Call your function from a sub and you'll be more easily able to debug it. – Tim Williams Oct 22 '21 at 04:20
  • code works for me as written – Chris Strickland Oct 22 '21 at 04:21
  • 1
    Looks like this answer - https://stackoverflow.com/questions/44813295/how-to-do-image-url-validation-in-excel-2016/44813655#44813655 - that does work, so maybe there is some issue with your URLs ? Or are you on a Mac? – Tim Williams Oct 22 '21 at 04:22
  • 1
    set a breakpoint (click on the margin to the left of the line) on the second line and step through (probably using f8) to see where the error occurs and what is actually thrown – Chris Strickland Oct 22 '21 at 04:22
  • @TimWilliams: I'm indeed on a Mac. I'm guessing that's kind of a big deal here? – iamthelabhras Oct 22 '21 at 04:26
  • 1
    VBA on Mac is quite different once you get outside of the Office application object models. Maybe check here - https://stackoverflow.com/questions/38211124/http-get-request-using-vba-in-osx-excel ? Not a Mac user so can't offer much else... If you end up using curl then it looks like there's an `-I` option which will perform a HEAD request. – Tim Williams Oct 22 '21 at 04:28
  • No worries, Tim. I appreciate you taking the time to thoughtfully respond. I'll dig into the link you shared and try make it work! – iamthelabhras Oct 22 '21 at 04:30
  • 1
    Might be able to use `PowerQuery` if on a Mac. See `https://learn.microsoft.com/en-us/powerquery-m/webaction-request` Just trying to give another potential option :) – Ryan Wildry Oct 22 '21 at 14:59

0 Answers0