Create an evaluation string in VBA
I have a list of strings defined as
Dim replyFormat(0 To 999) As String
and a list of answers as
Dim answers(0 to 999) As String
and throughout the code certain strings get added to replyFormat that look similar to this:
Name: {1} {3}
When everything is done, I define a string called sendBack and start looping through each line in replyFormat. I want to set sendBack equal to itself plus what replyFormat is, evaluating answers for the numbers in the curly brackets and finally adding vbCrLf to the end. For exmaple if answers contains { Yes, John, H, Doe } and replyFormat is "Name: {1} {3}" it would ouput "Name: John Doe"
vba outlook
asked Nov 22 '18 at 0:00
William V.William V.
136
add a comment |
I have a list of strings defined as
Dim replyFormat(0 To 999) As String
and a list of answers as
Dim answers(0 to 999) As String
and throughout the code certain strings get added to replyFormat that look similar to this:
Name: {1} {3}
When everything is done, I define a string called sendBack and start looping through each line in replyFormat. I want to set sendBack equal to itself plus what replyFormat is, evaluating answers for the numbers in the curly brackets and finally adding vbCrLf to the end. For exmaple if answers contains { Yes, John, H, Doe } and replyFormat is "Name: {1} {3}" it would ouput "Name: John Doe"
vba outlook
asked Nov 22 '18 at 0:00
William V.William V.
136
add a comment |
I have a list of strings defined as
Dim replyFormat(0 To 999) As String
and a list of answers as
Dim answers(0 to 999) As String
and throughout the code certain strings get added to replyFormat that look similar to this:
Name: {1} {3}
When everything is done, I define a string called sendBack and start looping through each line in replyFormat. I want to set sendBack equal to itself plus what replyFormat is, evaluating answers for the numbers in the curly brackets and finally adding vbCrLf to the end. For exmaple if answers contains { Yes, John, H, Doe } and replyFormat is "Name: {1} {3}" it would ouput "Name: John Doe"
vba outlook
asked Nov 22 '18 at 0:00
William V.William V.
136
I have a list of strings defined as
Dim replyFormat(0 To 999) As String
and a list of answers as
Dim answers(0 to 999) As String
and throughout the code certain strings get added to replyFormat that look similar to this:
Name: {1} {3}
When everything is done, I define a string called sendBack and start looping through each line in replyFormat. I want to set sendBack equal to itself plus what replyFormat is, evaluating answers for the numbers in the curly brackets and finally adding vbCrLf to the end. For exmaple if answers contains { Yes, John, H, Doe } and replyFormat is "Name: {1} {3}" it would ouput "Name: John Doe"
vba outlook
vba outlook
asked Nov 22 '18 at 0:00
William V.William V.
136
asked Nov 22 '18 at 0:00
William V.William V.
136
asked Nov 22 '18 at 0:00
William V.William V.
136
asked Nov 22 '18 at 0:00
William V.William V.
136
asked Nov 22 '18 at 0:00
William V.William V.
136
136
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
It sounds like you're referring to reflection which isn't supported in VBA. You can however achieve the desired result by using Regular Expressions (RegEx):
Function FormattedString(stringToFormat As String, replacements() As String) As String
Dim placeholder As Variant
Dim index As Long
With CreateObject("VBScript.RegExp")
.Pattern = "{([d]{1,3})}"
.Global = True
.MultiLine = False
.IgnoreCase = True
If .Test(stringToFormat) Then
For Each placeholder In .Execute(stringToFormat)
index = CLng(placeholder.SubMatches(0))
stringToFormat = Replace$(stringToFormat, placeholder, replacements(index))
Next
End If
End With
FormattedString = stringToFormat
End Function
Example use:
Sub FooBar()
Dim answers(0 To 3) As String
Const testString = "Name: {1} {3}"
answers(0) = "Test"
answers(1) = "John"
answers(2) = "Testing"
answers(3) = "Doe"
Debug.Print FormattedString(testString, answers) '// "Name: John Doe"
End Sub
answered Nov 22 '18 at 10:09
SamSam
15.7k33055
This works beautifully, thank you so much!
– William V.
Nov 27 '18 at 23:17
No worries, glad it worked!
– Sam
Nov 27 '18 at 23:25
add a comment |
If this is your object:
Ob = { Yes, John, H, Doe},
You could select object item like this:
Ob(1), Ob(3)
For more information, Please refer to this link:
Retrieve the index of an object stored in a collection using its key (VBA)
answered Nov 22 '18 at 9:20
Alina LiAlina Li
643125
You can't create "objects" like this in VBA
– Sam
Nov 22 '18 at 9:27
I mean, if you have created an object, then you can use the method to get the item. Not create an new object. @ Sam
– Alina Li
Nov 22 '18 at 9:37
If you create an array or a collection you can retrieve items using an index like this - the question is asking how to parse text and retrieve an index from it, then evaluate that into executable code.
– Sam
Nov 22 '18 at 9:48
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%2f53422120%2fcreate-an-evaluation-string-in-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
It sounds like you're referring to reflection which isn't supported in VBA. You can however achieve the desired result by using Regular Expressions (RegEx):
Function FormattedString(stringToFormat As String, replacements() As String) As String
Dim placeholder As Variant
Dim index As Long
With CreateObject("VBScript.RegExp")
.Pattern = "{([d]{1,3})}"
.Global = True
.MultiLine = False
.IgnoreCase = True
If .Test(stringToFormat) Then
For Each placeholder In .Execute(stringToFormat)
index = CLng(placeholder.SubMatches(0))
stringToFormat = Replace$(stringToFormat, placeholder, replacements(index))
Next
End If
End With
FormattedString = stringToFormat
End Function
Example use:
Sub FooBar()
Dim answers(0 To 3) As String
Const testString = "Name: {1} {3}"
answers(0) = "Test"
answers(1) = "John"
answers(2) = "Testing"
answers(3) = "Doe"
Debug.Print FormattedString(testString, answers) '// "Name: John Doe"
End Sub
answered Nov 22 '18 at 10:09
SamSam
15.7k33055
This works beautifully, thank you so much!
– William V.
Nov 27 '18 at 23:17
No worries, glad it worked!
– Sam
Nov 27 '18 at 23:25
add a comment |
It sounds like you're referring to reflection which isn't supported in VBA. You can however achieve the desired result by using Regular Expressions (RegEx):
Function FormattedString(stringToFormat As String, replacements() As String) As String
Dim placeholder As Variant
Dim index As Long
With CreateObject("VBScript.RegExp")
.Pattern = "{([d]{1,3})}"
.Global = True
.MultiLine = False
.IgnoreCase = True
If .Test(stringToFormat) Then
For Each placeholder In .Execute(stringToFormat)
index = CLng(placeholder.SubMatches(0))
stringToFormat = Replace$(stringToFormat, placeholder, replacements(index))
Next
End If
End With
FormattedString = stringToFormat
End Function
Example use:
Sub FooBar()
Dim answers(0 To 3) As String
Const testString = "Name: {1} {3}"
answers(0) = "Test"
answers(1) = "John"
answers(2) = "Testing"
answers(3) = "Doe"
Debug.Print FormattedString(testString, answers) '// "Name: John Doe"
End Sub
answered Nov 22 '18 at 10:09
SamSam
15.7k33055
This works beautifully, thank you so much!
– William V.
Nov 27 '18 at 23:17
No worries, glad it worked!
– Sam
Nov 27 '18 at 23:25
add a comment |
It sounds like you're referring to reflection which isn't supported in VBA. You can however achieve the desired result by using Regular Expressions (RegEx):
Function FormattedString(stringToFormat As String, replacements() As String) As String
Dim placeholder As Variant
Dim index As Long
With CreateObject("VBScript.RegExp")
.Pattern = "{([d]{1,3})}"
.Global = True
.MultiLine = False
.IgnoreCase = True
If .Test(stringToFormat) Then
For Each placeholder In .Execute(stringToFormat)
index = CLng(placeholder.SubMatches(0))
stringToFormat = Replace$(stringToFormat, placeholder, replacements(index))
Next
End If
End With
FormattedString = stringToFormat
End Function
Example use:
Sub FooBar()
Dim answers(0 To 3) As String
Const testString = "Name: {1} {3}"
answers(0) = "Test"
answers(1) = "John"
answers(2) = "Testing"
answers(3) = "Doe"
Debug.Print FormattedString(testString, answers) '// "Name: John Doe"
End Sub
answered Nov 22 '18 at 10:09
SamSam
15.7k33055
It sounds like you're referring to reflection which isn't supported in VBA. You can however achieve the desired result by using Regular Expressions (RegEx):
Function FormattedString(stringToFormat As String, replacements() As String) As String
Dim placeholder As Variant
Dim index As Long
With CreateObject("VBScript.RegExp")
.Pattern = "{([d]{1,3})}"
.Global = True
.MultiLine = False
.IgnoreCase = True
If .Test(stringToFormat) Then
For Each placeholder In .Execute(stringToFormat)
index = CLng(placeholder.SubMatches(0))
stringToFormat = Replace$(stringToFormat, placeholder, replacements(index))
Next
End If
End With
FormattedString = stringToFormat
End Function
Example use:
Sub FooBar()
Dim answers(0 To 3) As String
Const testString = "Name: {1} {3}"
answers(0) = "Test"
answers(1) = "John"
answers(2) = "Testing"
answers(3) = "Doe"
Debug.Print FormattedString(testString, answers) '// "Name: John Doe"
End Sub
answered Nov 22 '18 at 10:09
SamSam
15.7k33055
answered Nov 22 '18 at 10:09
SamSam
15.7k33055
answered Nov 22 '18 at 10:09
SamSam
15.7k33055
answered Nov 22 '18 at 10:09
SamSam
15.7k33055
15.7k33055
This works beautifully, thank you so much!
– William V.
Nov 27 '18 at 23:17
No worries, glad it worked!
– Sam
Nov 27 '18 at 23:25
add a comment |
This works beautifully, thank you so much!
– William V.
Nov 27 '18 at 23:17
No worries, glad it worked!
– Sam
Nov 27 '18 at 23:25
This works beautifully, thank you so much!
– William V.
Nov 27 '18 at 23:17
This works beautifully, thank you so much!
– William V.
Nov 27 '18 at 23:17
No worries, glad it worked!
– Sam
Nov 27 '18 at 23:25
No worries, glad it worked!
– Sam
Nov 27 '18 at 23:25
add a comment |
If this is your object:
Ob = { Yes, John, H, Doe},
You could select object item like this:
Ob(1), Ob(3)
For more information, Please refer to this link:
Retrieve the index of an object stored in a collection using its key (VBA)
answered Nov 22 '18 at 9:20
Alina LiAlina Li
643125
You can't create "objects" like this in VBA
– Sam
Nov 22 '18 at 9:27
I mean, if you have created an object, then you can use the method to get the item. Not create an new object. @ Sam
– Alina Li
Nov 22 '18 at 9:37
If you create an array or a collection you can retrieve items using an index like this - the question is asking how to parse text and retrieve an index from it, then evaluate that into executable code.
– Sam
Nov 22 '18 at 9:48
add a comment |
If this is your object:
Ob = { Yes, John, H, Doe},
You could select object item like this:
Ob(1), Ob(3)
For more information, Please refer to this link:
Retrieve the index of an object stored in a collection using its key (VBA)
answered Nov 22 '18 at 9:20
Alina LiAlina Li
643125
You can't create "objects" like this in VBA
– Sam
Nov 22 '18 at 9:27
I mean, if you have created an object, then you can use the method to get the item. Not create an new object. @ Sam
– Alina Li
Nov 22 '18 at 9:37
If you create an array or a collection you can retrieve items using an index like this - the question is asking how to parse text and retrieve an index from it, then evaluate that into executable code.
– Sam
Nov 22 '18 at 9:48
add a comment |
If this is your object:
Ob = { Yes, John, H, Doe},
You could select object item like this:
Ob(1), Ob(3)
For more information, Please refer to this link:
Retrieve the index of an object stored in a collection using its key (VBA)
answered Nov 22 '18 at 9:20
Alina LiAlina Li
643125
If this is your object:
Ob = { Yes, John, H, Doe},
You could select object item like this:
Ob(1), Ob(3)
For more information, Please refer to this link:
Retrieve the index of an object stored in a collection using its key (VBA)
answered Nov 22 '18 at 9:20
Alina LiAlina Li
643125
edited Nov 23 '18 at 1:32
answered Nov 22 '18 at 9:20
Alina LiAlina Li
643125
answered Nov 22 '18 at 9:20
Alina LiAlina Li
643125
answered Nov 22 '18 at 9:20
Alina LiAlina Li
643125
643125
You can't create "objects" like this in VBA
– Sam
Nov 22 '18 at 9:27
I mean, if you have created an object, then you can use the method to get the item. Not create an new object. @ Sam
– Alina Li
Nov 22 '18 at 9:37
If you create an array or a collection you can retrieve items using an index like this - the question is asking how to parse text and retrieve an index from it, then evaluate that into executable code.
– Sam
Nov 22 '18 at 9:48
add a comment |
You can't create "objects" like this in VBA
– Sam
Nov 22 '18 at 9:27
I mean, if you have created an object, then you can use the method to get the item. Not create an new object. @ Sam
– Alina Li
Nov 22 '18 at 9:37
If you create an array or a collection you can retrieve items using an index like this - the question is asking how to parse text and retrieve an index from it, then evaluate that into executable code.
– Sam
Nov 22 '18 at 9:48
You can't create "objects" like this in VBA
– Sam
Nov 22 '18 at 9:27
You can't create "objects" like this in VBA
– Sam
Nov 22 '18 at 9:27
I mean, if you have created an object, then you can use the method to get the item. Not create an new object. @ Sam
– Alina Li
Nov 22 '18 at 9:37
I mean, if you have created an object, then you can use the method to get the item. Not create an new object. @ Sam
– Alina Li
Nov 22 '18 at 9:37
If you create an array or a collection you can retrieve items using an index like this - the question is asking how to parse text and retrieve an index from it, then evaluate that into executable code.
– Sam
Nov 22 '18 at 9:48
If you create an array or a collection you can retrieve items using an index like this - the question is asking how to parse text and retrieve an index from it, then evaluate that into executable code.
– Sam
Nov 22 '18 at 9:48
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.
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%2f53422120%2fcreate-an-evaluation-string-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
