If you want to write a general purpose routine on par with native Excel functions, you have to invest a fair amount of resources in the issue. Posting each and every problem you run into means you want to develop a function not by yourself but rather through the collective brainpower of this forum. {grin} While there's nothing wrong with that you might want to more clearly define your goals.
As for your specific problem with strings, you have to do a lot more than just worry about adding quotes. You have to identify a number (and a number can be one of many different entities including in exponent form or a percentage value), a boolean, a defined name or formula, and then, of course, a string.
If I were to undertake a function such as this I'd address a weakness, or at least what I perceive as a weakness, in Microsoft's approach. Build the function with the signature
ConcatenateIf(Rng-to-concatenate, Condition, optional-delim=",")
Condition is not something applied to range-to-concatenate, but a standalone condition. This will allow you to include Excel functions or any other complex constructs.
For an example of how one would use this consider an array formula type construct
=ConcatenateIf(A1:A10,"(A1:A10>1)+(B1:B10<10)"),chr(13))
If all one wants is to see if A1:A10 is a literal starting with joe, use
=ConcatenateIf(A1:A10,"A1:A10='joe*'")
So, essentially, a string will be enclosed in single quotes (and a real single quote will be escaped, say as two single quotes).
In addition to providing more flexibility, this approach alleviates the need for the function to parse the Condition string to identify numbers, booleans, names, and strings.
These are working great for numerical values, but it is failing for when it has to do a string comparison.
Eg:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Value</td><td style=";">Condition Value</td><td style=";">Formula Return</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">joe</td><td style="text-align: right;;">1</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">joe</td><td style=";">joe</td><td style="text-align: right;;">#NAME?</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=operatortest(<font color="Blue">A2,"="&B2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=operatortest(<font color="Blue">A3,">"&B3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=operatortest(<font color="Blue">A4,"<>"&B4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=operatortest(<font color="Blue">A5,"="&B5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=operatortest(<font color="Blue">A6,"="&B6</font>)</td></tr></tbody></table></td></tr></table><br />
Code using to test:
Code:
Public Function operatortest(rng As Range, teststr As String)
operatortest = rng.Worksheet.Evaluate(rng.Address & teststr)
End Function
)
