Need Help ASAP Issue with Subs

[agustino.dav]

I am new to VBA and I'm teaching myself how to use it.

In this case there are two sheets (Sheet 2: "HC" and Sheet 3: "GC")
I am trying to create a few functions/subs (not really sure of the difference) that I can reference that will return values that I have them calculate
All my work is done within Sheet 3 in VBA

So I essentially have three sections

Sub fnc1()

End Sub

...

Sub fncN()

End Sub


Sub Worksheet_Activate()

End Sub


Sub Worksheet_Change(ByVal Target As Range)


End Sub


Now say one of the functions I want to run is this
Sub HCRRange()
' Obtain the last Row number on the Hard Coded Collection Sheet
a = 2
flag = True
While flag = True
If Sheets("Hard Coded Collection").Cells(a, 1) <> "" Then
a = a + 1
Else
flag = False
End If
Wend
HCRRange = a - 1
End Sub


And now I want to access this in my Sheet Change function

Sub Worksheet_Change(ByVal Target As Range)
Dim HCR As Integer
HCR = HCRRange()

End Sub

But i get "compiled error expected function or variable"
What am I doing wrong

[mikerickson]

If you are returning a value (or an object) use a Function


Function LastCellInColumnA() As Range
Set LastCellInColumnA = ThisWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp)
End Function

To use in in a Change event

Sub Worksheet_Change(ByVal Target As Range)
Dim HCR As Integer
HCR = LastCellInColumnA.Row

' ...
End Sub