VBA Macro for custom Excel 2010 function works in one workbook, causes invalid name error in another

0
votes

I'm using this VBA macro in an Excel 2010 workbook to define the function FileSize, which enables me to pull the size of a document into a document master worksheet using the filepath.

Function FileSize(FileName As String)
    FileSize = FileLen(FileName)
End Function

I then use the FileSize function to reference a file path string in column A like so:

=FileSize(A1)

This works in the workbook I wrote it for initially, but when I copypaste the macro for the Function into a new module for a new worksheet, I get an invalid name error.

Both workbooks are macro-enabled (.xlsm), and activating or deactivating option explicit hasn't had any effect. What am I doing wrong?/What am I neglecting to do?

2
excelvba

2 Answers

0
votes

#NAME suggests that your new workbook has no idea what the formula =FileSize() is referring to.

  1. Double check to insure that you are actually putting that function in a module in the same workbook in which you are using the formula.
  2. Make sure that you have defined the code as a Function and not a Sub.
  3. Rip your computer off your desk and throw it out the window.
0
votes

Thank you, JNevill! Luckily, defenestration won't be necessary.

I figured it out--I gave my module a name that was identical to the function it contained. Just changed the module name, and now the functions populate correctly.