VBA Add date by clicking Month Combobox

‘to add month date wise in combobox

Private Sub Req_Month_Change()

a = COmbobox_Month.Value

b = 1 & "-" & a & "-" & 17

c = WorksheetFunction.EoMonth(b, 0)

d = Format(c, "dd")

Combobox_Date.Clear

For i = 1 To d

Combobox_Date.AddItem i

Next i

End Sub

********************************************************************************************************************

Create text file Sub CreateAfile Set fs = CreateObject("Scripting.FileSystemObject") Set a = fs.CreateTextFile("c:\testfile.txt", True) a.WriteLine("This is a test.") a.Close End Sub Sub OpenTextFileTest Const ForReading = 1, ForWriting = 2, ForAppending = 3 Dim fs, f Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.OpenTextFile("c:\testfile.txt", ForAppending,TristateFalse) f.Write "Hello world!" f.Close End Sub

LOOPING EXAMPLES

Looping in cell

Sub Cell_Loop()

Dim cell As Range

'Loop through each cell in a cell range

  For Each cell In ActiveSheet.Range("A1:Z100")

    Debug.Print cell.Value

  Next cell

'Loop through each cell in a Named Range

  For Each cell In ActiveSheet.Range("RawData")

    Debug.Print cell.Value

  Next cell

'Loop through each cell in a Table body range

  For Each cell In ActiveSheet.ListObjects("Table1").DataBodyRange

    Debug.Print cell.Value

  Next cell

End Sub

Looping in chart

Sub Chart_Loop()

Dim sht As Worksheet

Dim cht As ChartObject

'Loop through each chart in the active workbook

  For Each sht In ActiveWorkbook.Worksheets

    For Each cht In sht.ChartObjects

      Debug.Print cht.Name

    Next cht

  Next sht

End Sub

Looping Through shapes

Sub Shape_Loop()

Dim sht As Worksheet

Dim shp As Shape

'Loop through each shape in the active workbook

  For Each sht In ActiveWorkbook.Worksheets

    For Each shp In sht.Shapes

      If shp.Type = msoAutoShape Then

        Debug.Print shp.Name

      End If

    Next shp

  Next sht

End Sub

VBA -While Wend and Do Loop

while wend

While condition(s)

   [statements 1]

   [statements 2]

   ...

   [statements n]

Wend

example while wend

Private Sub Constant_demo_Click()

   Dim Counter :  Counter = 10  

  

   While Counter < 15     ' Test value of Counter.

      Counter = Counter + 1   ' Increment Counter.

      msgbox "The Current Value of the Counter is : " & Counter

   Wend   ' While loop exits if Counter Value becomes 15.

End Sub  

do while

Do While condition

   [statement 1]

   [statement 2]

   ...

   [statement n]

   [Exit Do]

   [statement 1]

   [statement 2]

   ...

   [statement n]

Loop          

examples

Private Sub Constant_demo_Click()

   Do While i < 5

      i = i + 1

      msgbox "The value of i is : " & i

   Loop

End Sub

Private Sub Constant_demo_Click()

   i = 10

   Do

      i = i + 1

      MsgBox "The value of i is : " & i

   Loop While i < 3 'Condition is false.Hence loop is executed once.

End Sub

FOR EACH LOOP

FOR EACH LOOP

for each loop

For Each element In Group

   [statement 1]

   [statement 2]

   ....

   [statement n]

   [Exit For]

   [statement 11]

   [statement 22]

Next

Example 1

Private Sub Constant_demo_Click() 

   'fruits is an array

   fruits = Array("apple", "orange", "cherries")

   Dim fruitnames As Variant

   'iterating using For each loop.

   For Each Item In fruits

      fruitnames = fruitnames & Item & Chr(10)

   Next

  

   MsgBox fruitnames

End Sub

Example 2

For Each cl In ActiveSheet.Range("A1:A10")

 'Statements to be executed inside the loop 

Msgbox cl.value

Next cl

Example 3

‘if cell is greater then 0 cell will update as blank

For Each cl In ActiveSheet.Range("A1:C11") 

 If InStr(cl.Value, "a") > 0 Then 

  cl.Value = "" 

 End If 

Next cl

VBA LOOPS

If you are writing a VBA program and you want to execute the same task multiple times then you can do this by using VBA for loop.

For Loops

For counter = start To end [Step stepcount]

   [statement 1]

   [statement 2]

   ....

   [statement n]

   [Exit For]

   [statement 11]

   [statement 22]

   ....

   [statement n]

Next

Example 1

‘Display message box  1 to 10 times

Private Sub example1()

   Dim a As Integer

   a = 10

  

   For i = 0 To a

      MsgBox "The value is i is : " & i

   Next

End Sub

Sub exaple2()

'Fills cells A1:A56 with values of X by looping' --- Comment

'Increase value of X by 1 in each loop' --- Comment

Dim X As Integer

    For X = 1 To 56

        Range("A" &amp; X).Value = X

    Next X

End Sub

Sub Example3()

' Fills cells B1:B56 with the 56 background colours' --- Comment

Dim X As Integer

    For X = 1 To 56

        Range("B" &amp; X).Select

        With Selection.Interior

            .ColorIndex = X

            .Pattern = xlSolid

        End With

    Next X

End Sub

CONTINUE

VBA DATA TYPES

There are many VBA data types, which can be divided into two main categories, namely numeric and non-numeric data types.

Type	Range of Values
Byte	0 to 255
Integer	-32,768 to 32,767
Long	-2,147,483,648 to 2,147,483,648
Single	-3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.
Double	-1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
Currency	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal	+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places).

Type	Range of Values
String (fixed length)	1 to 65,400 characters
String (variable length)	0 to 2 billion characters
Date	January 1, 100 to December 31, 9999
Boolean	True or False
Object	Any embedded object
Variant (numeric)	Any value as large as double
Variant (text)	Same as variable-length string

VBA Range Objects Examples

RANGE OBJECT SOME EXAMPLES

  Rem Just in case there is anything on the

    Rem worksheet, delete everything

    Range("A:K").Delete

    Range("1:20").Delete

   

    Rem Create the sections and headings of the worksheet

    Range("B2") = "Georgetown Dry Cleaning Services"

    Range("B2").Font.Name = "Rockwell Condensed"

    Range("B2").Font.Size = 24

    Range("B2").Font.Bold = True

    Range("B2").Font.Color = vbBlue

   

    Range("B5") = "Order Identification"

    Range("B5").Font.Name = "Cambria"

    Range("B5").Font.Size = 14

    Range("B5").Font.Bold = True

    Range("B5").Font.ThemeColor = 5

   

    Rem To draw a thick line, change the bottom

    Rem borders of the cells from B5 to J5

    Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous

    Range("B5:J5").Borders(xlEdgeBottom).Weight = xlMedium

   

    Range("B6") = "Receipt #:"

    Range("D6:F6").Borders(xlEdgeBottom).LineStyle = xlContinuous

    Range("D6:F6").Borders(xlEdgeBottom).Weight = xlHairline

   

    Range("G6") = "Order Status:"

    Range("I6:J6").Borders(xlEdgeBottom).LineStyle = xlContinuous

    Range("I6:J6").Borders(xlEdgeBottom).Weight = xlHairline

   

    Range("B7") = "Customer Name:"

    Range("D7:F7").Borders(xlEdgeBottom).LineStyle = xlContinuous

    Range("D7:F7").Borders(xlEdgeBottom).Weight = xlHairline

   

    Range("G7") = "Customer Phone:"

    Range("I7:J7").Borders(xlEdgeBottom).LineStyle = xlContinuous

    Range("I7:J7").Borders(xlEdgeBottom).Weight = xlHairline

   

    Rem To draw a thick line, change the bottom

    Rem borders of the cells from B5 to J5

    Range("B8:J8").Borders(xlEdgeBottom).LineStyle = xlContinuous

    Range("B8:J8").Borders(xlEdgeBottom).Weight = xlThin

   

    Range("B9") = "Date Left:"

    Range("D9:F9").Borders(xlEdgeBottom).LineStyle = xlContinuous