forumla to set varable without wirting to sheet

[dantheman9]

Is is possible to do a formula off the sheet and also set the varable numberformat?

example of what im trying to do;

Dim stime as String
Dim Refresh as Integer
 
 
stime = Formula(Refresh "/ 86400").NumberFormat = "hh:mm:ss"

 

[dantheman9]

right so I guess this needs to be a function?
so how can i get this to work?

Sub test
Dim stime as String
Dim Refresh as Integer
 
 
stime = ctime(refresh)
 
End Sub
 
Function ctime(number)
ctime = number / 86400
with ctime
.FormatNumber = "hh:mm:ss"
End With 
End Function

 

[dantheman9]

ok so I have part of the function working, but the answer from the function seems to not be send back to the sub;

Sub test()
Dim refresh As Integer
Dim stime As Integer

refresh = 20 * 2       'cellhtml
stime = ctime(refresh)    ' stime is not updated with the value from ctime?
   
End Sub
 
 
Function ctime(number)
ctime = number / 86400
 
End Function

also can the ctime be converted into a time format? (effectly im looking to convert the value of refresh from seconds to hh:mm:ss.

 

[GTO]

Hi there,

I'm not experienced at extremely small/large numbers and conversion/coercement needs, but off the top, yo uare declaring stime as an Integer. 40/86400 is way less than 1, hence the seeming non-return.

Option Explicit
    
Sub test()
Dim refresh As Long
Dim stime As Variant
    
    refresh = 20 * 2       'cellhtml
    stime = ctime(refresh)    ' stime is not updated with the value from ctime?
End Sub
     
Function ctime(number) As Variant
    ctime = CDec(number / 86400)
    Debug.Print ctime
End Function

 

[dantheman9]

thanks GTO that worked great - seems I need to brush up on how to set varables correctly! ....not to mention how to set them in functions too!