seconds to hours and runtime error 6 in vba and M1 chip?

[blueflash]

i am having a problem with excel 2016 on a new iMac (OS12.4) with an M1 chip.
Has anyone had problems running excel 2016 with the m1 chip?
In the code below (copied from elsewhere), if I run it as is, correct values are calculated for variables h, m and s (though rounding for s doesn't always give the correct result), but it gives a Run Time error 6 overflow at the t= statement.
If I dim the variable totsecs as single, double, long, integer or date I get the same Run Time error at the statement totsecs=
I am sure that I did not have this sort of problem when running this code in excel 2016 on my previous mac which was a 2017 model running OS12.4

I also have problems with writing some variable values to cells in a worksheet, eg cells(5,"j").value= totsecs sometimes does not write anything to the cell but cells(5,"j").value = 658 or cells(5,"j").value = "abc" are ok.

Does anyone have any ideas? Is it a compatibility problem with the m1 chip? Is there a library or reference that I need to set or change?

If it is a specific compatibility problem with excel 2016, would excel 2019 solve the problem?

I have a number of VBA routines that were running without problems on my old mac and I cannot do without them.

Any advice would be greatly appreciated

Sub testtime()

Dim t As Date
Dim h As Integer
Dim m As Integer
Dim s As Integer
'Dim totsecs As Single

totsecs = 2425.66
h = Int(totsecs / 3600)
m = Int(totsecs / 60 - (h * 60))
s = Int(totsecs - ((h * 3600) + m * 60))
MsgBox h & " hours, " & m & " minutes and " & s & " seconds"

t = CDate(h & ":" & m & ":" & s)
MsgBox t
End Sub

h = Int(totsecs / 3600)
m = Int(totsecs / 60 - (h * 60))
s = Int(totsecs - ((h * 3600) + m * 60))
MsgBox h & " hours, " & m & " minutes and " & s & " seconds"

t = CDate(h & ":" & m & ":" & s)
MsgBox t

 

[Georgiboy]

Seems like a Microsoft Excel bug for Mac that's been around a while, have you tried to define the double as a variant?

 

[blueflash]

Yes, I still get the same run time error at t=...

 

[Georgiboy]

Does the below throw the same error:

Sub testtime()
    Dim t As Variant
    Dim h As Integer
    Dim m As Integer
    Dim s As Integer
    Dim totsecs As Variant
    
    totsecs = 2425.66
    h = Int(totsecs / 3600)
    m = Int(totsecs / 60 - (h * 60))
    s = Int(totsecs - ((h * 3600) + m * 60))
    MsgBox h & " hours, " & m & " minutes and " & s & " seconds"
    
    t = TimeValue(h & ":" & m & ":" & s)
    MsgBox t
End Sub

 

[Georgiboy]

Or maybe more like:

Sub testtime()
    Dim t As Date
    Dim h As Integer
    Dim m As Integer
    Dim s As Integer
    Dim totsecs As Variant
    
    totsecs = 2425.66
    h = Int(totsecs / 3600)
    m = Int(totsecs / 60 - (h * 60))
    s = Int(totsecs - ((h * 3600) + m * 60))
    MsgBox h & " hours, " & m & " minutes and " & s & " seconds"
    
    t = TimeSerial(h, m, s)

    MsgBox t
End Sub

 

[blueflash]

The first example gives the same error at t=..

the second example gives a result of

"0 hours, 40 minutes and 25 seconds"
in the first message box
and
"12:40:25 am" in the second message box

Seems a bit strange

 

[Georgiboy]

Interesting - sorry i don't have a Mac so have been throwing ideas out there.

What value is returned with the below:

Sub testtime()
    Dim t As Variant
    Dim h As Integer
    Dim m As Integer
    Dim s As Integer
    Dim totsecs As Variant
    
    totsecs = 2425.66
    h = Int(totsecs / 3600)
    m = Int(totsecs / 60 - (h * 60))
    s = Int(totsecs - ((h * 3600) + m * 60))
    MsgBox h & " hours, " & m & " minutes and " & s & " seconds"
    
    t = TimeValue(TimeSerial(h, m, s))

    MsgBox Format(t, "hh:mm:ss")
End Sub

 

[blueflash]

Thanks for your suggestions georgiboy.
The last one

produced a result of
12:40:25 am for t

I changed the value of totsecs to 12435.66 and got the following in the message boxes:

3 hours, 27 minutes and 5 seconds

3:27:05 am

I guess I could use the t=TimeSerial(h,m,s) version and extract the values from t and reformat them to a text string I want.
Seems like a longwinded way of doing things tho.
as a matter of interest, dimming t as date or variant or not dimming t at all, made no difference to the result using TimeValue or Time Serial.