Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please suggest any formulae / functions | Excel Discussion (Misc queries) | |||
Removing all formulae/functions/codes from previously saved worksh | Excel Discussion (Misc queries) | |||
Two questions on worksheet protection and hiding formulae/ functions | Excel Programming | |||
Nested functions in conditional formatting formulae | Excel Discussion (Misc queries) | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions |