Excel formulae to VBA functions
 

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

Excel formulae to VBA functions
 

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

Regards,
Peter T

"Kieranz" wrote in message
...
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

Excel formulae to VBA functions
 

On Apr 3, 12:14 pm, "Peter T" <peter_t@discussions wrote:
Dim sFla As String
sFla = "=IF(AND(OR(J10="""",ISTEXT(J10)),K10=""""),"""",S UM(K10:S10))"
Range("T10:T209").Formula = sFla

Regards,
Peter T

"Kieranz" wrote in message

...

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

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

Excel formulae to VBA functions
 

On Apr 3, 9:45*pm, Kieranz wrote:
On Apr 3, 12:14 pm, "Peter T" <peter_t@discussions wrote:





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

Regards,
Peter T

"Kieranz" wrote in message

...

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

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.

Excel formulae to VBA functions
 

"Kieranz" wrote in message
...
On Apr 3, 12:14 pm, "Peter T" <peter_t@discussions wrote:
Dim sFla As String
sFla = "=IF(AND(OR(J10="""",ISTEXT(J10)),K10=""""),"""",S UM(K10:S10))"
Range("T10:T209").Formula = sFla

Regards,
Peter T

"Kieranz" wrote in message


...

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

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

Excel formulae to VBA functions
 

On Apr 3, 3:16 pm, "Peter T" <peter_t@discussions wrote:
"Kieranz" wrote in message

... On Apr 3, 12:14 pm, "Peter T" <peter_t@discussions wrote:
Dim sFla As String
sFla = "=IF(AND(OR(J10="""",ISTEXT(J10)),K10=""""),"""",S UM(K10:S10))"
Range("T10:T209").Formula = sFla

Regards,
Peter T

"Kieranz" wrote in message

...





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

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

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