VBA Cutsom function breaks on updating another workbook

[PanzerRanger]

Hi!

I have write a custom function sista raden means last row:

Public Function SISTARADEN()
Dim a, h As Integer
a = Worksheets("Stl").Cells(Rows.Count, "A").End(xlUp).Row - 1
h = Worksheets("Stl").Cells(Rows.Count, "H").End(xlUp).Row - 1
If a > h Then
SISTARADEN = a
Else: SISTARADEN = h
End If

End Function

It is then used in a formula =IF(SISTARADEN()/30<1;1;ROUNDUP(SISTARADEN()/30;0))
It is use to automatically create pages in another workbook. The problem is, that if I make a full calculations in another workbook this custom formulas, and a few others, similar to this breaks by returning #VALUE !

What have I done wrong?

 

[FormR]

Hi, welcome to the forum! You probably need to qualify the workbook. Assuming the UDF is in the workbook that is calling it.

Public Function SISTARADEN()
 Dim a As Long, h As Long
 a = ThisWorkbook.Worksheets("Stl").Cells(Rows.Count, "A").End(xlUp).Row - 1
 h = ThisWorkbook.Worksheets("Stl").Cells(Rows.Count, "H").End(xlUp).Row - 1
 If a > h Then
 SISTARADEN = a
 Else: SISTARADEN = h
 End If
End Function

But, in my opinion, you should really be passing the ranges to the function, so..

Public Function SISTARADEN(Col1 As Range, Col2 As Range)
 Dim a As Long, h As Long
 a = Col1.Cells(Rows.Count, "A").End(xlUp).Row - 1
 h = Col2.Cells(Rows.Count, "H").End(xlUp).Row - 1
 If a > h Then
 SISTARADEN = a
 Else: SISTARADEN = h
 End If
End Function

Which you would call like:
=IF(SISTARADEN(stl!A:A;stl!H:H)/30<1;1;ROUNDUP(SISTARADEN(stl!A:A;stl!H:H)/30;0))

Note the changes to the variable declarations.

 

[FormR]

But, in my opinion, you should really be passing the ranges to the function, so..

That second piece of code was a last minute addition to my post and should you opt for that option this would be better:

Public Function SISTARADEN(Col1 As Range, Col2 As Range)
 Dim a As Long, h As Long
 a = Col1.Cells(Col1.Rows.Count).End(xlUp).Row - 1
 h = Col2.Cells(Col2.Rows.Count).End(xlUp).Row - 1
 If a > h Then
 SISTARADEN = a
 Else: SISTARADEN = h
 End If
End Function