Search formula for cell references in VBA
Within VBA, I want to search an excel formula (String) to find cell references.
Specifically I want to find the position(s) in the string where there is either a relative cell reference (ANY relative cell reference, not a specific one), or a mixed cell reference.
- I don't need to find absolute cell references, though I can just check and ignore those.
- I don't mind if the reference is on a different sheet, I'm happy to find the bit after the ! symbol.
- I will need to make sure that it doesn't find things that look like cell references in strings or sheet names or data names.
How can I do this? (This is the main question)
My thoughts:
I can see how I would find mixed cell references - I'm pretty sure the $ symbol can only ever come up in either mixed cell references, absolute references, inside sheet names, or inside strings (can anyone confirm that?), so with the right regular expression or algorithm you could find all those cases and then just check if it is an absolute cell reference and ignore that.
But how to find any and all relative cell references? The only idea I have is similar to the above. Beyond cell references, are there ever any times in Excel Formulas (outside of sheet names or inside strings) where there is a number following a letter? Any formula names or anything? The only other one I can think of is in a defined data name, but I'm not sure if you can use those in a formula, especially a Conditional Formatting formula). Can anyone think of any other times?
Anyone have any ideas?
excel vba string formula
|
show 8 more comments
Within VBA, I want to search an excel formula (String) to find cell references.
Specifically I want to find the position(s) in the string where there is either a relative cell reference (ANY relative cell reference, not a specific one), or a mixed cell reference.
- I don't need to find absolute cell references, though I can just check and ignore those.
- I don't mind if the reference is on a different sheet, I'm happy to find the bit after the ! symbol.
- I will need to make sure that it doesn't find things that look like cell references in strings or sheet names or data names.
How can I do this? (This is the main question)
My thoughts:
I can see how I would find mixed cell references - I'm pretty sure the $ symbol can only ever come up in either mixed cell references, absolute references, inside sheet names, or inside strings (can anyone confirm that?), so with the right regular expression or algorithm you could find all those cases and then just check if it is an absolute cell reference and ignore that.
But how to find any and all relative cell references? The only idea I have is similar to the above. Beyond cell references, are there ever any times in Excel Formulas (outside of sheet names or inside strings) where there is a number following a letter? Any formula names or anything? The only other one I can think of is in a defined data name, but I'm not sure if you can use those in a formula, especially a Conditional Formatting formula). Can anyone think of any other times?
Anyone have any ideas?
excel vba string formula
4
Look intoRange.Precedents
.
– Mathieu Guindon
Nov 12 '18 at 15:22
2
Would you be interested in regex?
– Kubie
Nov 12 '18 at 15:33
Thanks Mathieu. I mentioned I have a formula string, that's because it's from a Conditional Format. Still, I might be able to place the formula in an empty cell, trace the precedents, get a list of cells that are in the formula, and then search specifically for those. It's an interesting idea, though a pretty complicated one. Thanks
– James Carlyle-Clarke
Nov 12 '18 at 15:56
Hi Kubie, yes, I would very much be interested in RegEx, though I think the points about how to search for any and all relative cell references is an important one, and I don't know whether my suggestion would work.
– James Carlyle-Clarke
Nov 12 '18 at 15:57
1
One comment about your comments. instead of writing "Hi #Name", consider using @Name at the beginning of your comment. In your case it might be that Kubie and Mathieu never read your comment
– Pierre44
Nov 12 '18 at 16:19
|
show 8 more comments
Within VBA, I want to search an excel formula (String) to find cell references.
Specifically I want to find the position(s) in the string where there is either a relative cell reference (ANY relative cell reference, not a specific one), or a mixed cell reference.
- I don't need to find absolute cell references, though I can just check and ignore those.
- I don't mind if the reference is on a different sheet, I'm happy to find the bit after the ! symbol.
- I will need to make sure that it doesn't find things that look like cell references in strings or sheet names or data names.
How can I do this? (This is the main question)
My thoughts:
I can see how I would find mixed cell references - I'm pretty sure the $ symbol can only ever come up in either mixed cell references, absolute references, inside sheet names, or inside strings (can anyone confirm that?), so with the right regular expression or algorithm you could find all those cases and then just check if it is an absolute cell reference and ignore that.
But how to find any and all relative cell references? The only idea I have is similar to the above. Beyond cell references, are there ever any times in Excel Formulas (outside of sheet names or inside strings) where there is a number following a letter? Any formula names or anything? The only other one I can think of is in a defined data name, but I'm not sure if you can use those in a formula, especially a Conditional Formatting formula). Can anyone think of any other times?
Anyone have any ideas?
excel vba string formula
Within VBA, I want to search an excel formula (String) to find cell references.
Specifically I want to find the position(s) in the string where there is either a relative cell reference (ANY relative cell reference, not a specific one), or a mixed cell reference.
- I don't need to find absolute cell references, though I can just check and ignore those.
- I don't mind if the reference is on a different sheet, I'm happy to find the bit after the ! symbol.
- I will need to make sure that it doesn't find things that look like cell references in strings or sheet names or data names.
How can I do this? (This is the main question)
My thoughts:
I can see how I would find mixed cell references - I'm pretty sure the $ symbol can only ever come up in either mixed cell references, absolute references, inside sheet names, or inside strings (can anyone confirm that?), so with the right regular expression or algorithm you could find all those cases and then just check if it is an absolute cell reference and ignore that.
But how to find any and all relative cell references? The only idea I have is similar to the above. Beyond cell references, are there ever any times in Excel Formulas (outside of sheet names or inside strings) where there is a number following a letter? Any formula names or anything? The only other one I can think of is in a defined data name, but I'm not sure if you can use those in a formula, especially a Conditional Formatting formula). Can anyone think of any other times?
Anyone have any ideas?
excel vba string formula
excel vba string formula
edited Nov 12 '18 at 16:15
asked Nov 12 '18 at 15:12
James Carlyle-Clarke
441415
441415
4
Look intoRange.Precedents
.
– Mathieu Guindon
Nov 12 '18 at 15:22
2
Would you be interested in regex?
– Kubie
Nov 12 '18 at 15:33
Thanks Mathieu. I mentioned I have a formula string, that's because it's from a Conditional Format. Still, I might be able to place the formula in an empty cell, trace the precedents, get a list of cells that are in the formula, and then search specifically for those. It's an interesting idea, though a pretty complicated one. Thanks
– James Carlyle-Clarke
Nov 12 '18 at 15:56
Hi Kubie, yes, I would very much be interested in RegEx, though I think the points about how to search for any and all relative cell references is an important one, and I don't know whether my suggestion would work.
– James Carlyle-Clarke
Nov 12 '18 at 15:57
1
One comment about your comments. instead of writing "Hi #Name", consider using @Name at the beginning of your comment. In your case it might be that Kubie and Mathieu never read your comment
– Pierre44
Nov 12 '18 at 16:19
|
show 8 more comments
4
Look intoRange.Precedents
.
– Mathieu Guindon
Nov 12 '18 at 15:22
2
Would you be interested in regex?
– Kubie
Nov 12 '18 at 15:33
Thanks Mathieu. I mentioned I have a formula string, that's because it's from a Conditional Format. Still, I might be able to place the formula in an empty cell, trace the precedents, get a list of cells that are in the formula, and then search specifically for those. It's an interesting idea, though a pretty complicated one. Thanks
– James Carlyle-Clarke
Nov 12 '18 at 15:56
Hi Kubie, yes, I would very much be interested in RegEx, though I think the points about how to search for any and all relative cell references is an important one, and I don't know whether my suggestion would work.
– James Carlyle-Clarke
Nov 12 '18 at 15:57
1
One comment about your comments. instead of writing "Hi #Name", consider using @Name at the beginning of your comment. In your case it might be that Kubie and Mathieu never read your comment
– Pierre44
Nov 12 '18 at 16:19
4
4
Look into
Range.Precedents
.– Mathieu Guindon
Nov 12 '18 at 15:22
Look into
Range.Precedents
.– Mathieu Guindon
Nov 12 '18 at 15:22
2
2
Would you be interested in regex?
– Kubie
Nov 12 '18 at 15:33
Would you be interested in regex?
– Kubie
Nov 12 '18 at 15:33
Thanks Mathieu. I mentioned I have a formula string, that's because it's from a Conditional Format. Still, I might be able to place the formula in an empty cell, trace the precedents, get a list of cells that are in the formula, and then search specifically for those. It's an interesting idea, though a pretty complicated one. Thanks
– James Carlyle-Clarke
Nov 12 '18 at 15:56
Thanks Mathieu. I mentioned I have a formula string, that's because it's from a Conditional Format. Still, I might be able to place the formula in an empty cell, trace the precedents, get a list of cells that are in the formula, and then search specifically for those. It's an interesting idea, though a pretty complicated one. Thanks
– James Carlyle-Clarke
Nov 12 '18 at 15:56
Hi Kubie, yes, I would very much be interested in RegEx, though I think the points about how to search for any and all relative cell references is an important one, and I don't know whether my suggestion would work.
– James Carlyle-Clarke
Nov 12 '18 at 15:57
Hi Kubie, yes, I would very much be interested in RegEx, though I think the points about how to search for any and all relative cell references is an important one, and I don't know whether my suggestion would work.
– James Carlyle-Clarke
Nov 12 '18 at 15:57
1
1
One comment about your comments. instead of writing "Hi #Name", consider using @Name at the beginning of your comment. In your case it might be that Kubie and Mathieu never read your comment
– Pierre44
Nov 12 '18 at 16:19
One comment about your comments. instead of writing "Hi #Name", consider using @Name at the beginning of your comment. In your case it might be that Kubie and Mathieu never read your comment
– Pierre44
Nov 12 '18 at 16:19
|
show 8 more comments
1 Answer
1
active
oldest
votes
I wasn't sure what your use case is, but you could try something along the lines in this function:
This project uses Early Binding - you must set a reference to:
Microsoft VBScript Regular Expressions 5.5
Function findCellReferences(vTestVal As Variant) As Variant
'Check if vTestVal is a range, if so, convert to string
If TypeName(vTestVal) = "Range" Then
vTestVal = vTestVal.Formula
ElseIf TypeName(vTestVal) <> "String" Then
findCellReferences = "Type-Err!"
Exit Function
End If
Dim oMatches As MatchCollection
With New RegExp
.Pattern = "(?:^|[,!(=s])((?:$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?|" & _
"$?[a-z]{1,3}:$?[a-z]{1,3}|$?d+:$?d+))(?:$|[s,)])"
.IgnoreCase = True
.Global = True
If .test(vTestVal) Then
Dim i As Long, retArr()
Set oMatches = .Execute(vTestVal)
With oMatches
ReDim retArr(.Count - 1)
For i = 0 To .Count - 1
retArr(i) = .Item(i).SubMatches(0)
Next
End With
findCellReferences = Join(retArr, ",")
Else
findCellReferences = False
End If
End With
End Function
This function can accept two different data-types:
- Range
- String
This allows you to use this as a worksheet function to test the textual value of a formula, or you can use this to test an input string directly.
Here's the return checking a cell:
And here's what the formulas look like for the above:
This can also be used within VBA:
Sub Test()
Rem: Passing a string argument
Debug.Print findCellReferences("A1:B1, $C1")
' Prints: A1:B1,$C1
End Sub
Breaking down the Regular Expression: Regex101
(?:^|[,!(=s])((?:$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?|$?[a-z]{1,3}:$?
[a-z]{1,3}|$?d+:$?d+))(?:$|[s,)])
(?:^|[,(=s])
Requires one of the following to occur before your match
^
Start of string; or- A single character which is either
,
a comma (useful in formulas)
!
an exclamation (forSheet!
references)
(
opening parenthesis (useful in formulas)
=
literal equal sign (useful in formulas)
s
a whitespace character
(...)
capturing group that will return your value (one of three options)
$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?
not entire row / column
$
for an absolute reference (column), followed by?
making it optional
[A-Z]
character class for any letter,+
one or more times
$
for an absolute reference (row), followed by?
making it optional
d
any digit,+
one or more times
(?:...)
non capturing group to match a range of ranges (such asA1:B1
)
- This uses the same methods as above
- Followed by
?
, making the entire non-capturing group optional
$?[a-z]{1,3}:$?[a-z]{1,3}
Entire Columns, such asA:C
$?d+:$?d+
Entire Rows, such as1:3
(?:$|[s,)])
Requires one of the following to occur after your match
$
end of string; or- A single character which is one of
s
a whitespace character
,
a comma
)
closing parenthesis
Credits:
Suggestions by tripleee:
- Use Character class [xyz]
instead of OR
statements (?:x|y|z)
- Better indention of breakdown
- Grammar usage
Suggestions by Makyen:
- Support for entire rows 1:4
and columns A:C
- Limit FPs by checking Excel's max column limitation [a-z]{1,3}
(instead of [a-z]+
)
Hi K. Davis, you've certainly given me a lot to look through and experiment with. Before that process I can only thank you deeply for taking the time and expending the effort. I don't know what the effect of changes @tripleee's changes to the code are, if any, but I'll look at those too.
– James Carlyle-Clarke
Nov 13 '18 at 6:13
I consulted him after I made this post - his edits are certainly welcome @JamesCarlyle-Clarke
– K.Dᴀᴠɪs
Nov 13 '18 at 6:14
Will this exclude bits of literal strings that happen to look like cell references?
– chris neilsen
Nov 13 '18 at 7:51
1
That could be negated by removing thes
from the equation. OP will just have to prevent spaces around legitimate ranges @chrisneilsen - The issue is that VBScript RegEx doesn't support lookarounds.
– K.Dᴀᴠɪs
Nov 13 '18 at 8:05
Hi K. Davis, that's quite a thing of beauty and it seems to find any references passed to functions eg SUM(H7:M7). I haven't tested all of the edge cases (eg custom function names with letter followed by a number, named ranges with similar). The problem is it doesn't seem to find either cell reference in 'A3+B4' (with or without the equals sign). Regular expressions is not my area of expertise so I can't immediately see how I would improve on that. I also don't really get the last comment you made above, but it sounds like you are saying I should remove any spaces other than in strings?
– James Carlyle-Clarke
Nov 19 '18 at 2:33
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265024%2fsearch-formula-for-cell-references-in-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I wasn't sure what your use case is, but you could try something along the lines in this function:
This project uses Early Binding - you must set a reference to:
Microsoft VBScript Regular Expressions 5.5
Function findCellReferences(vTestVal As Variant) As Variant
'Check if vTestVal is a range, if so, convert to string
If TypeName(vTestVal) = "Range" Then
vTestVal = vTestVal.Formula
ElseIf TypeName(vTestVal) <> "String" Then
findCellReferences = "Type-Err!"
Exit Function
End If
Dim oMatches As MatchCollection
With New RegExp
.Pattern = "(?:^|[,!(=s])((?:$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?|" & _
"$?[a-z]{1,3}:$?[a-z]{1,3}|$?d+:$?d+))(?:$|[s,)])"
.IgnoreCase = True
.Global = True
If .test(vTestVal) Then
Dim i As Long, retArr()
Set oMatches = .Execute(vTestVal)
With oMatches
ReDim retArr(.Count - 1)
For i = 0 To .Count - 1
retArr(i) = .Item(i).SubMatches(0)
Next
End With
findCellReferences = Join(retArr, ",")
Else
findCellReferences = False
End If
End With
End Function
This function can accept two different data-types:
- Range
- String
This allows you to use this as a worksheet function to test the textual value of a formula, or you can use this to test an input string directly.
Here's the return checking a cell:
And here's what the formulas look like for the above:
This can also be used within VBA:
Sub Test()
Rem: Passing a string argument
Debug.Print findCellReferences("A1:B1, $C1")
' Prints: A1:B1,$C1
End Sub
Breaking down the Regular Expression: Regex101
(?:^|[,!(=s])((?:$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?|$?[a-z]{1,3}:$?
[a-z]{1,3}|$?d+:$?d+))(?:$|[s,)])
(?:^|[,(=s])
Requires one of the following to occur before your match
^
Start of string; or- A single character which is either
,
a comma (useful in formulas)
!
an exclamation (forSheet!
references)
(
opening parenthesis (useful in formulas)
=
literal equal sign (useful in formulas)
s
a whitespace character
(...)
capturing group that will return your value (one of three options)
$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?
not entire row / column
$
for an absolute reference (column), followed by?
making it optional
[A-Z]
character class for any letter,+
one or more times
$
for an absolute reference (row), followed by?
making it optional
d
any digit,+
one or more times
(?:...)
non capturing group to match a range of ranges (such asA1:B1
)
- This uses the same methods as above
- Followed by
?
, making the entire non-capturing group optional
$?[a-z]{1,3}:$?[a-z]{1,3}
Entire Columns, such asA:C
$?d+:$?d+
Entire Rows, such as1:3
(?:$|[s,)])
Requires one of the following to occur after your match
$
end of string; or- A single character which is one of
s
a whitespace character
,
a comma
)
closing parenthesis
Credits:
Suggestions by tripleee:
- Use Character class [xyz]
instead of OR
statements (?:x|y|z)
- Better indention of breakdown
- Grammar usage
Suggestions by Makyen:
- Support for entire rows 1:4
and columns A:C
- Limit FPs by checking Excel's max column limitation [a-z]{1,3}
(instead of [a-z]+
)
Hi K. Davis, you've certainly given me a lot to look through and experiment with. Before that process I can only thank you deeply for taking the time and expending the effort. I don't know what the effect of changes @tripleee's changes to the code are, if any, but I'll look at those too.
– James Carlyle-Clarke
Nov 13 '18 at 6:13
I consulted him after I made this post - his edits are certainly welcome @JamesCarlyle-Clarke
– K.Dᴀᴠɪs
Nov 13 '18 at 6:14
Will this exclude bits of literal strings that happen to look like cell references?
– chris neilsen
Nov 13 '18 at 7:51
1
That could be negated by removing thes
from the equation. OP will just have to prevent spaces around legitimate ranges @chrisneilsen - The issue is that VBScript RegEx doesn't support lookarounds.
– K.Dᴀᴠɪs
Nov 13 '18 at 8:05
Hi K. Davis, that's quite a thing of beauty and it seems to find any references passed to functions eg SUM(H7:M7). I haven't tested all of the edge cases (eg custom function names with letter followed by a number, named ranges with similar). The problem is it doesn't seem to find either cell reference in 'A3+B4' (with or without the equals sign). Regular expressions is not my area of expertise so I can't immediately see how I would improve on that. I also don't really get the last comment you made above, but it sounds like you are saying I should remove any spaces other than in strings?
– James Carlyle-Clarke
Nov 19 '18 at 2:33
add a comment |
I wasn't sure what your use case is, but you could try something along the lines in this function:
This project uses Early Binding - you must set a reference to:
Microsoft VBScript Regular Expressions 5.5
Function findCellReferences(vTestVal As Variant) As Variant
'Check if vTestVal is a range, if so, convert to string
If TypeName(vTestVal) = "Range" Then
vTestVal = vTestVal.Formula
ElseIf TypeName(vTestVal) <> "String" Then
findCellReferences = "Type-Err!"
Exit Function
End If
Dim oMatches As MatchCollection
With New RegExp
.Pattern = "(?:^|[,!(=s])((?:$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?|" & _
"$?[a-z]{1,3}:$?[a-z]{1,3}|$?d+:$?d+))(?:$|[s,)])"
.IgnoreCase = True
.Global = True
If .test(vTestVal) Then
Dim i As Long, retArr()
Set oMatches = .Execute(vTestVal)
With oMatches
ReDim retArr(.Count - 1)
For i = 0 To .Count - 1
retArr(i) = .Item(i).SubMatches(0)
Next
End With
findCellReferences = Join(retArr, ",")
Else
findCellReferences = False
End If
End With
End Function
This function can accept two different data-types:
- Range
- String
This allows you to use this as a worksheet function to test the textual value of a formula, or you can use this to test an input string directly.
Here's the return checking a cell:
And here's what the formulas look like for the above:
This can also be used within VBA:
Sub Test()
Rem: Passing a string argument
Debug.Print findCellReferences("A1:B1, $C1")
' Prints: A1:B1,$C1
End Sub
Breaking down the Regular Expression: Regex101
(?:^|[,!(=s])((?:$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?|$?[a-z]{1,3}:$?
[a-z]{1,3}|$?d+:$?d+))(?:$|[s,)])
(?:^|[,(=s])
Requires one of the following to occur before your match
^
Start of string; or- A single character which is either
,
a comma (useful in formulas)
!
an exclamation (forSheet!
references)
(
opening parenthesis (useful in formulas)
=
literal equal sign (useful in formulas)
s
a whitespace character
(...)
capturing group that will return your value (one of three options)
$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?
not entire row / column
$
for an absolute reference (column), followed by?
making it optional
[A-Z]
character class for any letter,+
one or more times
$
for an absolute reference (row), followed by?
making it optional
d
any digit,+
one or more times
(?:...)
non capturing group to match a range of ranges (such asA1:B1
)
- This uses the same methods as above
- Followed by
?
, making the entire non-capturing group optional
$?[a-z]{1,3}:$?[a-z]{1,3}
Entire Columns, such asA:C
$?d+:$?d+
Entire Rows, such as1:3
(?:$|[s,)])
Requires one of the following to occur after your match
$
end of string; or- A single character which is one of
s
a whitespace character
,
a comma
)
closing parenthesis
Credits:
Suggestions by tripleee:
- Use Character class [xyz]
instead of OR
statements (?:x|y|z)
- Better indention of breakdown
- Grammar usage
Suggestions by Makyen:
- Support for entire rows 1:4
and columns A:C
- Limit FPs by checking Excel's max column limitation [a-z]{1,3}
(instead of [a-z]+
)
Hi K. Davis, you've certainly given me a lot to look through and experiment with. Before that process I can only thank you deeply for taking the time and expending the effort. I don't know what the effect of changes @tripleee's changes to the code are, if any, but I'll look at those too.
– James Carlyle-Clarke
Nov 13 '18 at 6:13
I consulted him after I made this post - his edits are certainly welcome @JamesCarlyle-Clarke
– K.Dᴀᴠɪs
Nov 13 '18 at 6:14
Will this exclude bits of literal strings that happen to look like cell references?
– chris neilsen
Nov 13 '18 at 7:51
1
That could be negated by removing thes
from the equation. OP will just have to prevent spaces around legitimate ranges @chrisneilsen - The issue is that VBScript RegEx doesn't support lookarounds.
– K.Dᴀᴠɪs
Nov 13 '18 at 8:05
Hi K. Davis, that's quite a thing of beauty and it seems to find any references passed to functions eg SUM(H7:M7). I haven't tested all of the edge cases (eg custom function names with letter followed by a number, named ranges with similar). The problem is it doesn't seem to find either cell reference in 'A3+B4' (with or without the equals sign). Regular expressions is not my area of expertise so I can't immediately see how I would improve on that. I also don't really get the last comment you made above, but it sounds like you are saying I should remove any spaces other than in strings?
– James Carlyle-Clarke
Nov 19 '18 at 2:33
add a comment |
I wasn't sure what your use case is, but you could try something along the lines in this function:
This project uses Early Binding - you must set a reference to:
Microsoft VBScript Regular Expressions 5.5
Function findCellReferences(vTestVal As Variant) As Variant
'Check if vTestVal is a range, if so, convert to string
If TypeName(vTestVal) = "Range" Then
vTestVal = vTestVal.Formula
ElseIf TypeName(vTestVal) <> "String" Then
findCellReferences = "Type-Err!"
Exit Function
End If
Dim oMatches As MatchCollection
With New RegExp
.Pattern = "(?:^|[,!(=s])((?:$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?|" & _
"$?[a-z]{1,3}:$?[a-z]{1,3}|$?d+:$?d+))(?:$|[s,)])"
.IgnoreCase = True
.Global = True
If .test(vTestVal) Then
Dim i As Long, retArr()
Set oMatches = .Execute(vTestVal)
With oMatches
ReDim retArr(.Count - 1)
For i = 0 To .Count - 1
retArr(i) = .Item(i).SubMatches(0)
Next
End With
findCellReferences = Join(retArr, ",")
Else
findCellReferences = False
End If
End With
End Function
This function can accept two different data-types:
- Range
- String
This allows you to use this as a worksheet function to test the textual value of a formula, or you can use this to test an input string directly.
Here's the return checking a cell:
And here's what the formulas look like for the above:
This can also be used within VBA:
Sub Test()
Rem: Passing a string argument
Debug.Print findCellReferences("A1:B1, $C1")
' Prints: A1:B1,$C1
End Sub
Breaking down the Regular Expression: Regex101
(?:^|[,!(=s])((?:$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?|$?[a-z]{1,3}:$?
[a-z]{1,3}|$?d+:$?d+))(?:$|[s,)])
(?:^|[,(=s])
Requires one of the following to occur before your match
^
Start of string; or- A single character which is either
,
a comma (useful in formulas)
!
an exclamation (forSheet!
references)
(
opening parenthesis (useful in formulas)
=
literal equal sign (useful in formulas)
s
a whitespace character
(...)
capturing group that will return your value (one of three options)
$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?
not entire row / column
$
for an absolute reference (column), followed by?
making it optional
[A-Z]
character class for any letter,+
one or more times
$
for an absolute reference (row), followed by?
making it optional
d
any digit,+
one or more times
(?:...)
non capturing group to match a range of ranges (such asA1:B1
)
- This uses the same methods as above
- Followed by
?
, making the entire non-capturing group optional
$?[a-z]{1,3}:$?[a-z]{1,3}
Entire Columns, such asA:C
$?d+:$?d+
Entire Rows, such as1:3
(?:$|[s,)])
Requires one of the following to occur after your match
$
end of string; or- A single character which is one of
s
a whitespace character
,
a comma
)
closing parenthesis
Credits:
Suggestions by tripleee:
- Use Character class [xyz]
instead of OR
statements (?:x|y|z)
- Better indention of breakdown
- Grammar usage
Suggestions by Makyen:
- Support for entire rows 1:4
and columns A:C
- Limit FPs by checking Excel's max column limitation [a-z]{1,3}
(instead of [a-z]+
)
I wasn't sure what your use case is, but you could try something along the lines in this function:
This project uses Early Binding - you must set a reference to:
Microsoft VBScript Regular Expressions 5.5
Function findCellReferences(vTestVal As Variant) As Variant
'Check if vTestVal is a range, if so, convert to string
If TypeName(vTestVal) = "Range" Then
vTestVal = vTestVal.Formula
ElseIf TypeName(vTestVal) <> "String" Then
findCellReferences = "Type-Err!"
Exit Function
End If
Dim oMatches As MatchCollection
With New RegExp
.Pattern = "(?:^|[,!(=s])((?:$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?|" & _
"$?[a-z]{1,3}:$?[a-z]{1,3}|$?d+:$?d+))(?:$|[s,)])"
.IgnoreCase = True
.Global = True
If .test(vTestVal) Then
Dim i As Long, retArr()
Set oMatches = .Execute(vTestVal)
With oMatches
ReDim retArr(.Count - 1)
For i = 0 To .Count - 1
retArr(i) = .Item(i).SubMatches(0)
Next
End With
findCellReferences = Join(retArr, ",")
Else
findCellReferences = False
End If
End With
End Function
This function can accept two different data-types:
- Range
- String
This allows you to use this as a worksheet function to test the textual value of a formula, or you can use this to test an input string directly.
Here's the return checking a cell:
And here's what the formulas look like for the above:
This can also be used within VBA:
Sub Test()
Rem: Passing a string argument
Debug.Print findCellReferences("A1:B1, $C1")
' Prints: A1:B1,$C1
End Sub
Breaking down the Regular Expression: Regex101
(?:^|[,!(=s])((?:$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?|$?[a-z]{1,3}:$?
[a-z]{1,3}|$?d+:$?d+))(?:$|[s,)])
(?:^|[,(=s])
Requires one of the following to occur before your match
^
Start of string; or- A single character which is either
,
a comma (useful in formulas)
!
an exclamation (forSheet!
references)
(
opening parenthesis (useful in formulas)
=
literal equal sign (useful in formulas)
s
a whitespace character
(...)
capturing group that will return your value (one of three options)
$?[A-Z]{1,3}$?d+(?::$?[A-Z]{1,3}$?d+)?
not entire row / column
$
for an absolute reference (column), followed by?
making it optional
[A-Z]
character class for any letter,+
one or more times
$
for an absolute reference (row), followed by?
making it optional
d
any digit,+
one or more times
(?:...)
non capturing group to match a range of ranges (such asA1:B1
)
- This uses the same methods as above
- Followed by
?
, making the entire non-capturing group optional
$?[a-z]{1,3}:$?[a-z]{1,3}
Entire Columns, such asA:C
$?d+:$?d+
Entire Rows, such as1:3
(?:$|[s,)])
Requires one of the following to occur after your match
$
end of string; or- A single character which is one of
s
a whitespace character
,
a comma
)
closing parenthesis
Credits:
Suggestions by tripleee:
- Use Character class [xyz]
instead of OR
statements (?:x|y|z)
- Better indention of breakdown
- Grammar usage
Suggestions by Makyen:
- Support for entire rows 1:4
and columns A:C
- Limit FPs by checking Excel's max column limitation [a-z]{1,3}
(instead of [a-z]+
)
edited Nov 13 '18 at 8:23
answered Nov 13 '18 at 5:22
K.Dᴀᴠɪs
6,956112239
6,956112239
Hi K. Davis, you've certainly given me a lot to look through and experiment with. Before that process I can only thank you deeply for taking the time and expending the effort. I don't know what the effect of changes @tripleee's changes to the code are, if any, but I'll look at those too.
– James Carlyle-Clarke
Nov 13 '18 at 6:13
I consulted him after I made this post - his edits are certainly welcome @JamesCarlyle-Clarke
– K.Dᴀᴠɪs
Nov 13 '18 at 6:14
Will this exclude bits of literal strings that happen to look like cell references?
– chris neilsen
Nov 13 '18 at 7:51
1
That could be negated by removing thes
from the equation. OP will just have to prevent spaces around legitimate ranges @chrisneilsen - The issue is that VBScript RegEx doesn't support lookarounds.
– K.Dᴀᴠɪs
Nov 13 '18 at 8:05
Hi K. Davis, that's quite a thing of beauty and it seems to find any references passed to functions eg SUM(H7:M7). I haven't tested all of the edge cases (eg custom function names with letter followed by a number, named ranges with similar). The problem is it doesn't seem to find either cell reference in 'A3+B4' (with or without the equals sign). Regular expressions is not my area of expertise so I can't immediately see how I would improve on that. I also don't really get the last comment you made above, but it sounds like you are saying I should remove any spaces other than in strings?
– James Carlyle-Clarke
Nov 19 '18 at 2:33
add a comment |
Hi K. Davis, you've certainly given me a lot to look through and experiment with. Before that process I can only thank you deeply for taking the time and expending the effort. I don't know what the effect of changes @tripleee's changes to the code are, if any, but I'll look at those too.
– James Carlyle-Clarke
Nov 13 '18 at 6:13
I consulted him after I made this post - his edits are certainly welcome @JamesCarlyle-Clarke
– K.Dᴀᴠɪs
Nov 13 '18 at 6:14
Will this exclude bits of literal strings that happen to look like cell references?
– chris neilsen
Nov 13 '18 at 7:51
1
That could be negated by removing thes
from the equation. OP will just have to prevent spaces around legitimate ranges @chrisneilsen - The issue is that VBScript RegEx doesn't support lookarounds.
– K.Dᴀᴠɪs
Nov 13 '18 at 8:05
Hi K. Davis, that's quite a thing of beauty and it seems to find any references passed to functions eg SUM(H7:M7). I haven't tested all of the edge cases (eg custom function names with letter followed by a number, named ranges with similar). The problem is it doesn't seem to find either cell reference in 'A3+B4' (with or without the equals sign). Regular expressions is not my area of expertise so I can't immediately see how I would improve on that. I also don't really get the last comment you made above, but it sounds like you are saying I should remove any spaces other than in strings?
– James Carlyle-Clarke
Nov 19 '18 at 2:33
Hi K. Davis, you've certainly given me a lot to look through and experiment with. Before that process I can only thank you deeply for taking the time and expending the effort. I don't know what the effect of changes @tripleee's changes to the code are, if any, but I'll look at those too.
– James Carlyle-Clarke
Nov 13 '18 at 6:13
Hi K. Davis, you've certainly given me a lot to look through and experiment with. Before that process I can only thank you deeply for taking the time and expending the effort. I don't know what the effect of changes @tripleee's changes to the code are, if any, but I'll look at those too.
– James Carlyle-Clarke
Nov 13 '18 at 6:13
I consulted him after I made this post - his edits are certainly welcome @JamesCarlyle-Clarke
– K.Dᴀᴠɪs
Nov 13 '18 at 6:14
I consulted him after I made this post - his edits are certainly welcome @JamesCarlyle-Clarke
– K.Dᴀᴠɪs
Nov 13 '18 at 6:14
Will this exclude bits of literal strings that happen to look like cell references?
– chris neilsen
Nov 13 '18 at 7:51
Will this exclude bits of literal strings that happen to look like cell references?
– chris neilsen
Nov 13 '18 at 7:51
1
1
That could be negated by removing the
s
from the equation. OP will just have to prevent spaces around legitimate ranges @chrisneilsen - The issue is that VBScript RegEx doesn't support lookarounds.– K.Dᴀᴠɪs
Nov 13 '18 at 8:05
That could be negated by removing the
s
from the equation. OP will just have to prevent spaces around legitimate ranges @chrisneilsen - The issue is that VBScript RegEx doesn't support lookarounds.– K.Dᴀᴠɪs
Nov 13 '18 at 8:05
Hi K. Davis, that's quite a thing of beauty and it seems to find any references passed to functions eg SUM(H7:M7). I haven't tested all of the edge cases (eg custom function names with letter followed by a number, named ranges with similar). The problem is it doesn't seem to find either cell reference in 'A3+B4' (with or without the equals sign). Regular expressions is not my area of expertise so I can't immediately see how I would improve on that. I also don't really get the last comment you made above, but it sounds like you are saying I should remove any spaces other than in strings?
– James Carlyle-Clarke
Nov 19 '18 at 2:33
Hi K. Davis, that's quite a thing of beauty and it seems to find any references passed to functions eg SUM(H7:M7). I haven't tested all of the edge cases (eg custom function names with letter followed by a number, named ranges with similar). The problem is it doesn't seem to find either cell reference in 'A3+B4' (with or without the equals sign). Regular expressions is not my area of expertise so I can't immediately see how I would improve on that. I also don't really get the last comment you made above, but it sounds like you are saying I should remove any spaces other than in strings?
– James Carlyle-Clarke
Nov 19 '18 at 2:33
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265024%2fsearch-formula-for-cell-references-in-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4
Look into
Range.Precedents
.– Mathieu Guindon
Nov 12 '18 at 15:22
2
Would you be interested in regex?
– Kubie
Nov 12 '18 at 15:33
Thanks Mathieu. I mentioned I have a formula string, that's because it's from a Conditional Format. Still, I might be able to place the formula in an empty cell, trace the precedents, get a list of cells that are in the formula, and then search specifically for those. It's an interesting idea, though a pretty complicated one. Thanks
– James Carlyle-Clarke
Nov 12 '18 at 15:56
Hi Kubie, yes, I would very much be interested in RegEx, though I think the points about how to search for any and all relative cell references is an important one, and I don't know whether my suggestion would work.
– James Carlyle-Clarke
Nov 12 '18 at 15:57
1
One comment about your comments. instead of writing "Hi #Name", consider using @Name at the beginning of your comment. In your case it might be that Kubie and Mathieu never read your comment
– Pierre44
Nov 12 '18 at 16:19