Excel formulae to VBA functions

[Kieranz]

Hi all, I have the following excel formulae in Cell T10:
" =If (and(or(J10="",istext(J10)),K10=""),"",Sum(K10:S10)) "
which i would like to convert to a VBA function. The function would
then be copied down 200 rows in Column T.
Your help appreciated. Using XP with XL2003 still newbie but enjoying.
Rgds
KZ

 

[Peter T]

Dim sFla As String
sFla = "=IF(AND(OR(J10="""",ISTEXT(J10)),K10=""""),"""",SUM(K10:S10))"
Range("T10:T209").Formula = sFla

Regards,
Peter T

 

[Kieranz]

Dim sFla As String
sFla = "=IF(AND(OR(J10="""",ISTEXT(J10)),K10=""""),"""",SUM(K10:S10))"
Range("T10:T209").Formula = sFla

Regards,
Peter T

Hi Peter
Tried the above which is a sub procedure that copies the formulae to
the range T10 to T200, what i wanted was a function so that the
formulae is not visible and therefore temperproof.
Many thks
Rgds
KZ

 

[Ivyleaf]

Hi Peter
Tried the above which is a sub procedure that copies the formulae to
the range T10 to T200, what i wanted was a function so that the
formulae is not visible and therefore temperproof.
Many thks
Rgds
KZ- Hide quoted text -

- Show quoted text -

Hi Kieranz,

This should do it:

Function IFSUM(CellOne As Range, CellTwo As Range, _
SumRng As Range) As Variant

If (CellOne = vbNullString Or Application.IsText(CellOne)) _
And CellTwo = vbNullString Then _
IFSUM = "" Else IFSUM = Application.Sum(SumRng)

End Function

Use as: =IFSUM(J10,K10,K10:S10)

Cheers,
Ivan.

 

[Peter T]

Hi Peter
Tried the above which is a sub procedure that copies the formulae to
the range T10 to T200, what i wanted was a function so that the
formulae is not visible and therefore temperproof.
Many thks
Rgds
KZ

A UDF to perform that conditional sum in 200 cells on every re-calc will be
incredibly inefficient compared to normal worksheet formula. User will still
be able to see the formula, something like =myUDFsum(), and tamper with it
(albeit might not know how the UDF works).

If you really want to go some way at least to prevent both viewing and
tampering with the formula protect and hide formulas. Select all cells,
format, protection - uncheck 'Locked'. Now select the cells you want
protecting with hidden formulas, check both Locked and Hidden. Protect the
sheet.

Another way with VBA would be in a worksheet change event, only process
formulas that'll need updating. Advantages - all hidden from the User
without sheet protection(subject not opening the VBA project), probably
faster than a UDF. Disadvantages, lot more code to write and maintain, loss
of undo if value(s) change.

Regards,
Peter T

 

[Kieranz]

A UDF to perform that conditional sum in 200 cells on every re-calc will be
incredibly inefficient compared to normal worksheet formula. User will still
be able to see the formula, something like =myUDFsum(), and tamper with it
(albeit might not know how the UDF works).

If you really want to go some way at least to prevent both viewing and
tampering with the formula protect and hide formulas. Select all cells,
format, protection - uncheck 'Locked'. Now select the cells you want
protecting with hidden formulas, check both Locked and Hidden. Protect the
sheet.

Another way with VBA would be in a worksheet change event, only process
formulas that'll need updating. Advantages - all hidden from the User
without sheet protection(subject not opening the VBA project), probably
faster than a UDF. Disadvantages, lot more code to write and maintain, loss
of undo if value(s) change.

Regards,
Peter T

Many thks to both of you, Ivan and Peter. I will try both your
suggestions.
Again thks a mill.
Rgds
KZ