VBA macros in 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

 

[Jim Cone]

Rahul,

Here is an example of how it might be structured.
However, it looks as if it will take a lot more work.
Jim Cone
San Francisco, USA

'-------------------------
Function CalcAdvancement(AdvancementA, AdvancementB, AdvancementC, _
AdvancementD, AdvancementE, Advancementf, AdvancementH, AdvancementJ, _
AdvancementK, AdvancementL, AdvancementS, AdvancementU)
Dim rngX As Excel.Range
Dim lngN As Long
Dim msg As String
Dim overrideAmount As Double.
Dim bFundSelected As Boolean
Set rngX = Range("A2:A16")

overrideAmount = 0
'Instanciate the global variables to false
bFundSelected = False

'* Retireve the status of the Section Selection Fields
For lngN = 1 To 15
If rngX(lngN).Value = "x" Then
'Check if another fund has already been selected
If bFundSelected = False Then
bFundSelected = True
Else
'If fund was selected show warning and set totals to an ERROR state.
'Exit the Function immeadiatly
msg = MsgBox("More than one section has been selected, " & vbCr & _
"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, show warning, set to error condition and abort.
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
Next 'lngN
End Function
'--------------------------------



"Rahul Chatterjee" <[email protected]>
wrote in message