Improve VBA code Excel

This code took 1.3 seconds with 1000 rows in each table.

Sub InternExtern2()

    Dim vaSource As Variant
    Dim vaAddress As Variant
    Dim i As Long, j As Long
    Dim rRng As Range
    Dim snStart As Single

    snStart = Timer

    With Sheet1
        Set rRng = .Range("E6", .Cells(.Rows.Count, 5).End(xlUp)).Offset(, -1).Resize(, 24)
    End With
    vaSource = rRng.Value
    vaAddress = Sheet2.Range("address_table_names").Value

    For i = LBound(vaSource, 1) To UBound(vaSource, 1)
        If Len(vaSource(i, 2)) > 0 And InStr(1, vaSource(i, 24), "Extern") = 0 Then
            For j = LBound(vaAddress, 1) To UBound(vaAddress, 1)
                If Len(vaAddress(j, 1)) > 0 Then
                    If InStr(1, vaSource(i, 2), vaAddress(j, 1)) > 0 Then
                        If IsExternal(vaSource(i, 2), vaSource(i, 1), vaAddress(j, 4), vaAddress(j, 3)) Then
                            vaSource(i, 23) = "Extern"
                        Else
                            vaSource(i, 23) = "Intern"
                        End If
                    End If
                End If
            Next j
        End If
    Next i

    rRng.Value = vaSource

    Debug.Print Timer - snStart

End Sub

Public Function IsInternal(vValue As Variant) As Boolean

    IsInternal = True

End Function

Public Function IsExternal(ByVal sSource As String, ByVal sSource2 As String, ByVal sAddress As String, ByVal sAddress2 As String) As Boolean

    Dim bReturn As Boolean

    bReturn = InStr(1, sAddress, "10.") = 0
    bReturn = bReturn Or InStr(1, sAddress, "192.168.") = 0
    bReturn = bReturn Or Not IsInternal(sAddress2)
    bReturn = bReturn Or InStr(1, UCase(sSource), "-EXT-") > 0
    bReturn = bReturn Or InStr(sSource, "any") > 0
    bReturn = bReturn Or InStr(1, sSource2, "FW-Peering") > 0

    IsExternal = bReturn

End Function

The Boolean logic isn't the same (so don't rely on that), but the looping is. As has been mentioned, working with arrays instead of Ranges is much faster. It's actually the writing to the cells that's really slow (reading too, but not as much). In this code, I'm reading the whole range into a 1000x24 array, changing a piece of the array, and writing it back in one fell swoop.

Note that the method used here has some drawbacks. If you have formulas in column D:Z, using this method will overwrite them with values. If that is the case, then use multiple arrays to pull in only the columns you need as opposed to one big array. It seems you're only using D, E, and Z, so you could create three array variables, read in only those columns, and write out only Z. The performance won't change with three arrays or one.

You were checking column Z for "Extern" inside of the address loop. If you check it outside of that loop and it's false, then the loop never runs and that saves cycles. Be very careful about checking anything in 'source' inside of the 'address' loop. If you can move it out of that loop, do it.

Very minor, but checking that the Len of a string is 0 is faster than checking if the string is "" (empty string). It has to do with how computers store strings (they're arrays and checking the upper bound of the array is faster than reading in the whole string array even if it's empty).

Without knowing all of the details, it seems like this would be even faster using worksheet functions and no VBA. But you may have your reasons.

If possible, deactivate autocalculation, visibbillity etc.

Application.ScreenUpdating = False
Application.Calculation = xlManual

And set it back to normal values at the end. If this does not help, we can look at your code.

As mentioned by Christian you should turn

Application.ScreenUpdating = False
Application.Calculation = xlManual

Dimming multiple variables in 1 line doesnt work as expected:

Dim source, addrescell, destination As Range

Will Dim only destination as a Range and the other two as Variant, to get the proper dimensioning you should do:

Dim source As Range
Dim addrescell As Range
Dim destination As Range

I don't know if this would speed it up, but it is more properly.

Always use Value2 to get and let Cell/Range values, unless you want the explicit formatting applied to the value you get from the sheet. Value2 is the actual value of the cell without any formatting applied. Referring to that might speed things up a bit.

I notice that setting values back is done without use of either Value or Value2, I personally would use Value2 there as well.

As previously mentioned by @K_B, Value2 is significantly faster than Value and use it whenever possible.

The largest time savings would be to utilize arrays as opposed to cells, as mentioned by Sean Cheshire in the comments. My thesis model took ages to run (approx. 10 mins) utilizing cells but I was able to drop it to near 1 minute with arrays.

I would have voted up the comment by Mr. Cheshire however I don't have sufficient reputation - props Mr. Cheshire.