0

I have a Google Maps macro that includes a distance calculation.

I only want this if there is an internet connection, otherwise it should show a message saying "No internet connection available, therefore no distance calculation possible." Then, when the user clicks "Ok" it should still do the rest of the macro except the distance calculation and say for that field "No distance available".

The sub formula is:

Sub Calculate()
    Application.ScreenUpdating = False

    ' Calculate Macro

    Range("P32:Y32").Select
    ActiveCell.FormulaR1C1 = "=Calculation!R[-17]C[-7]"
    Range("P33:Y34").Select
    ActiveCell.FormulaR1C1 = "=Calculation!R[-16]C[-7]"
    Range("A100").Select

    If IsInternetConnected() = True Then
        If Range("V45") > 1 Then
            Dim wks1 As Worksheet
            Dim blnDone As Boolean, strBaseAddr As String, strGuestAddr As String
            Dim strDist As String, p As Integer
            Set wks1 = Application.ActiveSheet
            blnDone = False
            strBaseAddr = Range("W43")
            wks1.Activate
            wks1.Range("W44").Select

            While Not blnDone
                If Len(ActiveCell) <> 0 Then
                    blnDone = False
                    strDestAddr = ActiveCell
                    strDist = gDistance(strDestAddr, strBaseAddr)
                    p = 1

                    While strDist = "OVER_QUERY_LIMIT" And p <= 5  'if over limit more than 5 times, move on
                        ' try one more time after a pause
                        Application.Wait (Now() + #12:00:02 AM#)  'wait 2 seconds.. should be long enough but at times isn't.
                        strDist = gDistance(strDestAddr, strBaseAddr)
                        p = p + 1
                    Wend

                    If Not IsNumeric(strDist) Then
                        strDist = 0
                    End If

                    Range("X43") = strDist 'distance column
                    ActiveCell.Offset(1, 0).Select 'move down one row
                Else
                    blnDone = True
                End If
            Wend
        Else
            Range("X45") = "No distance available"
        Else
            MsgBox "No internet connection is detected!"
        End If

    End If

    Application.ScreenUpdating = True
 End Sub

and the function for the internet connection is:

Private Declare Function InternetGetConnectedState _
    Lib "wininet.dll" (ByRef dwflags As Long, _
    ByVal dwReserved As Long) As Long

Private Const INTERNET_CONNECTION_MODEM As Long = &H1
Private Const INTERNET_CONNECTION_LAN As Long = &H2
Private Const INTERNET_CONNECTION_PROXY As Long = &H4
Private Const INTERNET_CONNECTION_OFFLINE As Long = &H20

Function IsInternetConnected() As Boolean
    Dim L As Long
    Dim R As Long
    R = InternetGetConnectedState(L, 0&)

    If R = 0 Then
        IsInternetConnected = False
    Else
        If R <= 4 Then
            IsInternetConnected = True
        Else
            IsInternetConnected = False
        End If
    End If
End Function
Community
  • 1
  • 1
Max
  • 11
  • 2
  • Do you get any errors? What do you mean by it "does not work"? What have you tried? – BruceWayne May 07 '18 at 21:28
  • There are several issues: ①Always format and indent your code nicely and correctly or you don't ever see your mistakes! I did that for you in your question. ② Don't call your procedure `Calculate` because `Calculate` is a keyword already used by Excel. This will mess up easily! ③ In your procedure (pretty much in the end) you have 2 `Else` in one `If` block. That's no valid syntax and therefore not possible. ④ Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to make your code much faster, stable and avoid issues. – Pᴇʜ May 08 '18 at 06:39
  • Perhaps this answer might help: https://stackoverflow.com/questions/35222503/check-internet-connection-from-excel-vba – MacroMarc May 08 '18 at 06:49
  • I debug your internet connection sub and it works ok, your problem isn´t here, you should debug the rest of the code to locate were is the problem. – Aaron Pan Vega May 08 '18 at 10:19

0 Answers0