Question re. Custom Function (UDF)

[JumboCactuar]

Hi,
I have this custom UDF and want to use it as a formula to fill a column..

Would this be volatile and recalculate on every cell change?

Function URLExists(url As String) As Boolean
    Dim Request As Object
    Dim ff As Integer
    Dim rc As Variant
  
    On Error GoTo EndNow
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
  
    With Request
      .Open "GET", url, False
      .Send
      rc = .StatusText
    End With
    Set Request = Nothing
    If rc = "OK" Then URLExists = True
  
    Exit Function
EndNow:
End Function

B2 filled down =

=URLExists(A2)

Would i be better running a macro and writing the values? Or would it be fine to use as a formula

Thanks for any info

 

[offthelip]

I would never use a function that was going to make a call to a remote URL on every recalculation that is is recipe for making your workbook really really slow.
Just once when the workbook is activated should be good enough. URL don't usually disappear very often
Or even run on demand i.e in a subroutine

 

[JumboCactuar]

I would never use a function that was going to make a call to a remote URL on every recalculation that is is recipe for making your workbook really really slow.
Just once when the workbook is activated should be good enough. URL don't usually disappear very often
Or even run on demand i.e in a subroutine

Thanks
I will have a sub that loops the row and calls the function on request.
That should be ok i think

Sub CheckURLS()

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To LR
    Cells(i, 2) = URLExists(Cells(i, 2))
Next

End Sub

 

[lrobbo314]

Might want to consider putting it all into 1 subroutine. If you call that function for each cell in your range, you will be creating a new object for each cell.

Doing it like this might speed things up.

Sub MAIN()
    Dim Request As Object: Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    Dim r As Range: Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Dim AR() As Variant: AR = r.Value2
    Dim b As Boolean
    On Error GoTo EndNow
    
    For i = 1 To UBound(AR)
    b = True
        With Request
          .Open "GET", AR(i, 1), False
          .Send
          If b Then AR(i, 1) = True
        End With
    Next i
    
    Set Request = Nothing
    r.Offset(, 1).Value = AR
    Exit Sub
    
EndNow:
        AR(i, 1) = False
        b = False
        Resume Next
End Sub