Randall00
Board Regular
- Joined
- Jan 31, 2008
- Messages
- 54
Like any good topic on MrExcel, this one begins with me pulling my hair out in frustration!
I've constructed a fairly straightforward Index/Match formula that involves a couple of columns within Tables in Excel's main interface that looks something like this:
The Evaluate function of VBA can take a text string and calculate the result, but because my formula contains a text string, I was advised that it I would be better off using the shorthand form of the Evaluate function...that is, to encase the formula in square brackets like this:
This works pretty well, but there's a catch: the reason I brought the formula into VBA in the first place is because the text string within the formula needs to be a string variable that concatenates the (in this case) name of an arena with the day of the week.
In trying to get this to work, I created a variable called strConc that combines the two desire text strings into one and tried to incorporate it into the function. Lo and behold, I've managed to confuse both Excel and myself in trying to communicate exactly which part is a string to be evaluated and which part isn't.
All of the following attempts result in errors:
I've tried a few other things as well, but I've now buried myself in a deep misunderstanding of what these functions are doing.
There's a lot of information out there on how to incorporate quotation marks within text strings, but none seem to be related to the Evaluate function and since it's evaluating a text string right from the beginning, I can't exactly just stick a bunch of Chr(34)'s in there and fix it. Nor does it work if I try and declare the entire formula text as a single string variable and Evaluate that.
Incidentally, the only thing that seems to work is to put the the value of the strConc variable into a cell and then reference the cell address within the square brackets of the VBA function (which stands to reason because it's evaluating a worksheet function). However, I fear this little misadventure is already getting to dangerous levels of scope creep and I thought I'd ask if anyone knew anything about the way strings are handled in these two different Evaluate functions before investing too much time into a workaround for a workaround for a workaround, if you know what I mean. I would think there must be some way to do this.
I've constructed a fairly straightforward Index/Match formula that involves a couple of columns within Tables in Excel's main interface that looks something like this:
Code:
=INDEX(A_OpenClose[Total Open Hours], MATCH("Central ArenaMonday", A_OpenClose[CONC],0))The Evaluate function of VBA can take a text string and calculate the result, but because my formula contains a text string, I was advised that it I would be better off using the shorthand form of the Evaluate function...that is, to encase the formula in square brackets like this:
Code:
[INDEX(A_OpenClose[Total Open Hours], MATCH("Central ArenaMonday", A_OpenClose[CONC],0))]This works pretty well, but there's a catch: the reason I brought the formula into VBA in the first place is because the text string within the formula needs to be a string variable that concatenates the (in this case) name of an arena with the day of the week.
In trying to get this to work, I created a variable called strConc that combines the two desire text strings into one and tried to incorporate it into the function. Lo and behold, I've managed to confuse both Excel and myself in trying to communicate exactly which part is a string to be evaluated and which part isn't.
All of the following attempts result in errors:
Code:
strConc = strArenaName & strWeekday
[INDEX(A_OpenClose[Total Open Hours], MATCH(strConc, A_OpenClose[CONC],0))]
[INDEX(A_OpenClose[Total Open Hours], MATCH("strConc", A_OpenClose[CONC],0))]
Evaluate("INDEX(A_OpenClose[Total Open Hours], MATCH(strConc, A_OpenClose[CONC],0))")
Evaluate("INDEX(A_OpenClose[Total Open Hours], MATCH(" & strConc & ", A_OpenClose[CONC],0))")I've tried a few other things as well, but I've now buried myself in a deep misunderstanding of what these functions are doing.
There's a lot of information out there on how to incorporate quotation marks within text strings, but none seem to be related to the Evaluate function and since it's evaluating a text string right from the beginning, I can't exactly just stick a bunch of Chr(34)'s in there and fix it. Nor does it work if I try and declare the entire formula text as a single string variable and Evaluate that.
Incidentally, the only thing that seems to work is to put the the value of the strConc variable into a cell and then reference the cell address within the square brackets of the VBA function (which stands to reason because it's evaluating a worksheet function). However, I fear this little misadventure is already getting to dangerous levels of scope creep and I thought I'd ask if anyone knew anything about the way strings are handled in these two different Evaluate functions before investing too much time into a workaround for a workaround for a workaround, if you know what I mean. I would think there must be some way to do this.
