Passing row numbers as function argument

Hi,

I'm quite new on VBA and need help with a problem. I will try to explain without writing a novel.
I have an excel sheet with thousands of cells filled with data.
There are groups of data where I would like to do different kinds of calculations.
I have made a lot of different vba scripts explicity for each group of cells where I am doing some calculations.
Now, in order to decrease the number of scripts and make my code more maintainable I would like to send row numbers as argument to functions called from different cells. Then I can use less scripts which will be generic.
I have those as constants. from time to time I will change the values.

I put an example here (The important thing is not what is calculated but how I can get my start & end constants to my function).
The ones listed in the example is only a minor part of my constants.

So, the constants startGrp1 etc need to go as argument startGrp and endGrp in my script.

Private Const startGrp1 = 50
Private Const endGrp1 = 137
Private Const startGrp1b = 143
Private Const endGrp1b = 230
Private Const startGrp2 = 237
Private Const endGrp2 = 258
Private Const startGrp2b = 237
Private Const endGrp2b = 265

Private Const StartCpuCol = 15
Private Const EndCpuCol = 16
Private Const CpuInst = 14
Private Const Load = 22



Function Cpu_test(cpu As Variant, startGrp As Integer, endGrp As Integer)
Dim ret As Double
Dim counter As Integer
Dim divisor As Integer
ret = 0
For counter = startGrp To endGrp
If Worksheets("mySheet1").Cells(counter, StartCpuCol).Value <= cpu Then
If Worksheets("mySheet1").Cells(counter, EndCpuCol).Value >= cpu Then
divisor = Worksheets("mySheet1").Cells(counter, CpuInst).Value
ret = ret + (Worksheets("mySheet1").Cells(counter, Load).Value) / divisor
End If
End If
Next
Cpu_test = ret
End Function


Hope someone can help :-)
Kind Regards,
Ulf

Hi,

I understand, sorry about that.

Cervinus

Hi,

Thanks for quick reply and sorry for being unclear.

The thing is that I have lots and lots of different VBA scripts for different kinds of calculations.
The code snippet I showed was only one out of many. Also the constants that I show is only a small part of what I use.
I run different script from different cells and same scripts from many cells as well.
I use three different worksheets that work together and the main worksheet contains about 200 000 cells.
Today my VBA code spans over approximately 30000 lines of code with 2000 functions.

The idea is to reduce the number of code lines and functions which will be possible if I can send row numbers (my constants) as function arguments.
Today I have explicit VBA scripts for each group of data (spanning over specific rows).
Part of the problem is that approximately once a month I are changing the rows for different groups I use and if I use the method you suggest I will have to change manually in 50-100 cells.

If I can decrease lines of code excel will (hopefully) recalculate faster.

Hi,
Thanks :-)

I will try your suggestion. However it will take a while since I'll be out of town for a couple of days.
Sorry for not being able to test right away since you guys are so quick with your help, but I will come back with result (if positive) or upload a copy of my workbook.

Hi,

My problem is solved :-)

Instead of using constants I put my row identifiers in cells and pass those cell references as argument to my functions.

Please Login or Register  to view this content.

and my function call looks like this now:

Please Login or Register  to view this content.

Maybe not the best solution but it works fine for my purpose :-)
Thanks again for your assistance. I appreciate it :-)