Hi,

   I have been writing in VBA for over 5 years, 
   I am going through my archives starting with short videos
   Uploading demos to YouTube and providing source code
   Keep popping back through 2015..
        VBA YouTube Playlist here
   

Setting up Excel for VBA

How to Comment Code in VBA

use a simple '(apostrophe) before the code you would like to comment
This part of the code will turn green.
I came up with a coding key to find out what year i made the code
' = 2010
'/ = 2011
'// = 2012
'/// = 2013 and so on
I found this very useful when first learning, these days i use
'// basically because i write in C++ and C# more now.




Hello World

  • VBA Excel Hello World (Watch below or click here to watch on YouTube)
  • !! Make sure to save as .xlsm !! Sub messageBox() MsgBox ("Hello World") End Sub
    VBA HelloWorld
  • VBA Excel Random Number Generator (Watch below or click here to watch on YouTube)
  • Sub randomNumberGenerator() '//Variables required for the generator '//you can press F5 in the sub to test Dim Low As Double Dim High As Double Dim Random As Long Low = 1 '//change to lowest required High = 200 '//change to highest required Random = Int((High - Low + 1) * Rnd() + Low) '//Shows the number on screen MsgBox Random End Sub
  • VBA Excel Random Number (Watch below or click here to watch on YouTube)
  • '/////Add to ThisWorkbook ///// Private Sub Workbook_Open() '//Calls Closeandsave at the time required Application.OnTime TimeValue("17:33:00"), "Closeandsave" End Sub '///// Add to Module1 ///// Sub Closeandsave() '//This Sub will close the program at the recommended time '//Will save without asking Windows("AutoCloseAtACertainTime.xlsm").Activate ActiveWorkbook.Close SaveChanges:=True End Sub Add Username Date and Time to Spreadsheet
  • Add Username Date and Time to Spreadsheet (Watch below or click here to watch on YouTube)
  • '///// Add to Module1 ///// Sub UsernameDateandTime() '//Press F5 to launch Sub '//From inside the Code Dim n As Integer '//change sheet if required Sheets("Sheet1").Activate '//A1 Cell first cell selected '//Starts Loop Process If Range("A1").Value = "" Then n = 1 Else n = Cells(Rows.Count, "A").End(xlUp).Row + 1 End If '//Ends Loop '//Adds info to Cells Cells(n, "A").Value = Environ("username") Cells(n, "b").Value = Date Cells(n, "c").Value = Time End Sub Sub Workbook_Open() If Weekday(Now()) = vbTuesday Then Call weekDaySub End If End Sub '// Change the day if required '// Press F5 to test after adding sub to module Sub weekDaySub() MsgBox ("It`s Tuesday, wish it was Friday") End Sub '//Save and Close sheet to check Sub works Sub Auto_Open() If Weekday(Now()) = vbTuesday Then Call weekDaySub End If End Sub '// Change the day if required '// Press F5 to test after adding sub to module Sub weekDaySub() MsgBox ("It`s Tuesday, wish it was Friday") End Sub '//Save and Close sheet to check Sub works