Solved

Placement of a Function

Posted on 2016-07-26
40
78 Views
Last Modified: 2016-08-11
Hi All,

i am using the following basic Function to collate a table of comments into a single cell for onward copying into a consolidation file:

Public Function AllComments(Rng As Range)

R = 0
LR = Rng.Cells.Count
For Each Cell In Rng
    Newline = ""
    R = R + 1
    If R < LR Then Newline = Chr(10)
    Collated = Collated & Cell.Text & Newline
Next Cell
    AllComments = Collated
End Function

Open in new window

I am distributing this Function to numerous project files and I was looking to Automate the process.

The function is currently held in a Standard module and I am having to copy the code in the VBE and copy the sheet on which it is used.

Can the function be in the sheet code rather than a module? How do I get the Function to work when it is in the sheet code; I couldn't get it to work like that which is why it is in a standard module.

Also, on a separate but related issue: the file from which I am copying the sheet has lots of Named Ranges. When I copy the sheet I end up with copies of the Named Ranges in the destination file. The destination file already has all of these Named Ranges so I get two copies of each Named Range, one with Workbook scope and one with scope of the copied sheet.

On this occasion the sheet is very simple so it would be just as simple to insert a sheet and copy paste the contents onto the new sheet but I have seen this occur before and on those occasions it wasn't as simple so have had to go in and delete the surplus Named Ranges. I have tried recording this process with the VB Recorder but the script that I end up does not show any indication that it is the Sheet scope Range that it is deleting. Is there a way of defining this when deleting the range name?

Thanks
Rob H
0
Comment
Question by:Rob Henson
  • 20
  • 10
  • 7
  • +1
40 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 41729538
Rob

If you put it in a sheet module you would need to include the sheet's  codename when calling the function from anywhere other than the module it's in.

For example if it was in the module of a worksheet with the codename Sheet1 you would use Sheet1.AllComments.

As for the named range thing, which named ranges would you want to delete?
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41729544
How do I then use the Function as a formula on the Worksheet?

I have just tried moving the function to the relevant sheet (codename - Sheet5; tab name - VAR)

I tried =Sheet5.AllComments and it didn't accept it.

Thanks
Rob
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 41729620
To eliminate the named ranges you could do this after pasting the sheet.

Dim NR As Name

With ActiveSheet
    For Each NR In .Names
        NR
    Next
End With

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41729624
I'm not sure what "I am distributing this Function to numerous project files" means but you could put the standard module and the function in a template file and deliver the template instead.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41729625
But I only want to eliminate those that are Sheet scope. Those that are Workbook scope need to stay but they have the same name as those with sheet scope.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41729628
I have already implemented it in a template for future project files. I now need to implement it in the existing files.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41729635
But I only want to eliminate those that are Sheet scope. Those that are Workbook scope need to stay but they have the same name as those with sheet scope.
I didn't test it but I believe that it will only eliminate the ones on the active sheet.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 250 total points
ID: 41729654
didn't realise you were using the function on a work sheet.

If you are then it has to be in a standard module as far as I'm aware.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41729665
@Martin - with the amendment as below, it worked. Thanks.
Dim NR As Name

With ActiveSheet
    For Each NR In .Names
        NR.Delete
    Next
End With

Open in new window

@Norie - if it has to be on a Standard Module, I could put it on its own module in the template. Can I then have a routine that copies that module to the file, Alternatively, I could export the module first and then import it into the other file. How do I do that?

Thanks
Rob H
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41729694
@Martin - the question regarding the removal of Range Names maybe should have been a question in its own right. In your position as administrator are you able to move the Range Name related comments to their own question. I will then accept your solution on that as it deserves full quotient of points rather than just an Assisted marking on this question.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41729698
I'm happy with assisted but you could contact a moderator via Request Attention.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41729699
@ Norie - Phrasing the question differently, how do I import the exported module .bas file into a file using VBA?

Likewise, how do I get rid of existing modules?

The various project files that I am working with have come from various iterations of the Template file that I am working on so have inherited some of the modules that are now in the template file. The only module that should be in the Project files is the one with the Function as above.

As I open each of the project files I am going through a cleansing exercise deleting other modules as well as implementing the new feature for the collated comments (the function above).
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41729945
You could put the code in a module in an add-in.  That way, it will be available to all workbooks after the add-in has been added.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41729987
how do I import the exported module .bas file into a file using VBA?
Ron de Bruin has this code you can use. But note that he says "Run the ImportModules macro (It delete all existing modules/userforms from [the target] Workbook first)".

Public Sub ImportModules()
    Dim wkbTarget As Excel.Workbook
    Dim objFSO As Scripting.FileSystemObject
    Dim objFile As Scripting.File
    Dim szTargetWorkbook As String
    Dim szImportPath As String
    Dim szFileName As String
    Dim cmpComponents As VBIDE.VBComponents

    If ActiveWorkbook.Name = ThisWorkbook.Name Then
        MsgBox "Select another destination workbook" & _
        "Not possible to import in this workbook "
        Exit Sub
    End If

    'Get the path to the folder with modules
    If FolderWithVBAProjectFiles = "Error" Then
        MsgBox "Import Folder not exist"
        Exit Sub
    End If

    ''' NOTE: This workbook must be open in Excel.
    szTargetWorkbook = ActiveWorkbook.Name
    Set wkbTarget = Application.Workbooks(szTargetWorkbook)
    
    If wkbTarget.VBProject.Protection = 1 Then
    MsgBox "The VBA in this workbook is protected," & _
        "not possible to Import the code"
    Exit Sub
    End If

    ''' NOTE: Path where the code modules are located.
    szImportPath = FolderWithVBAProjectFiles & "\"
        
    Set objFSO = New Scripting.FileSystemObject
    If objFSO.GetFolder(szImportPath).Files.Count = 0 Then
       MsgBox "There are no files to import"
       Exit Sub
    End If

    'Delete all modules/Userforms from the ActiveWorkbook
    Call DeleteVBAModulesAndUserForms

    Set cmpComponents = wkbTarget.VBProject.VBComponents
    
    ''' Import all the code modules in the specified path
    ''' to the ActiveWorkbook.
    For Each objFile In objFSO.GetFolder(szImportPath).Files
    
        If (objFSO.GetExtensionName(objFile.Name) = "cls") Or _
            (objFSO.GetExtensionName(objFile.Name) = "frm") Or _
            (objFSO.GetExtensionName(objFile.Name) = "bas") Then
            cmpComponents.Import objFile.Path
        End If
        
    Next objFile
    
    MsgBox "Import is ready"
End Sub

Function FolderWithVBAProjectFiles() As String
    Dim WshShell As Object
    Dim FSO As Object
    Dim SpecialPath As String

    Set WshShell = CreateObject("WScript.Shell")
    Set FSO = CreateObject("scripting.filesystemobject")

    SpecialPath = WshShell.SpecialFolders("MyDocuments")

    If Right(SpecialPath, 1) <> "\" Then
        SpecialPath = SpecialPath & "\"
    End If
    
    If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = False Then
        On Error Resume Next
        MkDir SpecialPath & "VBAProjectFiles"
        On Error GoTo 0
    End If
    
    If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = True Then
        FolderWithVBAProjectFiles = SpecialPath & "VBAProjectFiles"
    Else
        FolderWithVBAProjectFiles = "Error"
    End If
    
End Function

Function DeleteVBAModulesAndUserForms()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        
        Set VBProj = ActiveWorkbook.VBProject
        
        For Each VBComp In VBProj.VBComponents
            If VBComp.Type = vbext_ct_Document Then
                'Thisworkbook or worksheet module
                'We do nothing
            Else
                VBProj.VBComponents.Remove VBComp
            End If
        Next VBComp
End Function

Open in new window

0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41730740
@aikimark - I don't believe an Add-In will be suitable as that would be specific to a machine.  Although I am administering these Project files and implementing this feature they are looked after on a daily basis by a group of Project Managers in various offices.  Also we have hot-desk policy in our workplace so can't guarantee which desk we will be using.

@Martin - I will try Ron De Bruin's code. The note that you highlight is what I want to do anyway, remove all and leave only the one that I import which has the function in it.

Thanks
Rob
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41730772
I get a Compile error on this line:

Dim objFSO As Scripting.FileSystemObject

and on:

Dim VBProj As VBIDE.VBProject

I am using Excel 2010, does that make a difference?
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 250 total points
ID: 41730956
To use the code Martin posted you'll need references to the Microsoft Scripting Runtime and Microsoft Visual Basic for Applications Extensibility 5.3 libraries.

You might also have to set the security level to allow programmatic access to the VBA project model.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41730968
Norie, how do I check to see if I have or get the references and libraries?

If they aren't standard with Excel 2010 then I doubt I will be able to download. We have very strict download protocols because of the nature of our work.

Thanks
Rob
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41730996
Found them in the References list and ticked to "activate" them.

Should the script from Ron now just work???

It would appear not.

My References dialogue looks like:References
Should there be anything else included?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41731228
Try it and see.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 31

Author Comment

by:Rob Henson
ID: 41731274
Still get the same error. Object not defined.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 250 total points
ID: 41731516
I made some changes to the code. Please see lines 10 and 11. Also note that in order to be able to update the target workbook, its  File -> Options -> Trust Center -> Trust Center Setttings -> Macro Settings -> Trust Access to the VBA Project object mode must be set.

Option Explicit

Public Sub ImportModules()
    Dim wkbTarget As Excel.Workbook
    Dim objFSO As Scripting.FileSystemObject
    Dim objFile As Scripting.File
    Dim szTargetWorkbook As String
    Dim szImportPath As String
    Dim szFileName As String
    Dim cmpComponents As VBIDE.VBComponents
    'new
   ' Change this to match the path where you have the file(s) to be imported.
    Const FolderWithVBAProjectFiles = "C:\MyPath"
    
    If ActiveWorkbook.Name = ThisWorkbook.Name Then
        MsgBox "Select another destination workbook" & _
        "Not possible to import in this workbook "
        Exit Sub
    End If

    'Get the path to the folder with modules
    'new
'    If FolderWithVBAProjectFiles = "Error" Then
'        MsgBox "Import Folder not exist"
'        Exit Sub
'    End If

    ''' NOTE: This workbook must be open in Excel.
    szTargetWorkbook = ActiveWorkbook.Name
    Set wkbTarget = Application.Workbooks(szTargetWorkbook)
    
    If wkbTarget.VBProject.Protection = 1 Then
    MsgBox "The VBA in this workbook is protected," & _
        "not possible to Import the code"
    Exit Sub
    End If

    ''' NOTE: Path where the code modules are located.
    szImportPath = FolderWithVBAProjectFiles & "\"
        
    Set objFSO = New Scripting.FileSystemObject
    If objFSO.GetFolder(szImportPath).Files.Count = 0 Then
       MsgBox "There are no files to import"
       Exit Sub
    End If

    'Delete all modules/Userforms from the ActiveWorkbook
    Call DeleteVBAModulesAndUserForms

    Set cmpComponents = wkbTarget.VBProject.VBComponents
    
    ''' Import all the code modules in the specified path
    ''' to the ActiveWorkbook.
    For Each objFile In objFSO.GetFolder(szImportPath).Files
    
        If (objFSO.GetExtensionName(objFile.Name) = "cls") Or _
            (objFSO.GetExtensionName(objFile.Name) = "frm") Or _
            (objFSO.GetExtensionName(objFile.Name) = "bas") Then
            cmpComponents.Import objFile.Path
        End If
        
    Next objFile
    
    MsgBox "Import is ready"
End Sub
'new
'Function FolderWithVBAProjectFiles() As String
'    Dim WshShell As Object
'    Dim FSO As Object
'    Dim SpecialPath As String
'
'    Set WshShell = CreateObject("WScript.Shell")
'    Set FSO = CreateObject("scripting.filesystemobject")
'
'    SpecialPath = WshShell.SpecialFolders("MyDocuments")
'
'    If Right(SpecialPath, 1) <> "\" Then
'        SpecialPath = SpecialPath & "\"
'    End If
'
'    If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = False Then
'        On Error Resume Next
'        MkDir SpecialPath & "VBAProjectFiles"
'        On Error GoTo 0
'    End If
'
'    If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = True Then
'        FolderWithVBAProjectFiles = SpecialPath & "VBAProjectFiles"
'    Else
'        FolderWithVBAProjectFiles = "Error"
'    End If
'
'End Function

Function DeleteVBAModulesAndUserForms()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        
        Set VBProj = ActiveWorkbook.VBProject
        
        For Each VBComp In VBProj.VBComponents
            If VBComp.Type = vbext_ct_Document Then
                'Thisworkbook or worksheet module
                'We do nothing
            Else
                VBProj.VBComponents.Remove VBComp
            End If
        Next VBComp
End Function

Open in new window

0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41731555
Travelling at the minute so won't be able to check until back in the office tomorrow.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41731585
You could reference the function with a fully pathed workbook name.
Example:
='C:\Users\AikiMark\Documents\CodeMaster.xls'!AllComments(A2:D4)
='\\AllUsers\CommonCode\CodeMaster.xls'!AllComments(A2:D4)

Open in new window

0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41732932
Surely, having the function in a separate workbook would have the same issues as an Add In?

Each user would have to have a copy of the Codemaster file in their User directory.

Or.... could the file containing the function be in the same directory as the Project Files??? Currently in the same directory there is a file that gets used by all Project files for reference for exchange rates, period dates etc etc.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41732954
@Rob

It is another way to let the user know that they need to do something.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41732973
Sorry Aikimark, don't understand that last comment!!

I am not expecting the users to do anything at all. They are filling in effectively a table with variance analysis explanations consisting of columns for Date, Value and Reason. There will be multiple lines in the table for numerous variances.

The function then collates the comments into one cell so that a consolidator file that pulls info from all the files can just look at the one cell to get the comments.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41733074
@Rob

If you can't add a module to the workbooks or have the people/group policy/administrative automation do an add-in to the users'  Excel environments, then what kind of 'magic' do you expect?  You could perform the concatenation with an external batch program or as part of the importing/consolidation process as a work-around to 'magic'.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41733119
I already have the Function written to do the concatenation 'magic' and I am wanting to distribute that into 70+ project files. The consolidation to the summary file is then just simple formula linking to a specific cell/range.

I can do the distribution manually by opening each file and copy and pasting in the VB Editor; and removing other surplus modules while I am in the file.

I was looking for a VBA script (such as Ron De Bruin's code) that would do the clean-up and import for me at the click of a button (or wave of a wand!!). I already have a short script that is copying a sheet from a template file which has the table and concatenation function on it; it is then the copying of the VBA behind the function that I need to copy in.

Unfortunately, I can't get Ron's code to work.

Are you able to answer the question regarding having the function in a central file that each of the project files already link to?

Thanks
Rob
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41733144
Unfortunately, I can't get Ron's code to work.
Did you try the version I modified? If so please explain what doesn't work.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41733158
You could include a hyperlink cell that the user can click if they don't have the workbook with the code.  That still means that the data sheets, themselves would need to be updated.
Example:
=HYPERLINK("c:\users\Aikimark\documents\codemaster.xls","AddMissingWorkbook")

Open in new window

A simpler solution would be to change the data sheets workbook to add the necessary code or open (read only) workbook with the code.  That still means changing something.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41733267
Hi Martin, sorry hadnt seen the updated code. I am away from work now so will try it tomorrow.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41733286
Hi Aikimark, I am getting confused by your suggestion.

I have written a basic Function script that looks at a range and concatenates the text from each cell in that range with a carriage return. This function gets used on a separate worksheet. The function script is on a separate standard module.

I am going through a number of project files copying the worksheet and the module from a template file. The template file will be used for future projects.

I have written a small script that is copying the worksheet and wanted to add to it extra lines for copying the module or just the function script.

If instead I can reference the function in a formula when the function is in a module in another workbook I would happily do that but not via hyperlink or anything, just a straight formula as it would be if the function was in the same workbook.

Thanks
Rob
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41733399
You can reference code in another workbook, but that workbook needs to be open.  Clicking on a hyperlink will do that.

You can push the code in different ways (already mentioned) and can automate the process with VBScript, Powershell, and possibly through an AD group policy.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41733446
Ron's code assumes that the code that I posted will be in workbook that is the driver of, rather than the target of the code, and that a second workbook will be open which will be where the imported module(s) will wind ip.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41733535
You can also provide a link for the user that will invoke a script that will open both workbooks.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41734294
HI Martin,

That now appears to be working with a test file. I will see if I can implement it with the real files.

I already have the Module exported so I only need the Delete existing and Import routines.

I will let you know how I get on.

Thanks
Rob
0
 
LVL 31

Author Closing Comment

by:Rob Henson
ID: 41734303
Got there in the end.

Many thanks to all who contributed.

Thanks
Rob
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41734679
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 41752313
Hi Martin,

I will be raising a follow on question to this shortly. Your input will be much appreciated.

Thanks
Rob
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now