If Statement That Cancels Sub Based on File Name

Question

I am trying to implement and If statement where if it is true, a message box is displayed and the sub is not carried out and if it is true, the sub is carried out like normal.

With my current code, the sub is always carried out, even though the If condition should be met. I am not sure where I have gone wrong:

Dim templateFile As String
templateFile = "T:\Sales and Subs Dept\ARK - Dev\ALPHA TEMPLATE w.o Loop.xlsm"

If Filepath = templateFile Then
    MsgBox "Please save template as different document"
    Exit Sub
Else

All other code goes below the else and concludes with an End If.

Have you tried using `debug.print` to see the value of `Filepath ` immediately before your `if` statement? — cybernetic.nomad, Feb 05 '19 at 18:27
There is nothing wrong with the logic of how you are using `Exit Sub` in the `If` statement -- the problem is that your condition for exiting is never being met. — John Coleman, Feb 05 '19 at 18:34
Any ideas for why the condition may not be met? I just used debug.print to check the value of the templateFile variable, and it matches the file name of the document I currently have open. What I am trying to do is prompt the user to save as if templateFile matches the name of the currently open file — Geni O, Feb 05 '19 at 18:37
Place a breakpoint (F9) on the `If` statement, then run the code and when the breakpoint is hit, hover `Filepath` and then hover `templatefile`. Do they match? — Mathieu Guindon, Feb 05 '19 at 18:39
If you are never seeing `MsgBox "Please save template as different document"` then `Filepath` is never equal to `templateFile`. Perhaps you just are not perceiving the difference (which could be in white space). For example, what is `debug.Print (Len(Filepath) = Len(templateFile))`? — John Coleman, Feb 05 '19 at 18:42
I've just checked this and filepath is empty. Is there a particular methodology for getting the name of the currently open file into a variable? — Geni O, Feb 05 '19 at 18:43
Just out of best practice, i would avoid using spaces in file paths. If you have to use a space, perhaps an underscore would be better suited. — IrwinAllen13, Feb 05 '19 at 18:44
to get the file path...check this out... https://stackoverflow.com/questions/2813925/how-to-get-the-path-of-current-worksheet-in-vba — IrwinAllen13, Feb 05 '19 at 18:44
Setting filepath equal to ActiveWorkbook.FullName fixed the issue. My beginner's mistake was thinking filepath was a function that automatically fetched the file path for you. Thanks a ton — Geni O, Feb 05 '19 at 18:51

Cyril · Accepted Answer · 2019-02-06T13:35:53.060

-2

Post edited after accepting per the discussion in the comments below.

Added Option Explicit and specified how to list the Filepath, which should keep things similar:

Option Explicit
sub fdsa()
    Dim templateFile As String, Filepath As String
    Filepath = Application.ActiveWorkbook.path & ActiveWorkbook.Name & ".xlsm"
    templateFile = "T:\Sales and Subs Dept\ARK - Dev\ALPHA TEMPLATE w.o Loop.xlsm"
    If Filepath = templateFile Then 
        MsgBox "Please save template as different document."
        Exit Sub
    Else
        'run your macro where case isn't true
    End If
end sub

edited Feb 06 '19 at 13:35

answered Feb 05 '19 at 18:42

Cyril

6,448
1
18
31

3

Interesting: What is your logic that allows for a `goto` to be cleaner/simpler? `Goto`s create spaghetti code that is anything but. Especially as this example is well served by an `If-The-Else-Endif` – AJD Feb 05 '19 at 18:57
@AJD Without seeing the other code to be entered where the comment was left in the posted snippit, I made a suggestion, which wasn't directly intended to answer the question. From having others review my code, adding Goto items for items which exit sub, rather than using if-statements, was the general consensus or at least the common feedback I was given. After a few years of having that beaten into my head, I suggested here. I guess I should ask, have I had bad associates reviewing or does that seem reasonable? – Cyril Feb 05 '19 at 19:33
You could read: [Goto Considered Harmful](http://www.u.arizona.edu/~rubinson/copyright_violations/Go_To_Considered_Harmful.html). They tend to be frowned upon, though VBA itself is a bit of an old-school language in which at least occasionally a goto is the best option. I don't think that this is one of those occasions, but it is somewhat similar to VBA error handling and is readable enough. I've never heard of Gotos being advocated like that. Perhaps you are working somewhere which has a large VBA code base which is written in a certain style and the intention is to enforce consistency? – John Coleman Feb 06 '19 at 12:17
1

@JohnColeman I asked a few people and you seem to be spot on... apparently the previous Manager (primary reviewer) had that style, pretty much requiring it... since things get passed around between reviewers, the style was kept consistent, leading to that being listed as the "best practice" here. We have one older fellow who can't remember if he learned that here or if that was something he was taught back in the day. Thanks for the link and sorry to other folks who read this post when the suggestion for goto was still there; i have since edited. – Cyril Feb 06 '19 at 13:39

If Statement That Cancels Sub Based on File Name

1 Answers1