Static variable vs Function [Excel 2003]

[Dynamo Nath]

I've been reading a book and came across Static variables and was wondering what the pros and cons are of using a such a variable versus calling a function each time the value is required. My code currently calls a function each time.

 

[GlennUK]

You code calls a function to do what? Can you show your function code?

 

[Dynamo Nath]

It counts how many lines have been imported from a separate spreadsheet. That figure is then to select that data area and/or specific columns throughout the rest of the code. Each time the number is needed, I call the function. Everything works ok but I was wondering if using static variables would better/neater/quicker etc.

row_count = ActiveSheet.Range(cnst_wo_no & cnst_header_row, _
    ActiveSheet.Range(cnst_wo_no & cnst_header_row).End(xlDown)).Rows.Count + cnst_header_row - 1

 

[GlennUK]

I'd say to store your row_count as a Public variable. Set it up once with a single call to the function, and then reference it as a variable after that.

 

[Dynamo Nath]

What would be the difference between a static and a public variable? They sound like they do almost the same thing...

 

[GlennUK]

Not quite ... as far as I know Static means that variables in called functions will remain the same between calls while the main code is still running. Public means that variables will remain between runs of the main code. I may be wrong ... there are plenty of experts on here who may correct me on this.

 

[Dynamo Nath]

Ah, a slight but subtle difference. I'll have to experiment and see which way works best. Thx