How to use VBA to write VBA code

Question

I have been looking into using VBA to write VBA Code. So that ultimately I can write an algorithm to write loops depending on a variable entered.

I have come across the below, which looks as if it will add code to the VBA project but I am missing something for it to work. Struggling to find anything on this. I know that 'ThisWorkbook' is the issue. Any ideas to what I need to call it to be able to reference that sheet (same name) in the VBA project.

Thanks

Option Explicit

Sub test()

Dim WB_Write_Code_Test As Workbook
Dim WS_Test As Worksheet
Dim nextline As Long

Set WB_Write_Code_Test = Workbooks.Open("Write Code Test")

Set WS_Test = WB_Write_Code_Test.Sheets("Test")

With ActiveWorkbook.VBProject. _
VBComponents(ThisWorkbook).CodeModule

nextline = .CountOfLines + 1

.InsertLines nextline, WS_Test.Cells(1, 1).Value
End With

End Sub

You could write a vba module then import it into the workbook, or generate the code in the Console then copy/paste it. I don't think it's a good idea to write vba code for writing vba code... sounds strange. — exSnake
– exSnake, Commented Aug 28, 2017 at 14:51
what is the specific failure you're having? Is ThisWorkbook the same as ActiveWorkbook? — David Zemens
– David Zemens, Commented Aug 28, 2017 at 14:51
So the idea is that the code will run and add code depending on the variables. I can just delete it all and replace with original code at the end. (saves me creating the code and then copying & paste and then running it?) The specific issue (I think) is the ThisWorkbook. ThisWorkbook refers to the VBA Project sheet I am writing code into. ActiveWorkbook is the excel doc itself. — Jon Smith
– Jon Smith, Commented Aug 28, 2017 at 14:59
Or if I wanted a code that could 'learn' I would need it to be able to write code directly — Jon Smith
– Jon Smith, Commented Aug 28, 2017 at 15:12
Be aware you need to specifically allow access to VBProject using VBA in the Excel macro options. — Erik Westwig
– Erik Westwig, Commented Aug 28, 2017 at 15:53

Jon Smith · Accepted Answer · 2017-08-28 16:49:57Z

1

Thanks, That has done it.

For reference the missing code is:

VBComponents(ThisWorkbook.CodeName).CodeModule

answered Aug 28, 2017 at 16:49

Jon Smith

931 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use VBA to write VBA code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related