create module by means of VBA

[Jan Lichtenbelt]

I want to create a new module in a subroutine. I use excel 2016. In an other subroutine I want to put a string in the new module. The sting will contain a lot of functions and subroutines.

Who can help?

Kind regards

Jan (John)

[Haluk]

In order to do this, you have to;

1) File>> Options>> Trust Center >>>> Click "Trust Center Settings" button. Then, check the option "Trust access to the VBA project object modal"

2) In the VBA editor, Tools >> References find and add the reference "Microsoft Visual Basic for Applications Extensibility 5.3"

Now, copy and paste the following code to a new module.

The following procedure will add a new module named "NewModule" to your project

Please Login or Register  to view this content.

The following code will add a procedure to "NewModule" which is created by the above code...

Please Login or Register  to view this content.

If you want to add procedure or functions that is allready written in a Text file and import them into this "NewModule", then you can use this;

Please Login or Register  to view this content.

If you wish to list all the modules available in your project with their types, this is the code that you can use;

Please Login or Register  to view this content.

If you wish to delete the module "NewModule" in case of you don't need any more, then you can use the below code;

Please Login or Register  to view this content.

[bakerman2]

Please Login or Register  to view this content.

You forgot to include the function.

[Jan Lichtenbelt]

Dear Haluk,

Thanks a lot. I'm very happy with all your subroutines!!! Just what I needed, especially the "Trust access to the VBA project object modal" seems to be essential.

I have one problem. The listing of the modules gives an error on "CompTypeToName(VBComp)" (unknown function or subroutine).

And I have two questions:
1) How to use a particular cell in a worksheet as text file for use in the module?
2) How to close all modules?

Kind regards

Jan (John)

[Haluk]

You forgot to include the function.

I have one problem. The listing of the modules gives an error on "CompTypeToName(VBComp)" (unknown function or subroutine).
...
..

The function, named "CompTypeToName" has been added in post #2

And I have two questions:
1) How to use a particular cell in a worksheet as text file for use in the module?
2) How to close all modules?

I don't know what your particular cell houses, so this will be just a guess......

I assume that, your mentioned "particular cell" is A1 on the active sheet and it is housing a text .... such as;

Please Login or Register  to view this content.

Then, you can add this text as a procedure into module "NewModule" with the code below;

Please Login or Register  to view this content.

And, as per your question #2 .... I didn't understand what you mean by "closing the modules".

.

[Jan Lichtenbelt]

I like the Excel equivalent of the access subroutine:

Sub CloseModules()
'Closes all libraries
'14 september 2006, Jan Lichtenbelt
Dim dbs As Object
Dim obj As AccessObject

Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllModules collection.
On Error Resume Next
For Each obj In dbs.AllModules
DoCmd.Close acModule, obj.Name, acSaveYes
Next obj

Set obj = Nothing
Set dbs = Nothing

End Sub

[Haluk]

I don't have MS Access so .... I don't know what is being done with the code in your message #6

Do you want to close the VBA window itself and see only the Excel window?

Maybe its better if you can provide 2 pictures related with "Closing Modules"...... before and after.

[Jan Lichtenbelt]

I'm working with the Dutch version of Excel 2016.
That means that it is sometimes hard to translate things into the english language.
But what I want mean is that a subroutine closes all module windows, like can be seen in the pictures.

(left side: all windows closed, right side: all module windows open)

[Haluk]

Hi again;

You can try the code below but, note that; "Trust access to the VBA project object modal" option should be active before you run the code.

Please Login or Register  to view this content.

.

[Jan Lichtenbelt]

I changed it into:

Sub Test_Close_CodePane()
On Error GoTo Fout
Application.VBE.ActiveCodePane.Window.Close
Exit Sub
Fout:
MsgBox Err.Number & " " & Err.Description

End Sub

But no error message. Do this subroutine step-by-step, nothing happened. But just run this subroutine, ONLY the Module window with this subroutine closed, all other module windows stayed open.

[Haluk]

Yes, that code closes the active code pane only....

So; if you want to close all of them, then use this;

Please Login or Register  to view this content.

.

[Norie]

Jan

What are you trying to do?

[dmcgov]

@haluk

what command would also close the userform window, i looked though the codes and couldn't find it.

[Jan Lichtenbelt]

Dear Haluk

All works now, as I wanted. Again, great thanks for help. Your solutions are simple, your answers very quick and you listen very good to my questions. That gives this Forum a high level.

Kind regards

Jan