Hi. I'm writing a VBA program to assist in writing Crystal Report Formulae. Basically, I want to avoid writing the same piece of code several times when all that is needed is to substitute a different field or value.
An example of this is working out the number of students enrolled on the first day of each month. You need 12 formulae, one for each month, but the basic structure of the formula is the same in each one, just the date is different.
This program doesn't need to be fancy as I'm likely to be the only person using it, I just like writing things that make my job easier.
Right. So what's the problem? Basically, you enter the formula structure into a box with wildcard characters, e.g. '$$$' which will be substituted for a value when the code is run. The problem is that I am allowing for 3 different wildcards ($$$, £££ and ###), and each of these can appear several times during the code. Let me give an example:
The following is a formula that checks if the date for which the number of enrolments displayed is in the past or future. If it is in the past, it prints out the number. If it is in the future, it prints "---":
if ({@Y2_Date_April} > CurrentDate)
then "---"
else ToText (Sum ({@Y2_Enrolled_April}, {modstart.Code}), 0)
There are two variables here: Y2 and April. (Y2 is the second year of a course). In the structure box, you would type:
if ({@$$$_Date_£££} > CurrentDate)
then "---"
else ToText (Sum ({@$$$_Enrolled_£££}, {modstart.Code}), 0)
When the code is run, it would substitute "$$$" for "Y2" and "£££" for "April", with the basic premise that instead of having to type this code out each time, or cut and paste and then modify the values manually, I just enter "May" or "Y1" or whatever, click the button and the code is created for me.
So. How would I go about searching the string for the wildcards, and then how would I substitute the values in? I am aware of how to use InStr() and Mid() and the other similar functions. It's the actual logic behind the program that is escaping me!
Thanks.
An example of this is working out the number of students enrolled on the first day of each month. You need 12 formulae, one for each month, but the basic structure of the formula is the same in each one, just the date is different.
This program doesn't need to be fancy as I'm likely to be the only person using it, I just like writing things that make my job easier.
Right. So what's the problem? Basically, you enter the formula structure into a box with wildcard characters, e.g. '$$$' which will be substituted for a value when the code is run. The problem is that I am allowing for 3 different wildcards ($$$, £££ and ###), and each of these can appear several times during the code. Let me give an example:
The following is a formula that checks if the date for which the number of enrolments displayed is in the past or future. If it is in the past, it prints out the number. If it is in the future, it prints "---":
if ({@Y2_Date_April} > CurrentDate)
then "---"
else ToText (Sum ({@Y2_Enrolled_April}, {modstart.Code}), 0)
There are two variables here: Y2 and April. (Y2 is the second year of a course). In the structure box, you would type:
if ({@$$$_Date_£££} > CurrentDate)
then "---"
else ToText (Sum ({@$$$_Enrolled_£££}, {modstart.Code}), 0)
When the code is run, it would substitute "$$$" for "Y2" and "£££" for "April", with the basic premise that instead of having to type this code out each time, or cut and paste and then modify the values manually, I just enter "May" or "Y1" or whatever, click the button and the code is created for me.
So. How would I go about searching the string for the wildcards, and then how would I substitute the values in? I am aware of how to use InStr() and Mid() and the other similar functions. It's the actual logic behind the program that is escaping me!
Thanks.
