Function defined in worksheet code module is not found

[rbaumann]

I am running Excel 2010 on a Windows 64-bit machine. I have a workbook with several worksheets, each of which contains data about a different folder on my file system. I have a general code module that contains VBA code that is used across all of the worksheets. It works fine. But I have been trying to create a function called FolderPath () in each of the worksheet code modules that returns the full path to the folder to which the worksheet pertains. My plan was that if I were on Sheet1, then invoking the FolderPath() method would activate the FolderPath() method in the Sheet1 worksheet code module, and invoking the FolderPath() method while I had Sheet2 active would activate the FolderPath() method in the Sheet2 worksheet code module, and so forth. So depending on which worksheet I had open I would get a value from the FolderPath() method that was appropriate for that worksheet. I have entered the FolderPath() method on each worksheet's code module by right-clicking on that worksheet's tab and selecting the Show Code entry from the context menu, so I'm pretty sure that I'm entering the code in the worksheet code module.

The problem is that when I am viewing any given worksheet and enter a reference to the worksheet function in any cell (e.g. =FolderPath()), I get a NAME! error. If I move the function to a general code module, Excel finds it with no problem, but it can't seem to find anything I put in the worksheet code module. I would have thought that the current worksheet's code module would be a place that Excel would automatically search when trying to locate a user-defined function, but that doesn't seem to be happening in my situation.

Is there something I need to do to get Excel to look in the worksheet code modules? Thank you.

 

[StephenCrump]

Welcome to the Forum!

You can call a Sub or Function in another module by qualifying appropriately, something like:

'In a code module
Function FolderPath() As String

    FolderPath = ActiveSheet.FolderPath

End Function
'In Sheet1 module etc etc
Function FolderPath() as String

    FolderPath = "This is folder path for Sheet1"

End Function

But why would you want to duplicate code in each sheet module? Wouldn't it be better to use a single function?

 

[rbaumann]

Hi Stephan, your code worked! Thank you very much!

Regarding your question, I'm not actually duplicating code in each sheet module. Each sheet module has a function called FolderPath but each such function returns a different value, which is the path to the folder on the file system that contains the data for the corresponding study. Here's an example. Let's say I have a workbook with three worksheets, named Study1, Study2, and Study3. In my file system there are three different folders that hold materials related to each of the three studies, say \\share\studies\study1, \\share\studies\archived\study2, and \\share\studies\archived\study3. The worksheet code module for the Study1 worksheet would have a function as follows:

Public Function FolderPath() as String

FolderPath = "\\share\studies\study1"

End Function

The worksheet code module for the Study2 worksheet would have a function as follows:

Public Function FolderPath() as String

FolderPath = \\share\studies\archived\study2

End Function

and similar for the Study3 worksheet.

Additionally, say that each of these folders contains a document called study_status.docx. In each of my three worksheets I have a text string in cell A1 that says "Date of Last Update to Study Status" and a formula in cell A2 that gives the last modification date of the study's corresponding study_status.docx document. So in my general code module I would have a function such as the following (using your solution):

Function StudyStatusLastModificationDate() as String

StudyStatusLastModificationDate = FileDateTime (ActiveSheet.FolderPath & "\study_status.docx")

End Function

With this arrangement, then the coding behind each of the three worksheets would be simplified, i.e. no IF ... THEN ... ELSE coding to distinguish among studies. In each of the three tabs, the coding behind cell A2 would simply be =StudyStatusLastModificationDate(). Behind the scenes, the StudyStatusLastModificationDate method would invoke the ActiveSheet.FolderPath() method to get the path to the specific study_status.docx document for the currently active worksheet.

Thanks again for your solution, it works perfectly.

Richard