I`ve got a problem with cells format operations under the Function.
There is the code below and after IF I`d like to clear content of active cell and the cell above or change their color to another but it doesn`t work (but for example merging cells works). The question is why ? It works under Sub but not under Function.
Thanks for help in advance
Code:
Option Explicit
Function Odliczanie2(data1a As String) As String
Dim data2 As Date
Dim data1 As Date
Dim data2godzina As Integer
Dim data2dzien As Integer
Dim roznicaSekundy As Long
data2 = Now()
If IsDate(data1a) Then
data1 = CDate(data1a)
roznicaSekundy = DateDiff("s", data2, data1)
data2dzien = Int(roznicaSekundy / 86400)
data2godzina = Round((roznicaSekundy - (data2dzien * 86400)) / 3600)
If roznicaSekundy < 0 Then
Odliczanie2 = "PO CZASIE !"
Else
Odliczanie2 = data2dzien & " d " & data2godzina & " godz "
End If
Else
Range(ActiveCell, ActiveCell.Offset(-1, 0)).Clear
'lub .Interior.ColorIndex = 23
End If
End Function
Excell file attached.
For example column "2" cells F12 and F13.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
changing values to other cells is not possible when called as a worksheet function
as a function it will work if called from someother procedure, but not from a formula
only suggestion i can think of to try is to use application.ontime to call another procedure after a small delay, so that the function can finish first, before clearing the cell
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Reply With Quote
