Adding VBA Code Programatically

Submitted by:Jhon Brain

Date added:22 April, 2013

Category:Visual Basic

Adding VBA Code Programmatically in Excel

Tags: code for excel

Code Snippet:

Public Sub Main()
' See if there's a module already there
For Each c In ActiveWorkbook.VBProject.VBComponents
If (c.Properties("Name") = ActiveSheet.Name) Then
Set WBCodeMod = c.CodeModule
End If
Next

' Count the lines in the module
LineCount = WBCodeMod.CountOfLines

' If so delete them
ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook._
ActiveSheet.CodeName).CodeModule.DeleteLines 1, LineCount

' Adding code programatically
ActiveWorkbook.VBProject._
VBComponent(ActiveWorkbook.ActiveSheet.CodeName).CodeModule.AddFromString _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbCr & _
"Dim iColor As Integer" & vbCr & _
"On Error Resume Next" & vbCr & _
"iColor = Target.Interior.ColorIndex" & vbCr & _
"If iColor < 0 Then" & vbCr & _
" iColor = 36" & vbCr & _
"Else" & vbCr & _
" iColor = iColor + 1" & vbCr & _
"End If" & vbCr & _
"If iColor = Target.Font.ColorIndex Then iColor = iColor + 1" & vbCr & _
"Cells.FormatConditions.Delete" & vbCr & _
"With Range(""A"" & Target.Row, Target.Address) " & vbCr & _
" .FormatConditions.Add Type:=2, Formula1:=""TRUE""" & vbCr & _
" .FormatConditions(1).Interior.ColorIndex = iColor" & vbCr & _
"End With" & vbCr & _
"With Range(Target.Offset(1 - Target.Row, 0).Address & _
"":"" & Target.Offset(-1, 0).Address)" & vbCr & _
" .FormatConditions.Add Type:=2, Formula1:=""TRUE""" & vbCr & _
" .FormatConditions(1).Interior.ColorIndex = iColor" & vbCr & _
"End With" & vbCr & _
"End Sub"
End Sub
 
 

Comments