Friday, November 30, 2012

What is the difference between Subroutine and Function in VBA

Excel VBA Sub Routine Vs Function
The Sub(routine) can NOT return a value. But a Function can return a value. If you want to call them then a Function can be called by it’s name with variables in parenthesis and  a Subroutine can be called only by writing it name with variables without parenthesis as explained below.
Subroutine (Sub) Syntax with Example:                                                              
Sub  Add(x as integer, y as integer, z as integer)
z= x+y
End Sub
'Calling Subroutine
Add  x, y, z
when it will return result will be saved in z variable, so it has not returned anything.

Function Syntax with Example:                                                                 
Function  Add(x as integer, y as integer) as integer
Add= x+y        
End Function
'Calling Function
T=Add( x, y)
when it will return result will be stored in ‘T’ variable. The function always returns a value so its value must be saved in some variable

Explanation:
Sub:

The easiest way to think of a Sub vs a Function is that a Function can return a value and a Sub cannot. A Sub can be thought of as a small program that performs some action that is contained within the program. 
Subs can be used to update a cell or perform an import and calculation, but the result can’t be returned to another sub or function. Another thing to note is that a sub (or macro) cannot be accessed directly by a cell reference. 
For example, when a cell is used to show today’s date, =Today(), that formula is also a built-in function, not a sub.

Function :

A Function is similar to a Sub, except that a Function can return a value. It may be easier to think of a function as similar to a formula in excel. You can provide the function the necessary inputs and the function returns the desired value. 
You can build a custom function for just about any action and then access that function from either a call from a macro, or a direct reference from a cell. 

Thanks, TAMATAM

Sunday, November 4, 2012

How To Create a Daily Task Workbooks For Current Month using Excel VBA Macro

Excel VBA Macro to Create a Daily Task Workbooks for Current Month
'This Macro Create Folder For the Curret Month(Eg: Noveber_2012)
'This Macro Creates Workbooks In the Current Month Folder(Eg: 01-Nov2012).
'This Macro Creates 30/31 Workbooks Based on No.of Days in a Month
'Each Workbook Name Begins From 1st Day of Month to Last Day(Eg:01-Nov-2012......... 30-Nov-2012)
'Note: 
We can use this Macro once in a Month to create the required Workbooks for that Month.

Sub DayTaskBooks4Month()

Dim D As String
Dim M As String
Dim MN As Strin
Dim Y As String
Dim P As String

Dim FSO As Object

Dim MyPath As String
Dim B As String
Dim A As Integer

Application.DisplayAlerts = False


D = Day(DateSerial(Year(Date), MONTH(Date) + 1, 1) - 1)

'Counts No.of Days In a Month
M = MONTH(Date)
MN = MonthName(M)
Y = Year(Date)
P = "C:\Documents and Settings\Administrator\My Documents\"
MyPath = P & MN & "_" & Y

Set FSO = CreateObject("Scripting.FileSystemObject")


If FSO.FolderExists(MyPath) = True Then

MsgBox MyPath & " Folder Already Exists In Given Path", vbOKCancel, "Folder Already Exists"
Exit Sub
End If

MkDir P & MN & "_" & Y


For A = 1 To D

B = A & " - " & Left(MN, 3) & " - " & Y
Workbooks.Add.SaveAs P & MN & "_" & Y & "\" & B & ".Xlsx"
Workbooks(B).Save
Workbooks(B).Close
Next A
End Sub

Thanks,
TAMATAM

How To Create a Monthly Task Workbook with All Day Sheets

Excel VBA Macro To Create a Monthly Task Workbook with All Day Sheets
'This Macro Create Folder For the Curret Month(Eg: November_2012)
'This Macro Creates a Workbook For the Current Month In MonthFolder(Eg: November_2012).
'The Work Book Contains 30/31 Sheets Based on No.of Days in a Month
'Each Sheet Name Begins From 1st Day of Month to Last Day(Eg:01-11-2012.....30-11-2012)
'Note: 
We can use this Macro once In a Month to create a Workbook for a Month

Sub MonthlyTaskBook()

Dim D As String
Dim M As String
Dim MN As String
Dim Y As String
Dim P As String

Dim FSO As Object

Dim MyPath As String
Dim A As Integer

Application.DisplayAlerts = False


D = Day(DateSerial(Year(Date), MONTH(Date) + 1, 1) - 1)

'Counts No.of Days In a Month
M = MONTH(Date)
MN = MonthName(M)
Y = Year(Date)
P = "C:\Documents and Settings\Administrator\My Documents\"

Set FSO = CreateObject("Scripting.FileSystemObject")


If FSO.FolderExists(MyPath) = True Then

MsgBox MyPath & " Folder Already Exist In Given Path", VbOkCancel, "Filder Already Exits"
Exit Sub
End If

MkDir P & MN & "_" & Y

Workbooks.Add.SaveAs P & MN & "_" & Y & "\" & MN & "_" & Y & ".xlsx"
Workbooks(MN & "_" & Y).Activate

For A = D To 1 Step -1

ActiveWorkbook.Sheets.Add.Name = A & "-" & Left(MN, 3) & "-" & Y
Next A
For A = 1 To 3
ActiveWorkbook.Sheets("Sheet" & A).Delete
Next A
Workbooks(MN & "_" & Y).Save
Workbooks(MN & "_" & Y).Close

End Sub


Thanks, Tamatam

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog