asked on
Would it be possible to change the parameters in a function, and to "hardcode"a value in a function.
Would it be possible to change the parameters in a function, and to "hardcode"a value in a function.
In the atttached file the Bilinear interpolation is done via the InterpolateCXY function.
In the example I created a xc and yc cell. As you can see the yc value is 15. However in the ycvalues there is no value 15. I can point to the yc part of the function.
Therefore I tried to get the avarage of the value above and below to get to 15. Would it be possible to "hardcode" 15 in the function so I do not have to point to for example to cell R5?
Interpolate-test.xlsm
In the atttached file the Bilinear interpolation is done via the InterpolateCXY function.
In the example I created a xc and yc cell. As you can see the yc value is 15. However in the ycvalues there is no value 15. I can point to the yc part of the function.
Therefore I tried to get the avarage of the value above and below to get to 15. Would it be possible to "hardcode" 15 in the function so I do not have to point to for example to cell R5?
Interpolate-test.xlsm
VBAMicrosoft ExcelMicrosoft Office
Last Comment
faraoosiris
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Well I think by putting your average function in YC in cell R5 instead of a value you get what you want.
Check the file
GOwflow
Interpolate-test--3-.xlsm
Check the file
GOwflow
Interpolate-test--3-.xlsm
ASKER
Thanks for the rapid respons to my question
@garlin007 I will try your suggestion this evening.
@gowflow Your suggestion would be my last suggestion. I will explain. The attached file is a excel version of a part of an official document.
I would like to keep the layout as close as possible to the original and lend it credibility. Furthermore if an update would be
necessary I don't want someone to change eg R5.
@garlin007 I will try your suggestion this evening.
@gowflow Your suggestion would be my last suggestion. I will explain. The attached file is a excel version of a part of an official document.
I would like to keep the layout as close as possible to the original and lend it credibility. Furthermore if an update would be
necessary I don't want someone to change eg R5.
I don't want someone to change eg R5.No problem you protect this cell and any other cell you want to protect and put a password on the document and this way no one can change it. I am ok at VBA but this function is unknown to me so do not want to venture into playing with the code.
Gowflow
Sometimes I have a workbook that needs constants and formulas that I don't want to mess up the reports on the main workbook tabs. For example, it could be the as of date for the data, the u.s. exchange rate, or whether I want the monthly quarterly or annual version of the report, Etc. To do this I create a new sheet named "Resources" and I use the first three columns to store the values and formulas that I need. In the first column I label the value, in the second column I enter the value or formula, and in the third column I'll add some notes like (use 1 for monthly, 3 for quarterly, or 12 for annual). I also name each value cell so when I refer to them in formulas on the other sheets I can use a name instead of a cell reference.
On the Resources sheet I also typically have sections with notes and procedures for how to use the workbook. A typical note might be to define which U.. exchange rate to use and where it can be found on the www. Typical procedure titles might be "Month-End Procedure" or "How to Add a New Asset". Not only do these notes help the person who uses the workbook on a regular basis, but they help me if I need to update the functionality of the workbook.
In your case, I suggest that you add a Resources sheet to hide the formulas you don't want the users to tamper with. Also I recommend that you name the ranges containing the source data for these formulas so that when you refer to the source data on the Resources tab you can use names instead of cell references. This makes it much easier to understand the formulas on the Resources tab. Also, it is more robust in case the layout on the main tab changes, since often all you need to do is make sure that the names for the ranges are still valid.
On the Resources sheet I also typically have sections with notes and procedures for how to use the workbook. A typical note might be to define which U.. exchange rate to use and where it can be found on the www. Typical procedure titles might be "Month-End Procedure" or "How to Add a New Asset". Not only do these notes help the person who uses the workbook on a regular basis, but they help me if I need to update the functionality of the workbook.
In your case, I suggest that you add a Resources sheet to hide the formulas you don't want the users to tamper with. Also I recommend that you name the ranges containing the source data for these formulas so that when you refer to the source data on the Resources tab you can use names instead of cell references. This makes it much easier to understand the formulas on the Resources tab. Also, it is more robust in case the layout on the main tab changes, since often all you need to do is make sure that the names for the ranges are still valid.
ASKER
Good afternoon
@ gowflow Thank you for participating in the solution of my question.
@ Gary Berjamin. I marked your input as being the solution. I made a copy of the function especially for that page and changed the parameter to variant as per your suggestion. It also excepts range notation as well as the "hardcoded"value and keeps the sheet "clean".
@ gowflow Thank you for participating in the solution of my question.
@ Gary Berjamin. I marked your input as being the solution. I made a copy of the function especially for that page and changed the parameter to variant as per your suggestion. It also excepts range notation as well as the "hardcoded"value and keeps the sheet "clean".
Gowflow