Friday, 30 November 2012

Excel VBA -ThisWorkBook Vs. ActiveWorkBook

What is the difference between ActiveWorkBook and ThisWorkbook
ThisWorkBook object refers to the workbook in which the macro code is contained. 

ActiveWorkBook object refers to the workbook that is currently active.

Most times, they will refer to the same workbook. But if the workbook on which running the macro code is not the active workbook then they will point to some different objects.


Thanks.,
Excel Reddy
            MBA(Finance)

Excel VBA Sub Routine Vs Function

What is the difference between Subroutine and 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

More 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 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. 

Sunday, 18 November 2012

Microsoft Excel MOD Function

MS Excel MOD Function:
MOD Function Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
Syntax:
MOD(Number, Divisor)

Example:

NUMBER
Divisor
MOD Function
Result
100
30
 =MOD(A2,B2)
10
100
11
 =MOD(A3,B3)
1
272
30
 =MOD(A4,B4)
2

Sunday, 4 November 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: This Macro Should use only once In a 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: Noveber_2012)
'This Macro Creates a Workbook For the Current Month In MonthFolder(Eg: Noveber_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: This Macro Should use only once In 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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts