Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variant vs Double for functions where data could be "null"
I am creating a number of calculation functions which will exist
within a class object. The properties used in the function may not have been given a value so I need an efficient way to determine this and acting accordingly. I can see about 3 options - all with drawbacks - so am hoping someone could give me some guidance on which is good practice and of course if there is a better alternative! I wonder if vb.net offers any insights given that it doesn't support variants? My options a 1) Declare variables as variant and initialise to Null. Now I can simply carry out the calculation and if any of the variables is null the function will return Null. Very straight foward but I would normally steer away from using variants if possible. 2) Declare variables as double and initialise to a value representing no value (eg. -999) Now I can test each of the variables before doing the calculation. This itself seems to be somewhat of an overhead though and also means that when I call other properties (which are themselves calculations) I need to pass them first to a variable and test them rather than using them directly in my function. If I could use zero as my "no value" constant it would work in much the same way as Null for variants but for division would give an error. 3) Create my own variable type as follows Type Number val as double hasval as integer '0 if no, 1 if yes end Type Now I can multiply .hasval for each property and if any of them are 0 it will return 0. Very interested to hear how others have handled this issue since it must have come up before. Thanks a lot, Andrew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variant vs Double for functions where data could be "null"
Where is the data coming from that is passed to the class?
Maybe it better to validate the data before being passed to that class as you are then closer to the source and can test more appropiately/specific. In general there is a bit more information needed about what exactly you are doing. RBS "Andrew" wrote in message ... I am creating a number of calculation functions which will exist within a class object. The properties used in the function may not have been given a value so I need an efficient way to determine this and acting accordingly. I can see about 3 options - all with drawbacks - so am hoping someone could give me some guidance on which is good practice and of course if there is a better alternative! I wonder if vb.net offers any insights given that it doesn't support variants? My options a 1) Declare variables as variant and initialise to Null. Now I can simply carry out the calculation and if any of the variables is null the function will return Null. Very straight foward but I would normally steer away from using variants if possible. 2) Declare variables as double and initialise to a value representing no value (eg. -999) Now I can test each of the variables before doing the calculation. This itself seems to be somewhat of an overhead though and also means that when I call other properties (which are themselves calculations) I need to pass them first to a variable and test them rather than using them directly in my function. If I could use zero as my "no value" constant it would work in much the same way as Null for variants but for division would give an error. 3) Create my own variable type as follows Type Number val as double hasval as integer '0 if no, 1 if yes end Type Now I can multiply .hasval for each property and if any of them are 0 it will return 0. Very interested to hear how others have handled this issue since it must have come up before. Thanks a lot, Andrew |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variant vs Double for functions where data could be "null"
The data will normally be coming from a userform or XL itself. The
idea is that the class will calculate what it can based on the inputs it has. A "null" entry is valid and just means that value is unknown at the moment - but if a function needs that value for its calculation then it should also return some form of "null". For example, the class is able to calculate density of a fluid but only if the fluid composition, temperature and pressure is known. Other properties however need less inputs in order to solve. In the userform case, entering one value (eg. pressure) should display any other values which can now be calculated. I could try and keep track of what is or isn't known as it is passed to the class (which gets a bit complicated) or just attempt the calculation using one of the approaches already mentioned and return some sort of null value if it can't solve. Is that any clearer or more confusing? Cheers, Andrew On Feb 17, 1:52*pm, "RB Smissaert" wrote: Where is the data coming from that is passed to the class? Maybe it better to validate the data before being passed to that class as you are then closer to the source and can test more appropiately/specific. In general there is a bit more information needed about what exactly you are doing. RBS "Andrew" wrote in message ... I am creating a number of calculation functions which will exist within a class object. *The properties used in the function may not have been given a value so I need an efficient way to determine this and acting accordingly. *I can see about 3 options - all with drawbacks - so am hoping someone could give me some guidance on which is good practice and of course if there is a better alternative! *I wonder if vb.net offers any insights given that it doesn't support variants? *My options a 1) Declare variables as variant and initialise to Null. Now I can simply carry out the calculation and if any of the variables is null the function will return Null. *Very straight foward but I would normally steer away from using variants if possible. 2) Declare variables as double and initialise to a value representing no value (eg. -999) Now I can test each of the variables before doing the calculation. This itself seems to be somewhat of an overhead though and also means that when I call other properties (which are themselves calculations) I need to pass them first to a variable and test them rather than using them directly in my function. *If I could use zero as my "no value" constant it would work in much the same way as Null for variants but for division would give an error. 3) Create my own variable type as follows Type Number *val as double *hasval as integer * *'0 if no, 1 if yes end Type Now I can multiply .hasval for each property and if any of them are 0 it will return 0. Very interested to hear how others have handled this issue since it must have come up before. Thanks a lot, Andrew- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variant vs Double for functions where data could be "null"
Doesn't make it much clearer to me, but unless speed is an issue then maybe
variants are the best option. RBS "Andrew" wrote in message ... The data will normally be coming from a userform or XL itself. The idea is that the class will calculate what it can based on the inputs it has. A "null" entry is valid and just means that value is unknown at the moment - but if a function needs that value for its calculation then it should also return some form of "null". For example, the class is able to calculate density of a fluid but only if the fluid composition, temperature and pressure is known. Other properties however need less inputs in order to solve. In the userform case, entering one value (eg. pressure) should display any other values which can now be calculated. I could try and keep track of what is or isn't known as it is passed to the class (which gets a bit complicated) or just attempt the calculation using one of the approaches already mentioned and return some sort of null value if it can't solve. Is that any clearer or more confusing? Cheers, Andrew On Feb 17, 1:52 pm, "RB Smissaert" wrote: Where is the data coming from that is passed to the class? Maybe it better to validate the data before being passed to that class as you are then closer to the source and can test more appropiately/specific. In general there is a bit more information needed about what exactly you are doing. RBS "Andrew" wrote in message ... I am creating a number of calculation functions which will exist within a class object. The properties used in the function may not have been given a value so I need an efficient way to determine this and acting accordingly. I can see about 3 options - all with drawbacks - so am hoping someone could give me some guidance on which is good practice and of course if there is a better alternative! I wonder if vb.net offers any insights given that it doesn't support variants? My options a 1) Declare variables as variant and initialise to Null. Now I can simply carry out the calculation and if any of the variables is null the function will return Null. Very straight foward but I would normally steer away from using variants if possible. 2) Declare variables as double and initialise to a value representing no value (eg. -999) Now I can test each of the variables before doing the calculation. This itself seems to be somewhat of an overhead though and also means that when I call other properties (which are themselves calculations) I need to pass them first to a variable and test them rather than using them directly in my function. If I could use zero as my "no value" constant it would work in much the same way as Null for variants but for division would give an error. 3) Create my own variable type as follows Type Number val as double hasval as integer '0 if no, 1 if yes end Type Now I can multiply .hasval for each property and if any of them are 0 it will return 0. Very interested to hear how others have handled this issue since it must have come up before. Thanks a lot, Andrew- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variant vs Double for functions where data could be "null"
Maybe another way of looking at is is that I am trying to make my
class object fully "encapsulated". That is, the calculations it performs could be attached to yet undefined interfaces. As a result I can't rely on validation at the input source. Thanks again. On 17 Feb, 16:48, "RB Smissaert" wrote: Doesn't make it much clearer to me, but unless speed is an issue then maybe variants are the best option. RBS "Andrew" wrote in message ... The data will normally be coming from a userform or XL itself. *The idea is that the class will calculate what it can based on the inputs it has. *A "null" entry is valid and just means that value is unknown at the moment - but if a function needs that value for its calculation then it should also return some form of "null". *For example, the class is able to calculate density of a fluid but only if the fluid composition, temperature and pressure is known. *Other properties however need less inputs in order to solve. * In the userform case, entering one value (eg. pressure) should display any other values which can now be calculated. *I could try and keep track of what is or isn't known as it is passed to the class (which gets a bit complicated) or just attempt the calculation using one of the approaches already mentioned and return some sort of null value if it can't solve. Is that any clearer or more confusing? Cheers, Andrew On Feb 17, 1:52 pm, "RB Smissaert" wrote: Where is the data coming from that is passed to the class? Maybe it better to validate the data before being passed to that class as you are then closer to the source and can test more appropiately/specific. In general there is a bit more information needed about what exactly you are doing. RBS "Andrew" wrote in message ... I am creating a number of calculation functions which will exist within a class object. The properties used in the function may not have been given a value so I need an efficient way to determine this and acting accordingly. I can see about 3 options - all with drawbacks - so am hoping someone could give me some guidance on which is good practice and of course if there is a better alternative! I wonder if vb.net offers any insights given that it doesn't support variants? My options a 1) Declare variables as variant and initialise to Null. Now I can simply carry out the calculation and if any of the variables is null the function will return Null. Very straight foward but I would normally steer away from using variants if possible. 2) Declare variables as double and initialise to a value representing no value (eg. -999) Now I can test each of the variables before doing the calculation. This itself seems to be somewhat of an overhead though and also means that when I call other properties (which are themselves calculations) I need to pass them first to a variable and test them rather than using them directly in my function. If I could use zero as my "no value" constant it would work in much the same way as Null for variants but for division would give an error. 3) Create my own variable type as follows Type Number val as double hasval as integer '0 if no, 1 if yes end Type Now I can multiply .hasval for each property and if any of them are 0 it will return 0. Very interested to hear how others have handled this issue since it must have come up before. Thanks a lot, Andrew- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variant vs Double for functions where data could be "null"
OK, more reason then perhaps to go with variants.
RBS "Andrew" wrote in message ... Maybe another way of looking at is is that I am trying to make my class object fully "encapsulated". That is, the calculations it performs could be attached to yet undefined interfaces. As a result I can't rely on validation at the input source. Thanks again. On 17 Feb, 16:48, "RB Smissaert" wrote: Doesn't make it much clearer to me, but unless speed is an issue then maybe variants are the best option. RBS "Andrew" wrote in message ... The data will normally be coming from a userform or XL itself. The idea is that the class will calculate what it can based on the inputs it has. A "null" entry is valid and just means that value is unknown at the moment - but if a function needs that value for its calculation then it should also return some form of "null". For example, the class is able to calculate density of a fluid but only if the fluid composition, temperature and pressure is known. Other properties however need less inputs in order to solve. In the userform case, entering one value (eg. pressure) should display any other values which can now be calculated. I could try and keep track of what is or isn't known as it is passed to the class (which gets a bit complicated) or just attempt the calculation using one of the approaches already mentioned and return some sort of null value if it can't solve. Is that any clearer or more confusing? Cheers, Andrew On Feb 17, 1:52 pm, "RB Smissaert" wrote: Where is the data coming from that is passed to the class? Maybe it better to validate the data before being passed to that class as you are then closer to the source and can test more appropiately/specific. In general there is a bit more information needed about what exactly you are doing. RBS "Andrew" wrote in message ... I am creating a number of calculation functions which will exist within a class object. The properties used in the function may not have been given a value so I need an efficient way to determine this and acting accordingly. I can see about 3 options - all with drawbacks - so am hoping someone could give me some guidance on which is good practice and of course if there is a better alternative! I wonder if vb.net offers any insights given that it doesn't support variants? My options a 1) Declare variables as variant and initialise to Null. Now I can simply carry out the calculation and if any of the variables is null the function will return Null. Very straight foward but I would normally steer away from using variants if possible. 2) Declare variables as double and initialise to a value representing no value (eg. -999) Now I can test each of the variables before doing the calculation. This itself seems to be somewhat of an overhead though and also means that when I call other properties (which are themselves calculations) I need to pass them first to a variable and test them rather than using them directly in my function. If I could use zero as my "no value" constant it would work in much the same way as Null for variants but for division would give an error. 3) Create my own variable type as follows Type Number val as double hasval as integer '0 if no, 1 if yes end Type Now I can multiply .hasval for each property and if any of them are 0 it will return 0. Very interested to hear how others have handled this issue since it must have come up before. Thanks a lot, Andrew- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
Null, "null", vbNull, vbNullString, vbEmpty | Excel Programming | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
cells formatted to tick when text value "Y" if or null if "N" | Excel Discussion (Misc queries) | |||
"IF"- "THEN" type Formula based on Null value | Excel Worksheet Functions |