VBA - Syntax issue on VBA on new Excel

[cadence]

Hi there,

Having an issue updating a file from old 2008 Excel to Excel 2019.

This routine use to work and still does except this one line is getting called with a runtime error 1004. Method '_Default of object 'Range' Failed.

Can anyone suggest a different way to write this line or why it might not work in a newer version go excel?

wsBudget.Range("O78") = iDaysToExpiry

Public Sub CheckExpiry()
Dim oScrambler As New Encryption
Dim wsBudget As Worksheet
Dim sActivationKey As String
Dim dteExpiry As String
Dim iDaysToExpiry As Integer
Dim sExpiryDate As String

    Application.EnableCancelKey = xlDisabled
    Set wsBudget = ThisWorkbook.Sheets("budget")
    sActivationKey = ThisWorkbook.Sheets("Authorise").Range("D10")
   
    'Decrypt will return the date as a number, so convert back to date format
    sExpiryDate = oScrambler.Decrypt(sActivationKey)
   
    If sExpiryDate = "" Then
      'Invalid code - may have been tampered with
      MsgBox "Your activation code is invalid. Please contact for a new activation key.", vbError, g_sSystem
      Call HideAll
      ThisWorkbook.Sheets("Authorise").Activate
      End
    Else
      dteExpiry = CDate(sExpiryDate)
    End If
   
    wsBudget.Unprotect (g_sPassword)
    iDaysToExpiry = DateDiff("d", Now(), dteExpiry)
    wsBudget.Range("O77") = Format(dteExpiry, "MMMM dd, yyyy")
    wsBudget.Range("O78") = iDaysToExpiry
    wsBudget.Protect (g_sPassword)
    If iDaysToExpiry < 0 Then
        'Expired already
        MsgBox "Your validation time has expired. Please contact for a new activation key.", vbError, g_sSystem
        Call HideAll
        ThisWorkbook.Sheets("Authorise").Activate
        End
    Else
        If iDaysToExpiry <= 30 Then
          'Expiring within 30 days
          MsgBox "Your licence will expire in " & iDaysToExpiry & " days." & vbNewLine & "Please contact soon for a new activation key.", vbExclamation, g_sSystem
        End If
        Call ShowAll
    End If
End Sub

much appreciated

 

[Yongle]

The 4 relevant lines do what they should if extracted from your code

    Dim wsBudget As Worksheet
    Dim iDaysToExpiry As Integer
    Set wsBudget = ThisWorkbook.Sheets("budget")
    wsBudget.Range("O78") = iDaysToExpiry

Is VBA is identifying the correct problem line ?
It is peculiar that 1st line below runs but the 2nd does not
- what happens if you comment out the 2nd line?

    wsBudget.Range("O77") = Format(dteExpiry, "MMMM dd, yyyy")
    'wsBudget.Range("O78") = iDaysToExpiry

Does setting the 2 cells at the beginning of the code work ?

   Dim O77 As Range, O78 As Range
'after this line
    Set wsBudget = ThisWorkbook.Sheets("budget")
'set the 2 cells
    Set O77 = wsBudget.Range("O77")
    Set O78 = O77.Offset(1)

'further down replace these 2 lines
    wsBudget.Range("O77") = Format(dteExpiry, "MMMM dd, yyyy")
    wsBudget.Range("O78") = iDaysToExpiry
    O77 = Format(dteExpiry, "MMMM dd, yyyy")
    O78 = iDaysToExpiry

Even though it this appears to make no sense
I have seen this "tweak" fix a similar problem

    wsBudget.Unprotect (g_sPassword)
wsBudget.Activate
    iDaysToExpiry = DateDiff("d", Now(), dteExpiry)
    wsBudget.Range("O77") = Format(dteExpiry, "MMMM dd, yyyy")
    wsBudget.Range("O78") = iDaysToExpiry
ThisWorkbook.Sheets("Authorise").Activate
    wsBudget.Protect (g_sPassword)