Declaring variable

[confusion123]

In the code below, is there any reason why these are declared twice, once in the Sub and once in the Function?

Dim objLoan As Loan

Dim objLoans As Collection

Could they not been declared once only at the top of the module?

Sub TestCollectionObject()

Dim rg As Range

Dim objLoans As Collection

Dim objLoan As Loan

Set rg = ThisWorkbook.Worksheets("Loans"). _

Range("LoanListStart").Offset(1, 0)

' get the collection of loan objects

Set objLoans = CollectLoanObjects(rg)

Debug.Print "There are " & objLoans.Count & _

" loans."

' iterate through each loan

For Each objLoan In objLoans

Debug.Print "Loan Number " & objLoan.LoanNumber & _

" has a payment of " & Format(objLoan.Payment, "Currency")

Next

Set objLoans = Nothing

Set objLoan = Nothing

Set rg = Nothing

End Sub

Function CollectLoanObjects(rg As Range) As Collection

Dim objLoan As Loan

Dim objLoans As Collection

Set objLoans = New Collection

' loop until we find an empty row

Do Until IsEmpty(rg)

Set objLoan = New Loan

With objLoan

.LoanNumber = rg.Value

.Term = rg.Offset(0, 1).Value

.InterestRate = rg.Offset(0, 2).Value

.PrincipalAmount = rg.Offset(0, 3).Value

End With

' add the current loan to the collection

objLoans.Add objLoan, CStr(objLoan.LoanNumber)

' move to next row


Set rg = rg.Offset(1, 0)

Loop

Set objLoan = Nothing

Set CollectLoanObjects = objLoans

Set objLoans = Nothing

End Function

 

[Joe4]

Re: Declaring varable

Any declared within a Procedure or Function is "local" to that procedure or function only, and cannot be accessed from outside of that particular function or procedure.

If you have variables whose values you want to use in multiple procedures/functions, you can declare them globally. However, you should only do this if you need their value accessible in multiple procedures/functions. Don't do this just for the sake of trying to re-use the same variables. That could lead to unintended consequences if you are not careful.
See: Variable Scope in Excel VBA - EASY Excel Macros

 

[confusion123]

Re: Declaring varable

Any declared within a Procedure or Function is "local" to that procedure or function only, and cannot be accessed from outside of that particular function or procedure.

If you have variables whose values you want to use in multiple procedures/functions, you can declare them globally. However, you should only do this if you need their value accessible in multiple procedures/functions. Don't do this just for the sake of trying to re-use the same variables. That could lead to unintended consequences if you are not careful.
See: Variable Scope in Excel VBA - EASY Excel Macros

Thanks.

So in my example, it SHOULD be declared once at the module level?

 

[Joe4]

Re: Declaring varable

So in my example, it SHOULD be declared once at the module level?

I don't know, that is a determination for you to make based on the conditions I laid out.
Do you need to get the values set to those variables in one procedure/function to the other?

Note, very seldom do you ever see Function use Global variables. Functions are usually pretty generic, and work off of inputs sent to them.
So, many times, you see a procedure call a function, feeding it the inputs/variables it needs, i.e.

Sub MyProc()
    Dim var1 as Long
    Dim var2 as Long
    Dim var3 as Long
'   Some code here, calculating var1 and var2
'   Call function, calculating var3 by passing var1 and var 2 to my function
    var3 = MyFunc(var1,var2)
End Sub

Function MyFunc(input1 as Long, input2 as Long) as Long
'   calculation here
End Function

 

[RoryA]

Re: Declaring varable

Thanks.

So in my example, it SHOULD be declared once at the module level?

No, not really. That would lead to the slightly odd situation of populating a variable with an object, assigning the variable as the result of a function, deleting the variable and then populating it with the result of the function. If you're going to do that, you should simply pass the variable byref to a routine in the first place.

 

[confusion123]

Re: Declaring varable

I see.

I just thought because it's used once in the Sub and once in the Function, that I could declare just once as a module level variable.

 

[Joe4]

Re: Declaring varable

I just thought because it's used once in the Sub and once in the Function, that I could declare just once as a module level variable.

Not necessarily, for all the reasons we mentioned.

Employing that type of logic, what if someone used "x" a lot for their variables names? They may have multiple procedures in a single module that all use "x", but none of them are related in any way. Just because the different procedures use the same variable name does not mean that you want to declare it as a Global variable - that is not how you make that determination.

I rarely use Global variables. Most of the time, if values need to be shared, they can be passed by reference.

An example of where you might be using a Global Variable is let's say that you have a number of different procedures that check for various errors. Maybe you set up a Global Variable to keep track of that error count (since that value is being updated by different procedures).

 

[confusion123]

Re: Declaring varable

Not necessarily, for all the reasons we mentioned.

Employing that type of logic, what if someone used "x" a lot for their variables names? They may have multiple procedures in a single module that all use "x", but none of them are related in any way. Just because the different procedures use the same variable name does not mean that you want to declare it as a Global variable - that is not how you make that determination.

I rarely use Global variables. Most of the time, if values need to be shared, they can be passed by reference.

An example of where you might be using a Global Variable is let's say that you have a number of different procedures that check for various errors. Maybe you set up a Global Variable to keep track of that error count (since that value is being updated by different procedures).

Isn't it bad practice to have the same variable name but actually they are not related in any way? Why not just name them x, y, z, etc.?

 

[Joe4]

Re: Declaring varable

Isn't it bad practice to have the same variable name but actually they are not related in any way? Why not just name them x, y, z, etc.?

Not if they are in separate procedures, no. Each procedure is a separate container unto itself. And as long as the variables used are not Global, procedures cannot get the values of the variables from other procedures (unless they are passed by reference).
I see people who always use a variable of "i" the first time they do a loop in any procedure (if they have a second loop, they will use j, etc). Nothing wrong with that.

I would highly recommend using the "Option Explicit" command at the top of every Module, which forces one to declare all variables in their procedures and functions. This helps reduce errors.

 

[ScottR]

Re: Declaring varable

Isn't it bad practice to have the same variable name but actually they are not related in any way? Why not just name them x, y, z, etc.?

Not in the least, provided that you're designing the variable scope appropriately. Ie only using globals when absolutely necessary.

This is unlikely to cause you many issues in Excel but imagine relying heavily (and inappropriately) on Global variables in, say, Javascript. Even though a Browser might have tabs open to a dozen different pages there is only one "instance" of Javascript running and conflicting global variable names from different sites could theoretically cause all kinds of unexpected results.

If a variable is scoped only to exist within a procedure then no issues occur, no matter what they're called.