Need Help ASAP Issue with Subs

[agustinodavalos]

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

 

[V_Malkoti]

HCRange must be declared as a function since you expect it to return a value. Change your code to:

Function HCRRange()
' Obtain the last Row number on the Hard Coded Collection Sheet
    HCRRange = Sheets("Hard Coded Collection").Cells(Rows.Count, 1).End(xlUp).Row
End Function

I changed the code to get last row in a column inside HCRRange as this is concise.

 

[mole999]

add Option Explicit above the first sub, when you compile you will see what isn't defined

 

[agustinodavalos]

You are ALL AMAZING!