vba in MS Excel

[Rahul Chatterjee]

Hello All

I have inherited a spreadsheet containing VB macros from a predecessor of
mine. The macro does calcs on the basis of x's entered in certain fields on
the spreadsheet. The macro is invoked like this (from the spreadsheet
column)

=Function CalcAdvancement(AdvancementA, AdvancementB, AdvancementC,
AdvancementD, AdvancementE, Advancementf, AdvancementH, AdvancementJ,
AdvancementK, AdvancementL, AdvancementS, _
AdvancementU, ax, bx, cx, dx, ex, fx, g1x, g2x, hx, jx, kx, lx, sx, ux)

There are multiple functions like the above each being invoked from
different cells but with the same parameters

The function itself is very repititive (The following code is repeated
identically EXCEPT for the cell values)

****************************************************************************
***************************
Function CalcAdvancement(AdvancementA, AdvancementB, AdvancementC,
AdvancementD, AdvancementE, Advancementf, AdvancementH, AdvancementJ,
AdvancementK, AdvancementL, AdvancementS, _
AdvancementU, ax, bx, cx, dx, ex, fx, g1x, g2x, hx, jx, kx, lx, sx, ux)
overrideamount = 0

'Instanciate the global variables to false
bfundSelected = False

'* Retireve the status of the Section Selection Fields

ax = Worksheets("table").Range("a2").Value
bx = Worksheets("table").Range("a3").Value
cx = Worksheets("table").Range("a5").Value
dx = Worksheets("table").Range("a6").Value
ex = Worksheets("table").Range("a7").Value
fx = Worksheets("table").Range("a8").Value
g1x = Worksheets("table").Range("a9").Value
g2x = Worksheets("table").Range("a10").Value
hx = Worksheets("table").Range("a11").Value
jx = Worksheets("table").Range("a12").Value
kx = Worksheets("table").Range("a13").Value
lx = Worksheets("table").Range("a14").Value
sx = Worksheets("table").Range("a15").Value
ux = Worksheets("table").Range("a16").Value

'Verify the A fields
If ax = "x" Then

'Check if another fund has already been selected
If bfundSelected = False Then
bfundSelected = True
Else
'If fund was selected throw a warning message and set totals to an ERROR
state. Exit the Function immeadiatly
' msg = MsgBox("More than one section has been selected, please clear
the 'X' from the extra section(s)", vbExclamation)
CalcAdvancement = "Error"
Exit Function
End If

'Check to see if a value has been entered for the override totals
' If none, use default totals

If AdvancementA = "" Then
overrideamount = Worksheets("Table").Range("j18").Value
CalcAdvancement = overrideamount
'If override totals have been entered, use those
Else
overrideamount = Worksheets("Table").Range("v18").Value

'Verify that the value is numeric
If IsNumeric(Worksheets("front page").Range("BC34").Value) = False
Then
'If is not numeric, throw warning, set to error condition and abort
function
msg = MsgBox("Please enter a numeric value!", vbExclamation)
CalcAdvancement = "ERROR"
Exit Function

End If


'if all is well set the function to the override amount and end the
condition
CalcAdvancement = overrideamount
End If


End If
****************************************************************************
*****************************

The check """if ax = "x" Then""" is repeated for every value like if bx=x
then, if cx=x then - then the entire above section of code is repeated below
it with just different cell values.


So to sum up, basically, I have a spreadsheet with functions which have
similar code sets but are being repeated. I am trying to shorten the code
using maybe modularized logic (arrays to pass values of cells, ranges, etc),
a single function which can be called and parameterized to accept all the
different range values and validate and so on.

Can anyone give me some ideas as to how best to achieve all this.

Thanks in advance

 

[Jonathan West]

Hi Rahul

This group deals with VBA in Word. The Excel VBA experts hang out at
microsoft.public.excel.programming. For an authoritative reply, I suggest
you re-post there.