I can't find the answer anywhere, so I try to explain my problem here:
We have an XLA-file that gets updated each year, and gets a new name:
file_06.xla
file_07.xla
file_08.xla
...
Each file contains the same macros/functions, but with different names:
calculateA_06(var1,var2) inside file_06.xla
calculateA_07(var1,var2) inside file_07.xla
calculateA_08(var1,var2) inside file_08.xla
...
It is necessary to keep this file structure and naming structure, because we need to use the older xla's together with the new xla's.
But, if we use these functions inside a workbook, we need to be really careful to use the correct functions. If I want to calculate a 2007-case, I need to use calculateA_07(var1,var2)!
I am now searching to find a general solution for this 'problem', and I was thinking to do something like this:
- Make 1 cell on the sheet, containing the year: '06' or '07' or '08'
- create a new function: calculateA(var1,var2,YEAR), without a year reference inside the name
- let this new function reassamble the correct function name:
execute "result = calculateA_" & YEAR & "(var1,var2)"
But I cannot find a way to do this dynamically.
OK, I can do a simple "if YEAR = 06 then ..."
But I was thinking to make it more intelligent, avoiding a moment in the future where nobody remembers to add some years in the code...
Is this possible in Excel? (some of us are using excel2007, others use excelXP so it should be a general solution)
We have an XLA-file that gets updated each year, and gets a new name:
file_06.xla
file_07.xla
file_08.xla
...
Each file contains the same macros/functions, but with different names:
calculateA_06(var1,var2) inside file_06.xla
calculateA_07(var1,var2) inside file_07.xla
calculateA_08(var1,var2) inside file_08.xla
...
It is necessary to keep this file structure and naming structure, because we need to use the older xla's together with the new xla's.
But, if we use these functions inside a workbook, we need to be really careful to use the correct functions. If I want to calculate a 2007-case, I need to use calculateA_07(var1,var2)!
I am now searching to find a general solution for this 'problem', and I was thinking to do something like this:
- Make 1 cell on the sheet, containing the year: '06' or '07' or '08'
- create a new function: calculateA(var1,var2,YEAR), without a year reference inside the name
- let this new function reassamble the correct function name:
execute "result = calculateA_" & YEAR & "(var1,var2)"
But I cannot find a way to do this dynamically.
OK, I can do a simple "if YEAR = 06 then ..."
But I was thinking to make it more intelligent, avoiding a moment in the future where nobody remembers to add some years in the code...
Is this possible in Excel? (some of us are using excel2007, others use excelXP so it should be a general solution)