Adding a File Path and Filename

My file path does not put the brackets around the filename so this wont work.
Is this a latest windows thing?

ie. my exact filename is:

C:\Users\company\Dropbox\TeacherKit\Shared\macro\Adults.xlsx

You can also use =SUBSTITUTE(SUBSTITUTE(CELL("filename"),"[",""),"]",""), if you just want to remove [ ]

You can also use =SUBSTITUTE(SUBSTITUTE(CELL("filename"),"[",""),"]","")

It will give same result like this =SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","")

Bryan, to stay in Aldo's first post's philosophy ... I have been an MS Excel user since version 1.0 came out on Mac (long before there even was a Windows version), and have been programming VBA for Excel as long as it exists. I have some pretty complex VBA driven workbook applications out there, and I manage these types of applications on both Mac and Windows environments. In addition, I have been using (as a paying customer) all of the MS Office apps for as long as they exist (again on both platforms). All of that just to note that (as you mentioned) "I have authority therefore I am right" is not a valid argument in any scientific or technical discussion (or at least it shouldn't be) : I had no clue about what "volatile" meant or did (and not for lack of trying to find out, I searched for a long time trying to understand it and gave up on it in the end). I am soooooooo glad to have had the opportunity to read your post with the explanation, it makes things VERY clear. Sincere thanks from a 30+ year excel experienced user ;-)

Also, Allen, thanks for maintaining this platform here. The MS support websites are useful to find syntax information etc. but finding out why one would want to use a feature in the first place or how to make the most of it is something one can only learn from other users with similar problems to solve.

Thank you for the wonderful tips.

I'm glad I could help. You actually don't even strictly need to use Application.Volatile False, unless it's required by your organization's programming standards, or you just like to have it there for peace of mind. UDFs default to non-volatile. But it doesn't hurt anything to keep it there, either (I guess theoretically if you used the formula thousands of times it could slow it down, but the difference is probably imperceptible compared to whatever the rest of the code is doing).

This is fantastic! This is what was needed, a good clear explanation. No hard feeling here. You are correct that I have used Application.Volatile redundantly in the example I posted, and I should have pointed that out earlier. But I was more focused on the "FileName = ActiveWorkbook.FullName" line so I over looked that fact.

Application.ActiveWorkbook property is inherently volatile and "Application.Volatile" is not needed in this example. But having "Application.Volatile False" used correctly in certain macros can and does speed up calculations on a complex worksheet because a nonvolatile function is recalculated only when the input variables change, thus saving processing time. That is the most common way I use A.V, by creating nonvolatile functions.

And I wasn't aware of the previous posted articles either. I haven't spent much time searching around the site to seeing what was posted. Thanks for the links.

Bryan,

Thanks for this good in-depth explanation of the Volatile function.
It will certainly help me not to miss-use it in future projects.

Aldo,

As I clearly stated ("The fact that *you* describe..."), I was not assuming anything about you, and was only taking at face value what you have said to me. I really could care less about your qualifications or your length of tenure. It's results that matter, and the only thing you can use to defend yourself is the logical fallacy of argument from authority (I have knowledge therefore I am right).

(And let's talk about authority: you make it sound like you were hired by MS or something. Anyone can take the MOS tests. I just looked at the Specialist level, which you claim to be -- there's nothing covered on the outline that I don't know how to do, and I notice a distinct lack of VBA required. So If I make an assumption about you that you actually are an Excel MOS, your qualifications don't apply here. The Expert level looks like it covers super basic VBA stuff -- and again, looks like I could probably pass without training -- but nothing as nuanced as when to use Application.Volatile. Please correct me if the official Microsoft descriptions of the courses have an egregious error. I will thank you for pointing these out to me, however; they would look great on my resume and I’m now going to look into them further.)

So since you refuse to explain why you should use A.V, let me explain why you shouldn't:

Application.Volitile tells Excel to make a user-defined function (UDF) volatile. This means that any time you perform a recalculation, the function will be called again. Worksheet functions that are volatile are things like TODAY and, NOW, OFFSET, and INDIRECT, for which the function value could change based on an external (non-cell linked) parameter. For example, since NOW is volatile, it updates every time you perform a recalculation, so the date/time are constantly updating. If it were not volatile, then Excel would never know to update the time, and you would be stuck in the past until you performed a full recalculation (done either manually or before saving). In automatic calculation mode, a recalculation is called any time you change ANY cell, so in our example from before, NOW updates itself anytime you make a change to the worksheet.

Non volatile functions only recalculate when their precedent cells (the cells used in formulas) have changed. So if you have a formula in cell A1 of =B1, and your formula in cell A2 is =B2, when you change the value of B2, only A2 recalculates and not A1. It is important to avoid volatile functions if you can help it, so that you don't have lots of cells recalculating any time you make a change to *any* cell. This can be done with helper cells (i.e. only putting the date in one cell and having every cell refer to that one; the date cell will recalculate constantly, but the rest will only recalculate when the date changes) or by rewriting formulas to avoid volatile functions entirely. The more volatile functions you have in a worksheet, the more calculations will have to be done at *every* cell change, and the slower your worksheet will run. This is my explanation behind my statement of "The fact that you describe controlling large worksheets leads me to believe that you don't know what it does."

Now let's look at what your function does. ActiveWorkbook.FullName gives you, as it sounds, the full path and filename of the active workbook, i.e. the one you are looking at at the time. (Side note here: since you used ActiveWorkbook.FileName instead of Application.Caller.Parent.Parent.FileName, if you have two workbooks open and change a cell in the second workbook, your function in the first workbook will change to reflect the file path of the second book. Try it out if you don't believe me.) Now if you want to know if your function should be volatile, you have to ask yourself the question: Does the function depend on external inputs? In this case, the answer is yes (the file name is not a range value passed as a variable), but you have to ask yourself the important follow-up question: Does the external dependency have a value that changes? Since your file name is not something that changes as you use the worksheet, the answer is clearly no. Therefore, you do not need to and should not use Application.Volatile in your FileName function. And again, your file name has ABSOLUTELY NOTHING TO DO WITH (1) using A.V "all the time"; (2) "control[ing] macros in very large worksheets"; and least of all (3) "programming Excel since Win3.x days which was Office 4", and none of these things should be deciding factors for using it.

Now you might be saying: Bryan, but what about if I do a "save as"? Won't the file name change then? Why yes it will. But here's the thing: a full recalculation is performed whenever you reopen the workbook, so your file will be in sync the next time you open it. And there's the other kicker: your workbook recalculates *before* a save, not after, so even with A.V in the program, you will still have the wrong file name until you edit another cell. If you are worried about having the most up-to-date file name for a save 'n' print, *adding the A.V line isn't good enough*. If you are in a situation where you routinely use Save As, Application.Volatile could be a safety measure, but it's still less than ideal because of all the times you are recalculating when you don't need to. A better solution would be to update the Workbook_Print event and add the line "Application.CalculateFull".

Therefore, Aldo, there is no circumstance I can find for which you should use Application.Volatile in your function. I reiterate my prior statement: "Aldo, take out Application.Volatile. You've picked up one of Allen's bad habits."

(Perhaps you also want an explanation of the second sentence? Check out this page, where I explained to Allen that his own article on A.V was incorrect, and he used my comments to completely change the article: http://excelribbon.tips.net/T012654_Identifying_Digit-Only_Part_Numbers_Excluding_Special_Characters.html)

Smug passive-aggressive smile right back atcha :)

Bryan, I appreciate that fact that you don't who I am, what I do or know, and that is hard to show sometimes with texting/blogging/whatever. Many people get their hairs on-end because of it.

So I will tell you (and others) this ... I am Microsoft Certified Specialist in Excel. I did the work and went to MS and did the tests. I know exactly what "Application.Volatile" is and does. I have been using and programming Excel since Win3.x days which was Office 4.

I am part of this group because I always like to know what others are doing, find out about little tricks and work-arounds that others are using, and to help where I can to make this site the best it can be.

The best thing for you to do instead of "assuming" what others know, is to write an explanation as to why you feel strongly about "Application.Volatile" and it use. That way everyone here can learn about it and use it or not.

Happy coding :-)

There are uses for A.V, but you macro does not demonstrate one of them. "Using it all the time" means nothing. The fact that you describe controlling large worksheets leads me to believe that you don't know what it does.

Bryan ... I use it "Application.Volatile" all the time to control my macros in very large worksheets I create. It has it's purpose.

Aldo, take out Application.Volatile. You've picked up one of Allen's bad habits.

I agree with John, always add a cell range. This applies to Windows machines as well.

If you don't add a cell reference, and you have multiple files open, all files with this formula will display the path of the file last calculated.

Many years ago I had a problem using a cell with data in it. This may not apply anymore, but I have a habit of using a cell that is likely to be empty, like AZ7 (I have a macro that inserts the formula).

In the Header/Footer sections Alt + ___ will yield:

P=pathfile
A=tab name (I like to insert a "" between the pathfile & tab name)
D=date
M=time
T=page #
U=# of pages
F=font dialog box
L, C or R=jump to Left, Center or Right sections.

I use this macro when I need a file name string in my projects. I actually have a Add-In file that has many macros I use all the time, this being one of them.


Public Function FileName() As String
Application.Volatile
FileName = ActiveWorkbook.FullName
End Function

CELL works the same way on the PC version. This is definitely an unexpected outcome, so users should take note.

The CELL function has a second, optional but important argument that specifies which cell is referenced. If omitted, the function will refer to the most recently updated cell of the workbook which may not be on the worksheet of interest.

To play it safe, specify a cell on the worksheet for which the path, filename and tab name are desired. For example, =CELL("filename",A1) if inquiring about the worksheet on which the CELL function appears.

At least this is how CELL works on my 2008 Mac version of Excel.