“Object variable or With block variable not set” using Regular expressions
1
I'm getting the error
"Object variable or With block variable not set"
setting the function to the RegEx object, so the thing is that I need to validate a File name with those patterns.
I'm thinking that the Select case it's wrong too. I don't know how to solve it.
Do While xExcelFile <> ""
strPattern = GetPattern(newFileName)
With RegEx
.Pattern = strPattern 'error occurs here
End With
Select Case True
'go to next loop if the selected file and this workbook has the same name
Case xExcelFile = xWname
GoTo NextLoop
'filter by name pattern
Case RegEx.Test(newFileName)
Workbooks.Open fileName:=xSPath & xExcelFile 'open file
Case RegEx.Test(newFileName)
Workbooks.Open fileName:=xSPath & xExcelFile 'open file`
more operational code below
This is the function that searches the desired pattern, and I think its work properly
Function GetPattern(ByVal newFileName As String) As String
Dim strPattern As String
Static RegEx As Object
strPattern1 = "^([B]d{3}w{1,})"
strPattern2 = "^d*.d{4}w*"
If RegEx Is Nothing Then
' To speed the code up set the static RegEx object only once
Set RegEx = CreateObject("VBScript.RegExp")
End If
With RegEx
.Pattern = strPattern1
If RegEx.Test(newFileName) Then
strPattern = strPattern1
End If
.Pattern = strPattern2
If RegEx.Test(newFileName) Then
strPattern = strPattern2
End If
End With
GetPattern = strPattern
End Function
regex excel vba excel-vba
edited Nov 21 '18 at 13:36
Pᴇʜ
20.2k42650
asked Nov 21 '18 at 13:24
Jose Chiesa
103
add a comment |
1
I'm getting the error
"Object variable or With block variable not set"
setting the function to the RegEx object, so the thing is that I need to validate a File name with those patterns.
I'm thinking that the Select case it's wrong too. I don't know how to solve it.
Do While xExcelFile <> ""
strPattern = GetPattern(newFileName)
With RegEx
.Pattern = strPattern 'error occurs here
End With
Select Case True
'go to next loop if the selected file and this workbook has the same name
Case xExcelFile = xWname
GoTo NextLoop
'filter by name pattern
Case RegEx.Test(newFileName)
Workbooks.Open fileName:=xSPath & xExcelFile 'open file
Case RegEx.Test(newFileName)
Workbooks.Open fileName:=xSPath & xExcelFile 'open file`
more operational code below
This is the function that searches the desired pattern, and I think its work properly
Function GetPattern(ByVal newFileName As String) As String
Dim strPattern As String
Static RegEx As Object
strPattern1 = "^([B]d{3}w{1,})"
strPattern2 = "^d*.d{4}w*"
If RegEx Is Nothing Then
' To speed the code up set the static RegEx object only once
Set RegEx = CreateObject("VBScript.RegExp")
End If
With RegEx
.Pattern = strPattern1
If RegEx.Test(newFileName) Then
strPattern = strPattern1
End If
.Pattern = strPattern2
If RegEx.Test(newFileName) Then
strPattern = strPattern2
End If
End With
GetPattern = strPattern
End Function
regex excel vba excel-vba
edited Nov 21 '18 at 13:36
Pᴇʜ
20.2k42650
asked Nov 21 '18 at 13:24
Jose Chiesa
103
Don't put the next loop in the select case; leave that particular case blank, or set i=0 and then for the other operational code, if i<>0 then execute, otherwise end if and moves to next loop. This could lead to issues with overarching with statements, etc.
– Cyril
Nov 21 '18 at 13:28
1
It looks likeRegExis local to theGetPatterncode, which is why you get the error in the calling code.
– Rory
Nov 21 '18 at 13:34
More to @Rory's point, provide the whole proc that contains theDo Whileloop - there are a lot of pieces missing that will help put the puzzle together.
– FreeMan
Nov 21 '18 at 14:45
add a comment |
1
1
1
1
I'm getting the error
"Object variable or With block variable not set"
setting the function to the RegEx object, so the thing is that I need to validate a File name with those patterns.
I'm thinking that the Select case it's wrong too. I don't know how to solve it.
Do While xExcelFile <> ""
strPattern = GetPattern(newFileName)
With RegEx
.Pattern = strPattern 'error occurs here
End With
Select Case True
'go to next loop if the selected file and this workbook has the same name
Case xExcelFile = xWname
GoTo NextLoop
'filter by name pattern
Case RegEx.Test(newFileName)
Workbooks.Open fileName:=xSPath & xExcelFile 'open file
Case RegEx.Test(newFileName)
Workbooks.Open fileName:=xSPath & xExcelFile 'open file`
more operational code below
This is the function that searches the desired pattern, and I think its work properly
Function GetPattern(ByVal newFileName As String) As String
Dim strPattern As String
Static RegEx As Object
strPattern1 = "^([B]d{3}w{1,})"
strPattern2 = "^d*.d{4}w*"
If RegEx Is Nothing Then
' To speed the code up set the static RegEx object only once
Set RegEx = CreateObject("VBScript.RegExp")
End If
With RegEx
.Pattern = strPattern1
If RegEx.Test(newFileName) Then
strPattern = strPattern1
End If
.Pattern = strPattern2
If RegEx.Test(newFileName) Then
strPattern = strPattern2
End If
End With
GetPattern = strPattern
End Function
regex excel vba excel-vba
edited Nov 21 '18 at 13:36
Pᴇʜ
20.2k42650
asked Nov 21 '18 at 13:24
Jose Chiesa
103
I'm getting the error
"Object variable or With block variable not set"
setting the function to the RegEx object, so the thing is that I need to validate a File name with those patterns.
I'm thinking that the Select case it's wrong too. I don't know how to solve it.
Do While xExcelFile <> ""
strPattern = GetPattern(newFileName)
With RegEx
.Pattern = strPattern 'error occurs here
End With
Select Case True
'go to next loop if the selected file and this workbook has the same name
Case xExcelFile = xWname
GoTo NextLoop
'filter by name pattern
Case RegEx.Test(newFileName)
Workbooks.Open fileName:=xSPath & xExcelFile 'open file
Case RegEx.Test(newFileName)
Workbooks.Open fileName:=xSPath & xExcelFile 'open file`
more operational code below
This is the function that searches the desired pattern, and I think its work properly
Function GetPattern(ByVal newFileName As String) As String
Dim strPattern As String
Static RegEx As Object
strPattern1 = "^([B]d{3}w{1,})"
strPattern2 = "^d*.d{4}w*"
If RegEx Is Nothing Then
' To speed the code up set the static RegEx object only once
Set RegEx = CreateObject("VBScript.RegExp")
End If
With RegEx
.Pattern = strPattern1
If RegEx.Test(newFileName) Then
strPattern = strPattern1
End If
.Pattern = strPattern2
If RegEx.Test(newFileName) Then
strPattern = strPattern2
End If
End With
GetPattern = strPattern
End Function
regex excel vba excel-vba
regex excel vba excel-vba
edited Nov 21 '18 at 13:36
Pᴇʜ
20.2k42650
asked Nov 21 '18 at 13:24
Jose Chiesa
103
edited Nov 21 '18 at 13:36
Pᴇʜ
20.2k42650
asked Nov 21 '18 at 13:24
Jose Chiesa
103
edited Nov 21 '18 at 13:36
Pᴇʜ
20.2k42650
edited Nov 21 '18 at 13:36
Pᴇʜ
20.2k42650
edited Nov 21 '18 at 13:36
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 21 '18 at 13:24
Jose Chiesa
103
asked Nov 21 '18 at 13:24
Jose Chiesa
103
asked Nov 21 '18 at 13:24
Jose Chiesa
103
103
Don't put the next loop in the select case; leave that particular case blank, or set i=0 and then for the other operational code, if i<>0 then execute, otherwise end if and moves to next loop. This could lead to issues with overarching with statements, etc.
– Cyril
Nov 21 '18 at 13:28
1
It looks likeRegExis local to theGetPatterncode, which is why you get the error in the calling code.
– Rory
Nov 21 '18 at 13:34
More to @Rory's point, provide the whole proc that contains theDo Whileloop - there are a lot of pieces missing that will help put the puzzle together.
– FreeMan
Nov 21 '18 at 14:45
add a comment |
Don't put the next loop in the select case; leave that particular case blank, or set i=0 and then for the other operational code, if i<>0 then execute, otherwise end if and moves to next loop. This could lead to issues with overarching with statements, etc.
– Cyril
Nov 21 '18 at 13:28
1
It looks likeRegExis local to theGetPatterncode, which is why you get the error in the calling code.
– Rory
Nov 21 '18 at 13:34
More to @Rory's point, provide the whole proc that contains theDo Whileloop - there are a lot of pieces missing that will help put the puzzle together.
– FreeMan
Nov 21 '18 at 14:45
Don't put the next loop in the select case; leave that particular case blank, or set i=0 and then for the other operational code, if i<>0 then execute, otherwise end if and moves to next loop. This could lead to issues with overarching with statements, etc.
– Cyril
Nov 21 '18 at 13:28
Don't put the next loop in the select case; leave that particular case blank, or set i=0 and then for the other operational code, if i<>0 then execute, otherwise end if and moves to next loop. This could lead to issues with overarching with statements, etc.
– Cyril
Nov 21 '18 at 13:28
1
1
It looks like
RegEx is local to the GetPattern code, which is why you get the error in the calling code.– Rory
Nov 21 '18 at 13:34
It looks like
RegEx is local to the GetPattern code, which is why you get the error in the calling code.– Rory
Nov 21 '18 at 13:34
More to @Rory's point, provide the whole proc that contains the
Do While loop - there are a lot of pieces missing that will help put the puzzle together.– FreeMan
Nov 21 '18 at 14:45
More to @Rory's point, provide the whole proc that contains the
Do While loop - there are a lot of pieces missing that will help put the puzzle together.– FreeMan
Nov 21 '18 at 14:45
add a comment |
1 Answer
1
active
oldest
votes
0
Your Case RegEx.Test(newFileName) doesn't make sense.
It should be something like
Do While xExcelFile <> ""
Select Case True
'go to next loop if the selected file and this workbook has the same name
Case xExcelFile = xWname
GoTo NextLoop
Case GetPattern(newFileName) = "^([B]d{3}w{1,})"
'file is pattern 1
Case GetPattern(newFileName) = "^d*.d{4}w*"
'file is pattern 2
answered Nov 21 '18 at 13:31
Pᴇʜ
20.2k42650
Hi @Peh, thanks for your help, this works fine thanks you very much, but I that this could be more improved, o not?
– Jose Chiesa
Nov 21 '18 at 15:08
@JoseChiesa for code that works already but you think it can improved have a look at codereview.stackexchange.com
– Pᴇʜ
Nov 21 '18 at 15:16
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
});
}
});
draft saved
draft discarded
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%2f53413033%2fobject-variable-or-with-block-variable-not-set-using-regular-expressions%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
0
Your Case RegEx.Test(newFileName) doesn't make sense.
It should be something like
Do While xExcelFile <> ""
Select Case True
'go to next loop if the selected file and this workbook has the same name
Case xExcelFile = xWname
GoTo NextLoop
Case GetPattern(newFileName) = "^([B]d{3}w{1,})"
'file is pattern 1
Case GetPattern(newFileName) = "^d*.d{4}w*"
'file is pattern 2
answered Nov 21 '18 at 13:31
Pᴇʜ
20.2k42650
Hi @Peh, thanks for your help, this works fine thanks you very much, but I that this could be more improved, o not?
– Jose Chiesa
Nov 21 '18 at 15:08
@JoseChiesa for code that works already but you think it can improved have a look at codereview.stackexchange.com
– Pᴇʜ
Nov 21 '18 at 15:16
add a comment |
0
Your Case RegEx.Test(newFileName) doesn't make sense.
It should be something like
Do While xExcelFile <> ""
Select Case True
'go to next loop if the selected file and this workbook has the same name
Case xExcelFile = xWname
GoTo NextLoop
Case GetPattern(newFileName) = "^([B]d{3}w{1,})"
'file is pattern 1
Case GetPattern(newFileName) = "^d*.d{4}w*"
'file is pattern 2
answered Nov 21 '18 at 13:31
Pᴇʜ
20.2k42650
Hi @Peh, thanks for your help, this works fine thanks you very much, but I that this could be more improved, o not?
– Jose Chiesa
Nov 21 '18 at 15:08
@JoseChiesa for code that works already but you think it can improved have a look at codereview.stackexchange.com
– Pᴇʜ
Nov 21 '18 at 15:16
add a comment |
0
0
0
Your Case RegEx.Test(newFileName) doesn't make sense.
It should be something like
Do While xExcelFile <> ""
Select Case True
'go to next loop if the selected file and this workbook has the same name
Case xExcelFile = xWname
GoTo NextLoop
Case GetPattern(newFileName) = "^([B]d{3}w{1,})"
'file is pattern 1
Case GetPattern(newFileName) = "^d*.d{4}w*"
'file is pattern 2
answered Nov 21 '18 at 13:31
Pᴇʜ
20.2k42650
Your Case RegEx.Test(newFileName) doesn't make sense.
It should be something like
Do While xExcelFile <> ""
Select Case True
'go to next loop if the selected file and this workbook has the same name
Case xExcelFile = xWname
GoTo NextLoop
Case GetPattern(newFileName) = "^([B]d{3}w{1,})"
'file is pattern 1
Case GetPattern(newFileName) = "^d*.d{4}w*"
'file is pattern 2
answered Nov 21 '18 at 13:31
Pᴇʜ
20.2k42650
answered Nov 21 '18 at 13:31
Pᴇʜ
20.2k42650
answered Nov 21 '18 at 13:31
Pᴇʜ
20.2k42650
answered Nov 21 '18 at 13:31
Pᴇʜ
20.2k42650
20.2k42650
Hi @Peh, thanks for your help, this works fine thanks you very much, but I that this could be more improved, o not?
– Jose Chiesa
Nov 21 '18 at 15:08
@JoseChiesa for code that works already but you think it can improved have a look at codereview.stackexchange.com
– Pᴇʜ
Nov 21 '18 at 15:16
add a comment |
Hi @Peh, thanks for your help, this works fine thanks you very much, but I that this could be more improved, o not?
– Jose Chiesa
Nov 21 '18 at 15:08
@JoseChiesa for code that works already but you think it can improved have a look at codereview.stackexchange.com
– Pᴇʜ
Nov 21 '18 at 15:16
Hi @Peh, thanks for your help, this works fine thanks you very much, but I that this could be more improved, o not?
– Jose Chiesa
Nov 21 '18 at 15:08
Hi @Peh, thanks for your help, this works fine thanks you very much, but I that this could be more improved, o not?
– Jose Chiesa
Nov 21 '18 at 15:08
@JoseChiesa for code that works already but you think it can improved have a look at codereview.stackexchange.com
– Pᴇʜ
Nov 21 '18 at 15:16
@JoseChiesa for code that works already but you think it can improved have a look at codereview.stackexchange.com
– Pᴇʜ
Nov 21 '18 at 15:16
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53413033%2fobject-variable-or-with-block-variable-not-set-using-regular-expressions%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
Don't put the next loop in the select case; leave that particular case blank, or set i=0 and then for the other operational code, if i<>0 then execute, otherwise end if and moves to next loop. This could lead to issues with overarching with statements, etc.
– Cyril
Nov 21 '18 at 13:28
1
It looks like
RegExis local to theGetPatterncode, which is why you get the error in the calling code.– Rory
Nov 21 '18 at 13:34
More to @Rory's point, provide the whole proc that contains the
Do Whileloop - there are a lot of pieces missing that will help put the puzzle together.– FreeMan
Nov 21 '18 at 14:45