Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Option Explicit
- Public Sub AddNewWorkBookTEST()
- Dim nextline As Long, LastUsedRowList As Long
- Dim CodeString As String
- Dim x As Long
- Dim KWATT As Double
- Dim folderPath As String
- folderPath = Application.ActiveWorkbook.Path
- LastUsedRowList = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row
- For x = 1 To LastUsedRowList
- KWATT = Sheet4.Cells(x, 1)
- CodeString = CodeStringGenerator(KWATT)
- ''Update the module code
- With ActiveWorkbook.VBProject.VBComponents("MyNewTest").CodeModule
- .DeleteLines 1, .CountOfLines
- End With
- With ActiveWorkbook.VBProject.VBComponents("MyNewTest").CodeModule
- nextline = .CountOfLines + 1
- .InsertLines nextline, CodeString
- End With
- CallOtherModule x
- ''Calling the function in the second module (where the code was copied).
- '''Cannot call the function directly from this sub, since excel will crash:
- Call MyNewTest.SortedArray(x)
- Next x
- End Sub
- ''''''''''''''''''''''''''''''''''''''''''''''''''''''
- Public Sub CallOtherModule(ItemsCounter As Long)
- Call MyNewTest.SortedArray(ItemsCounter)
- End Sub
- ''''''''''''''''''''''''''''''''''''''''''''''''''''''
- '''The function that writes the code of the second module as String
- Function CodeStringGenerator(KWATT As Double) As String
- CodeStringGenerator = "'Option Explicit" & vbCrLf & "Public Function
- SortedArray(ItemsCounter As Long) As Variant()" & vbCrLf & vbCrLf _
- & "Dim TempSortedArray() As Variant" & vbCrLf _
- & "Sheet4.Cells(ItemsCounter, 2) = " & KWATT + 5 & vbCrLf _
- & "End Function" & vbCrLf
- End Function
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement