fill selected cell with formula

[Greasle]

#Newbie
Hi all
I tried to write a sub or a function to populate a selected cell with the formular below:

=SUBSTITUTE(LEFT(CELL("filename"), FIND("]", CELL("filename")) - 1), "[", "")

But i fail. Used examples but no luck.

Beware the formula works only with a saved workbook

anyone?

 

[Yongle]

Another UDF to give the full path

Formula in cell
=GetFullPath()

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

Again - must be placed in Standard module
Function should show (alongside built-in functions) as you begin to type = GetFull....

Note
The function refreshes whenever the workbook recalculates

 

[Yongle]

Excel does not automatically recalculate when using SaveAs

Placing this in ThisWorbook module forces the workbook to recalculate when file is saved
- it refresh the value in the cell containing the UDF if the file has been saved with a different name

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    With Application
        .Calculation = xlManual
        .Calculation = xlCalculationAutomatic
    End With
End Sub

 

[Aladin Akyurek]

full path including filename. Just the formula I posted.

If you enter the formula say in A1, try...

=SUBSTITUTE(LEFT(CELL("filename",A1), FIND("]", CELL("filename",A1)) - 1), "[", "")

You need thus anchor the formula to a cell, preferably to the cell in which you implement it.

 

[Greasle]

Where did you paste the function?
- It must be in a standard module (not Sheet module, not ThisWorkbook module)

standard module

 

[Yongle]

Did you try UDF in post#11?

 

[Greasle]

Did you try UDF in post#11?

Yes. same result "#name?"

Invalid name error
the formula contains unrecognized text

windows 10 Office 16 here

 

[Greasle]

Personal.xlsb makes the problem.

The module is in Personal.xlsb
if the function is in a module of a book1.xlsx or xlsm there is no problem.

tested
Function FilePath()
Application.Volatile
FilePath = Evaluate("=SUBSTITUTE(LEFT(CELL(""filename""), FIND(""]"", CELL(""filename"")) - 1), ""["", """")")
End Function

The sub however work fine with Personal.xlsb

 

[Greasle]

https://www.myonlinetraininghub.com...personal-xlsb-for-user-defined-functions-udfs seems to give a solution

 

[Greasle]

not a very nice solution.

The sub Yongle wrote works great for me within Personal.xlsb. Thanks again for that.