Getting Hyperlink from External Database

I am having issues with getting the value or URL of a hyperlink from an external source.

I have a cell with the following formula:

=HYPERLINK([@[CHANGE_LINK]])

It gets the URL from an external data source and puts it into the cell.

In my VBA sub i have the following:

Dim cellRow As Integer
Dim URL As String
Dim rng As Range
Set rng = Sheets(changeSheet).Range("A1:A2000").SpecialCells(xlCellTypeVisible)
cellRow = 4
For Each Value1 In rng.SpecialCells(xlCellTypeVisible)
    URL = Range(Value1.Address).Value '<----CANT GET URL!
    MsgBox URL
    Sheets(sheetname).Range("B" & cellRow & ":B" & cellRow).Value = "=HYPERLINK(" & URL & ",""View Change"")"
    cellRow = cellRow + 1
Next Value1

However, my question is. How do I get the URL of that formula into a VBA variable?

I have tried multiple ways of doing this and none seem to get the URL:

Range("A1:A1").value
Range("A1:A1").Address
Range("A1:A1").Formula
Range("A1:A1").Hyperlink(1)
Range("A1:A1").Hyperlink(1).Item(1)
Range("A1:A1").Hyperlinks
Range("A1:A1").Hyperlink.address
Range("A1:A1").Hyperlink.subaddress