ExtendingInsensibility into Code modules. Copy table contents to VBIDE VB Editor code modules. ..

**DocAElstein** · 12-26-2018, 04:37 PM

ExtendingInsensibility into Code modules. Copy table contents to VBIDE VB Editor code modules.

This idea came from a forum question. http://www.eileenslounge.com/viewtopic.php?f=30&t=31395
The idea is basically to use a code module for a temporary storage of the values from a spreadsheet range. So effectively a code module would be used as a pseudo simple text file.
I have a requirement where I have a back up of spreadsheet string values in a text file. As the capacity for holding text in the code window seems to be quite large, I thought I might consider the alternative. I expect other methods , such as the text file storage , will more often be appropriate, but I thought it was a useful additional option to have to hand.
I am sharing some of the coding here.
Initially, however, in these first two Posts, a review of coding applied to the coding environment itself, “coding coding”, as it were.. I won’t do a thorough review, but will try to cover all things relevant to the final coding of this Thread

Post #1: Extensifying VB Editor ability:
Security settings to allow modifying EB Editor things
Extensifying in the VB Editor direction of things might need something checked in security settings:
somehow you may need to “allow access to VBA Projects” or similar.
The exact steps vary a bit in different Excel versions. The steps are similar. Here a few screenshots of the steps in a few different excel versions.
Excel 2007 Options.jpg : https://i.imgur.com/k41o135.jpg http://i.imgur.com/SWlX72z.jpg
Security Center Security Center Settings.JPG : http://i.imgur.com/i1DVr6M.jpg https://i.imgur.com/nzTvpCs.jpg
Macro Setting Access to VBA Project.JPG : http://i.imgur.com/AlCtUeA.jpg http://i.imgur.com/Y9KMHr1.jpg
Excel2003 Tools Macros Security.JPG : http://i.imgur.com/bxfK3ws.jpg http://i.imgur.com/MxfEdfb.jpg
Excel2010 Home Options.JPG : http://i.imgur.com/9K3shBy.jpg http://i.imgur.com/vdjXoFN.jpg
Security Center Security Centre Settings.JPG : http://i.imgur.com/y98yqyM.jpg http://i.imgur.com/j02AY2p.jpg
Security center Settings for macros Access to VBA project.JPG : http://i.imgur.com/WMtcikh.jpg http://i.imgur.com/FPoBiX6.jpg

VBIDE. VBA Projects . Active Workbook, Active project
Sometimes you come across “VBIDE” when looking at these things, which probably comes from an earlier all encompassing idea of the Visual Basic Integrated Development Environment. The visual basic window that you get via Alt+F11 from Excel is more or less something like that development environment type idea.
It is probably best to avoid confusion to have active the workbook whose codes you are manipulating.
It would appear that the word project is used loosely when talking about code related things. By default most “Projects” have the default code name of VBAProject, as seen in the VBA Project explorer window. If you want to keep a slightly better order to your Projects, then you can change that name
VBA Project rename.JPG : http://i.imgur.com/hTzH7NT.jpg http://i.imgur.com/CHKxCrg.jpg
The VB Editor window and components therein appear to have been “built” from some large Class object with the name VBIDE. So in a similar way to that in which we have , for example, Worksheets already instantiated ( “built”) for us from type/ class Worksheet, we also have objects from the VBIDE Class available to us. If we make this Class available to us, as shown below, then we find that we can do a few more things programmatically. For the things done in this Thread, we do not need to do that.. But just for completeness, here are some ways to do that …
Manuel: Tools Extra ScrollTo Extensibility5.3.JPG : http://i.imgur.com/FyXT99z.jpg http://i.imgur.com/mP9gDjv.jpg
Using Coding: VBIDE programmatically.JPG : http://i.imgur.com/FSG1ZMo.jpg http://i.imgur.com/s9Sw1Kk.jpg

Code:

Sub CheckReferrence()
Dim Tool As Object, FlagGotIt As Boolean, strOut As String
' Check if referrence is already there
    For Each Tool In ThisWorkbook.VBProject.References
        If Tool.GUID = "{0002E157-0000-0000-C000-000000000046}" Then
         Let strOut = "Had it already" & vbCrLf
         Let FlagGotIt = "True"
         Exit For ' Needed to keep Tool Set if found
        Else
        End If
    Next Tool
' Check(Add) referrence if not there
    If FlagGotIt = False Then
     ThisWorkbook.VBProject.References.AddFromGuid GUID:="{0002E157-0000-0000-C000-000000000046}", Major:=5, Minor:=3
     Set Tool = ThisWorkbook.VBProject.References("VBIDE") '
    Else
    End If
 Let strOut = strOut & "Description:   " & Tool.Description & vbCr & "Class Name:  " & Tool.Name & vbCr & "Buitin:   " & Tool.BuiltIn & vbCr & "FullPath:   " & Tool.FullPath & vbCr & "Type:   " & Tool.Type & vbCr & "Isbroken:   " & Tool.IsBroken
                                                                                                                                                                                    '       c00 = c00 & vbCr & "Minor:" & vbTab & vbTab & It.Minor  '  (version)
' Display info about Tool                                                                                                                                                                                    '       c00 = c00 & vbCr & "Major:" & vbTab & vbTab & It.Major ' (version)
 MsgBox Prompt:=strOut: Debug.Print strOut
End Sub

Referencing code project things
For the purposes of this Thread, we appear to be able to get easily, even without the above Class VBIDE reference, at the code module which we wish to use. Hard coding to any code module name is possible. The Me property can be used for all but normal code modules for making a code more easy to share and use in any code module. Alternatively the active code module can be referenced. Here are a few ways to assign a variable to use in order to keep the later coding a bit tidier:

Code:

Sub referringToVBEthings()
Dim VBIDEVBAProj As Object
 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBComponents(Me.CodeName).CodeModule 'This will work on all but normal code modules. It will cause  a complie error
 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule
End Sub