Tuesday, April 11, 2017

*INTERVIEW QUESTIONS

*Ques 01. What is the difference between ByVal and ByRef and which is default ?

Ans- 
ByRef: If you pass an argument by reference when calling a procedure the procedure access to the actual variable in memory. As a result the variable's value can be changed by the procedure.

ByVal: If you pass an argument by value when calling a procedure the variable's value can be changed with in the procedure only outside the actual value of the variable is retained.

ByRef is default: Passing by reference is the default in VBA. If you do not explicitly specify to pass an argument by value VBA will pass it by reference.

*Ques 02. What is the meaning of Option Explicit and Option Base?

Ans-
Explicit makes the declaration of Variables Mandatory while Option Base used at module level to declare the default lower bound for array subscripts. For eg. Option Base 1 will make the array lower bound as 1 instead of 0.

Ques 03. What are various data type and their size?

Ans-
i) The Boolean data type has only two states, True and False.
ii) The Byte data type is an 8-bit variable which can store value from 0 to 255.
iii) The Double data type is a 64-bit floating point number used when high accuracy is needed.
iv) The Integer data type is a 16-bit number which can range from -32768 to 32767.
v) The Long data type is a 32-bit number which can range from -2,147,483,648 to 2,147,483,647.
vi) The Single data type is a 32-bit number ranging from -3.402823e38 to -1.401298e-45
vii) The String data type is usually used as a variable-length type of variable.


*Ques 04. Difference between ActiveWorkbook and ThisWorkbook.

Ans-
 ThisWorkbook refers to the workbook where code is being written while ActiveWorkbook refers to the workbook which is in active state with active window. In case of only one workbook open, ActiveWorkbook is same as ThisWorkbook.


Ques. Show me the variable diagram

Ans-


















Ques 05. Code to find a Last used Row in a column or Last used column of a Row.

Ans:
Last Row in a column can be find using End(xlUp) and Last Column in a row can be find using End(xlToLeft). For e.g. Range("A1048576").End(xlUp).Row gives last used row of Column A.

Ques 06. Difference between ActiveX and Form Controls.

Ans:
i) Forms controls can be used on worksheets and chart sheets. Forms controls can also be placed within embedded charts in Classic Excel (though not in Excel 2007). ActiveX controls can only be used on worksheets. ActiveX controls do not work in MacExcel.

ii) The Forms controls aren’t very complicated, and they have been part of Excel for longer (they were used in Excel 5/95’s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet.

*Ques 07. What is the difference b/w Functions and Subroutines?
Ans:
i) Subroutines never return a value but functions does return values.

ii) A function could not change the values of actual arguments whereas a subroutine could change them.

*Ques 08. How to debug a VBA code?

Ans:
Using Breakpoints(F9), Step-by-step execution (F8), Debug.Print & Immediate Window and Watch window.

*Ques 09. Draw basic Excel Object Model.

Ans:
Application --> Workbooks --> Worksheets --> Range / Chart

Ques 10. What are properties, methods, events and objects?

Ans:
All the controls in the ToolBox except the Pointer are objects in Visual Basic. These objects have associated properties, methods and events.
A property is a named attribute of a programming object. Properties define the characteristics of an object such as Size, Color etc. or sometimes the way in which it behaves.

A method is an action that can be performed on objects. For example, a cat is an object. Its properties might include long white hair, blue eyes, 3 pounds weight etc. A complete definition of cat must only encompass on its looks, but should also include a complete itemization of its activities. Therefore, a cat's methods might be move, jump, play, breath etc.
Visual Basic programs are built around events. Events are various things that can happen in a program. Let us consider a TextBox control and a few of its associated events to understand the concept of event driven programming. The TextBox control supports various events such as Change, Click, MouseMove and many more that will be listed in the Properties dropdown list in the code window for the TextBox control. We will look into a few of them as given below.
* The code entered in the Change event fires when there is a change in the contents of the TextBox
* The Click event fires when the TextBox control is clicked.
* The MouseMove event fires when the mouse is moved over the TextBox

Ques 11. How to hide a worksheet so that a user cannot unhide it?

Ans:
Use Sheet's visible property and set it to xlSheetVeryHidden . For eg. Sheets(1).Visible = xlSheetVeryHidden will super hide the first worksheet of the workbook.

Ques 12. Union is used for _____________ ?

Ans:
To unite the different ranges depending on the logic. It is similar to set union, here range works as set. For eg. Set nrange = Union(rng1,rng2)

Ques 13. Which are the 2 macro languages and which do you use ?

Asn:
XLM (used in Excel 97 or before) and VBA(used for 2000 and after). Obviously, VBA is in use these days.

Ques 14. How can you increase the number of rows in a worksheet?
Ans:
No one can't. They're fixed as 65536(2^16) in Excel 2003 or before and 1048576(2^20) in Excel 2007 & Excel 2010.

Ques 15. How can you increase the number of columns in a worksheet?

Ans
No one can't. They're fixed as 256(2^8) in Excel 2003 or before and 16384(2^14) in Excel 2007 & Excel 2010.

Ques. 16. How will you distribute a workbook such that it can't be copied using macro or anything?

Ans
We can create a workbook which cannot be modified but can not create a workbook which can't be copied.(It depends on system security, it has nothing to do with Excel or VBA)

Ques 17. How can you prevent a user for adding or deleting sheets?

Ans
You need to protect the workbook's structure.
Excel 2003 -->
Select Tools - Protection - Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked.
Excel 2007/2010 -->
Go to Review --> Click 'Protect Workbook' --> Click 'Protect Structure and Windows'

If you specify a password, that password will be required to unprotect the workbook. When a workbook's structure is protected, the user may not:
         * Add/Delete a sheet
         * Hide/Unhide a sheet
         * Rename a sheet
         * Move a sheet

*Ques 18. What types of workbook protection are available?

Ans
Excel provides three ways to protect a workbook:
         * Require a password to open the workbook
         * Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets
         * Prevent users from changing the size or position of windows

*Ques 19. The Font dialog box allows you to select different Fonts, their style, their size, and some other special effects. How do you bring up this Font dialog box?

Ans
Use Application.Dialogs(xlDialogFont).Show or Application.Dialogs(xlDialogFormatFont).Show to load font dialog box from Excel VBA.

*Ques 20. What is ADO, OLEDB & ODBC?

Ans:
ADO : ActiveX Data Objects is universal data access framework that encompasses the functionality of DAO.
ODBC : Open Database Connectivity(ODBC) is a windows technology that lets a database client application connect to a external database.
OLEDB : Low level programming interface designed to access a wide variety of data access Object Linking and Embedding (OLE).

*Ques 21. What is the method for returning more than one values from a function in VB?

Ans
Any of the three methods can be used:
i) Create a class with the properties you require to return and then return the object of the class from the function.
ii) Using ByRef for the values.
iii) Return an array of the values.

Ques 22. Does VBA supports OOP principles?

Ans
Yes because VBA is VB6.0 based which is an Object Based Programming Language and is also known as 'Event Driven Programming' and it supports Polymorphism, Encapsulation and partially Inheritance.

Ques 23. To set the command button for ESC, Which property needs to be changed?

Ans:
Set Cancel property of Button to True on the Form.

*Ques 24. What is a Variant, what the pros and cons of its use?

Ans
Variant data type is able to hold any other data type, including numbers, strings, dates, and object references. A Variant's descriptor is only 16 bytes long (4 short words for the type, and 2 long words for the data, or data pointer).
Pros: You cannot use Null with any variable type other than Variant.
You don't need to worry about what you have declared a variable as.
When a Variant has been declared but not assigned a value, it contains the special value Empty.
Cons: A developer may not remember and misuse a variable assigning any value to it which will be type-casted without errors.

Ques 25. What is a volatile function?

Ans:
Volatile functions are a type of function that will always recalculate. That means whenever Excel needs to calculate any part of the worksheet, those cells containing volatile functions will also calculate.

Ques 26. Give some examples of Volatile function.

Ans
Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()
Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()
Some are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97.
A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:
INDEX(), ROWS(), COLUMNS(), AREAS()
and CELL("Filename") IS volatile although a MSKBN article says its not.
One particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions:
=SUMIF(A1:A4,">0",B1) is volatile whereas =SUMIF(A1:A4,">0",B1:B4) is not volatile.

Ques 27. How do you make a UDF volatile?

Ans
By adding Application.Volatile statement to it. It must be the first line of your User Defined Function.

Ques 28. Is it possible to apply 'Application.Volatile(False)' to a volatile public function like INDEX and make it not volatile?

Ans
Actually INDEX is not a volatile function, even though some MicroSoft documentation says it is. Anyway no its not possible to apply Application.Volatile(False) to a built-in Excel function except by duplicating what the built-in function does inside a UDF.

Ques 29. What is Excel dependency tree?

Ans
Dependency trees are excel way of minimizing the calculation by tracking what has changed since last calculation. It allows Excel to recalculate only:
        * Formulae/Names that have changed.
        * Formulae containing Volatile Functions
        * Formulae dependent on changed or volatile formulae or cells or names.

Excel determines dependencies by looking at the cells referred to by each formula and by the argument list of each function.
Dependency trees are immediately updated whenever a formula is entered or changed.
To force the dependency trees to be rebuilt and all formulae calculation use CTRL+ALT+SHIFT+F9.

Ques 30. What are keyboard shortcuts and their equivalent VBA methods for Formulae calculation and building Excel dependency trees?

Ans
Shortcut Combination VBA Equivalent Meaning
F9 Application.Calculate Recalculate
Ctrl+Alt+F9 Application.CalculateFull Full Calculation
Ctrl+Alt+Shift+F9 Application.CalculateFullRebuild Rebuild Excel Dependency Tree
and Full Calculation
Shift+F9 Sheets(1).Calculate Calculate Worksheet

Ques 31. What does Range.Dirty used for ?

Ans
To add the specified cells to the list of cells requiring calculation at the next recalculation.

Ques 32. How do you check the Calculation state ?

Ans: 
Pretty simply, using the Application.CalculationState property which tells if calculation has completed ( xlDone ), is pending ( xlPending) , or is in process ( xlCalculating ).

Ques 33. How do you define Excel Calculation Interruption Key to stop the calculation?

Ans:
Using Application.CalculationInterruptKey= XlAnyKey | XLEscKey | XlNokey.
Remember using XlNokey, calculation cannot be interrupted.



Ques 34. What do you know about the interpretation features of VBA?

Ans
VBA is licensed to Microsoft and this compatible with and only Microsoft products. Code written is compiled by an intermediate language called P-code and this is stored in hosting applications such as Excel, Word and Access. The intermediate code is interpreted by a virtual machine. This code and intermediate language is the exclusive right of Microsoft.

Ques 35. Explain about insert module and Goal Seek functions present in VBA?

Ans:
The chief use of VBA is to make use of its special function which helps in repeated actions. Goal seek function helps to reduce manual entry of the code each and every time. This solves the problem of repeated function entry by automating functions and actions. Sub routines are inserted into the using the VBA editor and command insert module.

*Ques 36. State the difference between Visual Basic, VB Script and Visual Basic for Applications?

Ans
Visual basic is useful if you are planning to develop your programs from scratch.This language helps you in developing Active x controls, exe files, etc.
VB script is a powerful tool, through which you can create small scale applications on web pages, automation applications, etc. Integrated development environment is not present for VB script.
Visual Basic for Applications are very useful in automating your existing application. VB application is useful for developing already existing applications.

*Ques 37. What is the difference between UsedRange and CurrentRegion properties ?

Ans:
i) The current region is a range bounded by any combination of blank rows and blank columns.
This property is useful for many operations that automatically expand the selection to include the entire current region, such as the AutoFormat method. This property cannot be used on a protected worksheet.
The UsedRange property is used to select the range of used cells on a worksheet. It returns a Range object that represents the used range on the specified worksheet.
ii) Every non-blank cell got its CurrentRegion and its keyboard shortcut is Ctrl+Shift+Spacebar.
iii) There can be many current regions but there is only one used range in a worksheet.

*Ques 38. Tips and suggestion

First interviewer sees your general attitude towards programming and will try to calm down your blood pressure. While it seems very easy this is the biggest hurdle. He may give you a very easy program to write like sorting an array, coloring the sheet tabs, etc. The objective is not to see your technical knowledge but your programming behavior. Such programs are so obvious and easy that expectations are not about writing the program but to see comments, indentation, using meaningful variable names, declaring variables(which one don't needs to but considered a great practice), using Option Explicit, Debugging style etc. To know that good you're able to communicate your logic with other developers. This is very helpful for the team and is considered a good practice with fruits returned in long term.

Secondly, he will try to test your knowledge of Excel Object Model. They may ask you to sort and filter a range, or clean out duplicate data from a list, consolidate data from multiple files, import data from MS access databases etc . He will look for knowledge of and ability to work with fundamental Excel objects such as Workbook, Worksheet, Range etc; also he will definitely want to see that you neither use "Macro Recorder" nor macro recorder style code using Select and Activate everywhere.




Thirdly he would be interested in your excel knowledge as well as how VBA works. Like pivots, charts, UDF, volatile functions(Can you write code that runs every time the worksheet changes?”) etc.



Sunday, April 2, 2017

Charts

Option Explicit
'Basic Excel Pie Chart
Sub Chart_Exercise()

Dim rng As Range
Dim chrt As Chart

    'Intialization
    Set rng = Range("A1").CurrentRegion
    Set chrt = Sheet1.Shapes.AddChart.Chart

        'Chart Properties
        chrt.SetSourceData rng
        chrt.ChartType = xl3DPie
        chrt.ChartTitle.Text = "Quarterly Sales Figure"
End Sub
--------------------------------------------------------------------------------------------------------------------------
'Macro to user selection through radio button and change in Chart




Private Sub ComboBox1_Change()


'Variable Declaration
Dim rng As Range
Dim chrt As ChartObject

'----- Range Initialization

Set rng = Range("D3").CurrentRegion

'----- Chart Intialization

Set chrt = Sheets("Chart").ChartObjects(1)

'----- Chart Properties

chrt.Chart.SetSourceData rng
chrt.Chart.ChartTitle.Text = "Monthly Sales Figure"

'----- Conditions against dropdown

If ComboBox1.Value = "Bar" Then
    chrt.Chart.ChartType = xl3DBarStacked
   
ElseIf ComboBox1.Value = "Pie" Then
    chrt.Chart.ChartType = xlPie

ElseIf ComboBox1.Value = "Line" Then
    chrt.Chart.ChartType = xlLine

ElseIf ComboBox1.Value = "Area" Then
    chrt.Chart.ChartType = xlArea

End If

End Sub

Private Sub CommandButton1_Click()
'----- Variable declartion
Dim tme As Date

'----- Time initialization
tme = Now

'-----Loop to refresh sheet

Do Until Now > tme + (1 / 24 / 60 / 60) 'Adding 10 sec to time
   
    Sheets("Chart").Calculate

Loop

End Sub



Private Sub OptionButton1_Click()

'Variable Declaration
Dim rng As Range
Dim chrt As ChartObject

'----- Range Initialization

Set rng = Range("D3").CurrentRegion

'----- Chart Intialization

Set chrt = Sheets("Chart").ChartObjects(1)

'----- Chart Properties

chrt.Chart.SetSourceData rng
chrt.Chart.ChartTitle.Text = "Monthly Sales Figure"
chrt.Chart.ChartType = xl3DLine

End Sub

Private Sub OptionButton2_Click()
'Variable Declaration
Dim rng As Range
Dim chrt As ChartObject

'----- Range Initialization

Set rng = Range("D3").CurrentRegion

'----- Chart Intialization

Set chrt = Sheets("Chart").ChartObjects(1)

'----- Chart Properties

chrt.Chart.SetSourceData rng
chrt.Chart.ChartType = xlBarClustered
End Sub

Private Sub OptionButton3_Click()
'Variable Declaration
Dim rng As Range
Dim chrt As ChartObject

'----- Range Initialization

Set rng = Range("D3").CurrentRegion

'----- Chart Intialization

Set chrt = Sheets("Chart").ChartObjects(1)

'----- Chart Properties

chrt.Chart.SetSourceData rng
chrt.Chart.ChartType = xlPie
End Sub

Private Sub OptionButton4_Click()
'Variable Declaration
Dim rng As Range
Dim chrt As ChartObject

'----- Range Initialization

Set rng = Range("D3").CurrentRegion

'----- Chart Intialization

Set chrt = Sheets("Chart").ChartObjects(1)

'----- Chart Properties

chrt.Chart.SetSourceData rng
chrt.Chart.ChartType = xlArea
End Sub

Read from Excel to Text File

Option Explicit

'Macro to convert excel file into text file

Sub ExcelToTextTransfer()

Dim fso As New FileSystemObject
Dim TextFile As TextStream
Dim i, j, mnth As Long
Dim rng, c As Range

'------------Phase I

For mnth = 1 To 12              'Loop for month

    Sheet1.AutoFilterMode = False           'Remove existing filter
    Sheet1.Range("A1").AutoFilter field:=4, Criteria1:=Left(MonthName(mnth), 3)    'Autofilter on month on month

'------------Phase II

    Set TextFile = fso.CreateTextFile("C:\Users\Vinay Kumar\Desktop\VBA Classs\01 Apr\" _
                        & Left(MonthName(mnth), 3) & ".txt") 'Creation of text file
   
'------------Phase III
    Set rng = Range(Range("A1"), Range("A1").End(xlDown))       'Range selection of filtered data
   
    For Each c In rng
        If c.EntireRow.Hidden = False Then                      'If cell is visible perform the activity
            For j = 1 To Range("A1").End(xlToRight).Column
                TextFile.Write Cells(c.Row, j).Value & ","      'Writing on text file
            Next j
        End If
        If c.EntireRow.Hidden = False Then TextFile.Write vbNewLine     'Line Change
    Next c
   
    TextFile.Close          'Text file closure
   
   
Next mnth
   
MsgBox "done!!"
End Sub
------------------------------------------------------------------------------------------------------------------------
Option Explicit

Sub ExcelToTextFile()

Dim fso As New FileSystemObject             'Intialisation of file system object
Dim TextFile As TextStream
Dim wb As Workbook
Dim rng, c As Range
Dim i, j, k, mnth As Long

Set wb = ThisWorkbook
ActiveSheet.AutoFilterMode = False

mnth = 1
For k = 1 To 12

Range("A1").AutoFilter field:=4, Criteria1:=Left(MonthName(mnth), 3)
Set rng = ThisWorkbook.Sheets("Sheet1").Range(Range("A1"), Range("A1").End(xlDown)).SpecialCells(xlCellTypeVisible)
    
    Set TextFile = fso.CreateTextFile("C:\Users\Vinay Kumar\Desktop\VBA Classs\01 Apr\" & Left(MonthName(mnth), 3) & ".txt")
    For Each c In rng
        For j = 1 To 10
        
            
            TextFile.Write Cells(c.Row, j).Value & ","
            
    Next j
    
    TextFile.Write vbNewLine
    
    Next
mnth = mnth + 1
TextFile.Close
Next k

End Sub

Recursive File System Object

''Creating Recursive Procedures
'
'Procedures have a limited amount of space for variables. Each time a procedure calls itself, more of that space is used. A procedure that calls itself is a recursive procedure. A recursive procedure that continuously calls itself eventually causes an error. For

Option Explicit
'Recursive process to copy all vlc files in a given folder
Sub CopyVLCFiles()

RecursiveVLCFiles "C:\Users\Vinay Kumar\Pictures"
RecursiveVLCFiles "C:\Users\Vinay Kumar\Videos"
MsgBox "done!"

End Sub

Sub RecursiveVLCFiles(startfldr As String)

Dim fso As New Scripting.FileSystemObject
Dim fil As Scripting.File
Dim tempfolder As Scripting.Folder
Dim subfldr As Scripting.Folder


Set tempfolder = fso.GetFolder(startfldr)

For Each fil In tempfolder.Files

    If Left(fso.GetExtensionName(fil.Path), 3) = "MP4" Then
        fil.Copy Environ("userprofile") & "\desktop\finalvidopico\MP4\" & fil.Name
    End If
   
Next fil

For Each subfldr In tempfolder.SubFolders

    Call RecursiveVLCFiles(subfldr.Path)

Next subfldr
End Sub


'Recursive process to list all file type/extension
Sub GetExtensionName()

Recursiveextname "C:\Users\Vinay Kumar\Pictures"
Recursiveextname "C:\Users\Vinay Kumar\Videos"
MsgBox "done!"


End Sub

Sub Recursiveextname(startfldr As String)

Dim fso As New Scripting.FileSystemObject
Dim fil As Scripting.File
Dim tempfolder As Scripting.Folder
Dim subfldr As Scripting.Folder
Dim extname As String

Sheet12.Activate

Set tempfolder = fso.GetFolder(startfldr)

    For Each fil In tempfolder.Files
        extname = fso.GetExtensionName(fil.Path)
        ActiveCell.value = extname
        ActiveCell.Offset(0, 1).value = fil.Path
        ActiveCell.Offset(1, 0).Select
    Next fil

    For Each subfldr In tempfolder.SubFolders
        Call Recursiveextname(subfldr.Path)
    Next subfldr


End Sub

File System Object

Sub GetFileStructure()
    Dim fileNames1() As String
    Dim path1 As String
   
    If Trim(Range("G3").value = "") Or Trim(Range("G4").value = "") Then
        MsgBox "Please enter correct path and type to proceed!"
    Else
        path1 = Range("G3").value
 
        Select Case Range("G4").value
            Case "File"
                fileNames1() = GetAllFiles(path1)
            Case "Folder"
                fileNames1() = GetAllFolders(path1)
        End Select
   
        Call ClearRows  'delete last search
        For i = 0 To UBound(fileNames1()) Step 1
            'MsgBox fileNames1(i)
            Cells(10 + i, 1).value = fileNames1(i)
        Next
    End If
End Sub

Function GetAllFiles(path1 As String)
    Dim fso As New FileSystemObject
    Dim folder1 As Folder
    Dim file1 As File
    Dim fileNames1() As String
    Dim i As Integer

    ReDim fileNames1(0) As String
    If fso.FolderExists(path1) Then
        Set folder1 = fso.GetFolder(path1)
   
        For Each file1 In folder1.Files
            i = IIf(fileNames1(0) = "", 0, i + 1)
            ReDim Preserve fileNames1(i) As String
            fileNames1(i) = file1.Name
            'Debug.Print file1.Name
        Next
    End If
   
    GetAllFiles = fileNames1
End Function

Function GetAllFolders(path1 As String)
    Dim fso As New FileSystemObject
    Dim folder1 As Folder
    Dim subFolder1 As Folder
    'Dim file1 As File
    Dim folderNames1() As String
    Dim i As Integer

    ReDim folderNames1(0) As String
    If fso.FolderExists(path1) Then
        Set folder1 = fso.GetFolder(path1)

        For Each subFolder1 In folder1.SubFolders
            i = IIf(folderNames1(0) = "", 0, i + 1)
            ReDim Preserve folderNames1(i) As String
            folderNames1(i) = subFolder1.Name
            'Debug.Print subFolder1.Name
        Next
    End If

    'MsgBox folder1.Path
    GetAllFolders = folderNames1
End Function

Sub ClearRows()
    Dim rowCount As Integer
   
    rowCount = ActiveSheet.UsedRange.Rows.Count + 1
    'MsgBox rowCount
    Range("A10", Cells(rowCount, 1)).value = ""
End Sub

Miscellaneous Exercise 2

'Check filter is applied on the sheet or not if not apply filter on it
Sub autofilteron()

Sheet3.Activate
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("1:1").AutoFilter
End If

End Sub
'put auto filter off
Sub autofilteroff()

Sheet3.Activate
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If

End Sub
'hides all Filters except column 2
Sub HideArrows()

Dim C As Range
Dim i As Integer

Sheet3.Activate
i = Cells(1, 1).End(xlToRight).Column

For Each C In Range(Cells(1, 1), Cells(1, i))
    If C.Column <> 2 Then
        C.AutoFilter field:=C.Column, Visibledropdown:=False
    End If
Next

End Sub

'Count Visible Rows
Sub CountVisRows()

Sheet3.Activate
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
MsgBox rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 _
    & " of " & rng.Rows.Count - 1 & " Records"

End Sub


'Create a filter from B to G column and put filter on Course
'Name'"Central" and Permanent Address="Pen Set"
Sub autofiltertest1()

Sheet3.Activate
Range("B:G").AutoFilter field:=4, Criteria1:="Central"
Range("b:g").AutoFilter field:=6, Criteria1:="Pen Set"

End Sub


'If you set an object variable to a Range object, you can easily manipulate the range by using the variable name.

'The following procedure creates the object variable myRange and then assigns the variable to range A1:D5 on Sheet1 in the active workbook. Subsequent statements modify properties of the range by substituting the variable name for the Range object.

Sub Random()

    Sheet3.Activate
    Dim myRange As Range
    Set myRange = Worksheets("Sheet3").Range("A1:D5")
    myRange.Formula = "=RAND()"
    myRange.Font.Bold = True
   
End Sub

Miscellaneous Exercise 1

'Below code will create a NEW WORKBOOK and It will Save the new Workbook to location desktop
Sub Addworkbook()

Set wb = Workbooks.Add
wb.SaveAs filename:="D:\Users\vinay1ku\Desktop\New WB4.xls"

End Sub

'Below code will create a NEW WORKBOOK and It will Save the new Workbook to location desktop; Option2
Sub Addworkbook2()

Workbooks.Add
ActiveWorkbook.SaveAs filename:="D:\Users\vinay1ku\Desktop\New WB5.xls"

End Sub

'How to RENAME the Excel Worksheet Using VBA Macro
Sub RenameFile()

Set ws = ActiveWorkbook.Worksheets("test1")
ws.Name = "test2"

End Sub

'How to RENAME the Excel Worksheet Using VBA Macro; Option2
Sub RenameFile2()

ActiveWorkbook.Worksheets("test1").Name = "test33"

End Sub

'How to count BLANK CELLS/FORMULA and NON_FORMULA cells  in given range in Excel using VBA Macro
Sub BlankCellCount()

Sheet2.Activate
l = Range("b1:b20").Cells.SpecialCells(xlCellTypeFormulas).Count
n = Range("b1:b20").Cells.SpecialCells(xlCellTypeBlanks).Count
m = Range("b1:b20").Cells.SpecialCells(xlCellTypeConstants).Count

MsgBox ("Blank cells: " & n & "; Non-formula cells: " & m & " ; Formula cells: " & l)

End Sub

'How to set the BACKGROUND COLOR OF CELLS using Excel VBA Macro
Sub ColorChange()

Sheet2.Activate
ActiveWorkbook.Worksheets(1).Range("B2:D10").Interior.ColorIndex = 32
Rows("3:3").Select

End Sub

'SEND MAIL through macro with pr without attachement
Sub SendMail()

ActiveWorkbook.SendMail ("kvinay.g@gmail.com")

End Sub

'SEND MAIL through macro with pr without attachement; Option2
Sub sendmail2()

Set outlook = CreateObject("outlook.application")

Set Message = outlook.createitem(0)
    With Message
    .Subject = "test mail"
    .htmlbody = "any textbody"
    .Recipients.Add ("kvinay.g@gmail.com")
    End With

Set myattachments = Message.Attachments
    myattachments.Add "D:\Users\vinay1ku\Desktop\voter id app card.pdf"
    Message.display
    Message.send
   
End Sub

'ARRAY to feed data from worksheet(Single Dimention)
Sub MyArrayTest1()

Dim myarray(1 To 20)                'array always start with 0, if you wish u can mention like shown in example.
    For i = 1 To 20
        myarray(i) = Sheet2.Range("b" & i).value
    Next
    For j = 1 To 20
        Sheet2.Range("m" & j).value = myarray(j)
    Next
   
End Sub

'Array to feed data from worksheet
Sub MyArrayTest2()

    Dim myarray2() As Variant
    Sheet2.Activate
   
    myarray2 = Range("b1:c20")
    MsgBox "Max: " & WorksheetFunction.Max(myarray2) & "," & "Sum: " _
        & WorksheetFunction.Sum(myarray2)

End Sub

'Macro to set range object
Sub RangeTest()

    Dim testrange As Range
    Set testrange = Range("a1:g5")
   
    Sheet2.Activate
    testrange.Select
   
    testrange.Cells.SpecialCells(xlCellTypeBlanks).Select 'Select Blank cells from the range

End Sub

'Find the MAXIMUM from the range
Sub MaxOption()

    Sheet2.Activate
    MsgBox "Max'm in range(C1:C10): " & WorksheetFunction.Max(Range("C1:C10"))
   
End Sub

WorkSheet

'Worksheet Object
'Represents a worksheet.
'Remarks
'
'
'The Worksheet object is a member of the Worksheets collection. The Worksheets collection contains all the Worksheet objects in a workbook.
'
'The Worksheet object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets).
'
'
'Example
'
'
'Use Worksheets(index), where index is the worksheet index number or name, to return a single Worksheet object. The following example hides worksheet one in the active workbook.
'
'Visual Basic for Applications
'Worksheets(1).Visible = False
'
'The worksheet index number denotes the position of the worksheet on the workbook’s tab bar. Worksheets(1) is the first (leftmost) worksheet in the workbook, and Worksheets(Worksheets.Count) is the last one. All worksheets are included in the index count, even if they’re hidden.
'
'The worksheet name is shown on the tab for the worksheet. Use the Name property to set or return the worksheet name. The following example protects the scenarios on Sheet1.
'

'Create a macro to find out if the workbook belongs to him.
Sub Wokbookname()

Name = Application.UserName
ans = MsgBox("Is your name: " & Name, vbYesNo)
If ans = vbYes Then MsgBox "Great"
If ans = vbNo Then MsgBox "Ohh I am Sorry"

End Sub

Sub testWrksheets()
   
    Worksheets("sheet14").Select
   
    Worksheets("sheet12").Select False                  'Selecting multiple sheet
   
    Worksheets(5).Select        'Indexing of worksheets
   
    Sheet16Code.Select      'anotherway to selectsheet(by 2nd name of the sheet)
   
    Worksheets.Add          'adding a worksheet
   
    Worksheets.Add Worksheets("Sheet14")        'adding a worksheet before sheet14
   
    Worksheets.Add , Worksheets("Sheet12")      'adding a worksheet after sheet12 or you can _
                                                    write before:=/after:=worksheets("Sheet14") for clear refernce
   
    Worksheets.Add Before:=Worksheets(1)        'adding sheet to the start
   
    Worksheets.Add after:=Worksheets(Sheets.Count)      'adding sheet to the end
   
    Worksheets.Add after:=Worksheets(Sheets.Count), Count:=3    'adding 3 new sheets to the end
   
    Sheets.Add Before:=Worksheets(1), Type:=XlSheetType.xlChart     'adding chart type worksheet
   
    Application.DisplayAlerts = False
   
    Sheets(Sheets.Count).Delete       'deleting last sheet of the workbook
   
    Application.DisplayAlerts = True    'please note sheets include charts and other type but worksheets only refer to spreadsheet
   
    Charts.Delete   'delete all object type in one go
   
    Worksheets("sheet1").Copy , Worksheets("sheet25")   'copying sheet to the specific location
   
    Worksheets("sheet1").Copy   'without option if copied it will be copied in new brand new workbook
   
    Worksheets("sheet1").Copy Workbooks("book1").Sheets(1)      'coping to a another workbook
   
    Sheet1.Move after:=Sheets(Sheets.Count)      'moving of sheet
   
    Worksheets("sheet28").Name = "somethingelse"       'Name change of sheet
   
    Worksheets("somethingelse").Visible = xlSheetHidden     'hiding a sheet
   
    Worksheets("somethingelse").Visible = xlSheetVisible    'Unhiding a sheet
   
    Worksheets("somethingelse").Visible = xlSheetVeryHidden 'user cant unhide the sheet without code

End Sub

WorkBooks

'Workbook Object
'Represents a Microsoft Excel workbook.
'Remarks
'
'
'The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel.
'
'ThisWorkbook property
'The ThisWorkbook property returns the workbook where the Visual Basic code is running. In most cases, this is the same as the active workbook. However, if the Visual Basic code is part of an add-in, the ThisWorkbook property won’t return the active workbook. In this case, the active workbook is the workbook calling the add-in, whereas the ThisWorkbook property returns the add-in workbook.
'
'If you’ll be creating an add-in from your Visual Basic code, you should use the ThisWorkbook property to qualify any statement that must be run on the workbook you compile into the add-in.
'

Option Explicit
'Example WorkBooks
Sub TestWorkBook1()

Workbooks.Add
ActiveWorkbook.SaveAs "C:\Users\Vinay Kumar\Desktop\testWB.xlsx"
ActiveWorkbook.Close

End Sub

Sub TestWorkBook2()

Workbooks.Add.SaveAs Environ("userprofile") & "\Desktop\testWB56.xlsx"
ActiveWorkbook.Close
ActiveWorkbook.Save

End Sub

Sub TestWorkBook3()

Workbooks.Open (Environ("userprofile") & "\desktop\testwb5.xlsx")

End Sub

With Statement

'With Statement
'
'Executes a series of statements on a single object or a user-defined type.
'
'Syntax
'
'With Object
'[statements]
'
'End With
'
'The With statement syntax has these parts:
'
'Part Description
'object Required. Name of an object or a user-defined type.
'statements Optional. One or more statements to be executed on object.
'
'Remarks
'
'The With statement allows you to perform a series of statements on a specified object without requalifying the name of the object. For example, to change a number of different properties on a single object, place the property assignment statements within the With control structure, referring to the object once instead of referring to it with each property assignment. The following example illustrates use of the With statement to assign values to several properties of the same object.


Option Explicit

'Example With Statement
Sub TestWithStatement()
   
    With Range("a1:f1")
        .Interior.Color = vbRed
        .Font.Color = vbBlue
        .Font.Size = 18
        .Select
        .ColumnWidth = 20
    End With
   
End Sub

Type Declartion

'
'Type Statement
'
'Used at module level to define a user-defined data type containing one or more elements.
'
'Syntax
'
'[Private | Public] Type varname
'elementname [([subscripts])] As type
'[elementname [([subscripts])] As type]
'. . .
'
'End Type
'
'The Type statement syntax has these parts:
'
'Part Description
'Public Optional. Used to declare user-defined types that are available to all procedures in all modules in all projects.
'Private Optional. Used to declare user-defined types that are available only within the module where the declaration is made.
'varname Required. Name of the user-defined type; follows standard variable naming conventions.
'elementname Required. Name of an element of the user-defined type. Element names also follow standard variable naming conventions, except that keywords can be used.
'subscripts When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.
'type Required. Data type of the element; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, another user-defined type, or an object type.

Option Explicit
'Type Declartion(User Define Type) at module level only
Type Address
    NameNumber As String
    Street As String
    Town As String
    Country As String
    PostCode As String
End Type

Type Contact
    Title As String
    FirstName As String
    LastName As String
    DateofBirth As Date
   
    HomeAddress As Address
    WorkAddress As Address
End Type

Private Type Student

    Name As String
    Hindi As Integer
    Eng As Integer
    Math As Integer
    Science As Integer
    Behaviour As Behaviours
End Type

Private Enum Behaviours
    Good
    Bad
    Naughty
    Ugly
    Sweet
End Enum

Private Sub TestStudentType()

Dim NewStudent As Student

NewStudent.Name = "Ridhi"
NewStudent.Hindi = 45
NewStudent.Math = 63
NewStudent.Eng = 85
NewStudent.Science = 96
NewStudent.Behaviour = Good
NewStudent.Behaviour = Bad

MsgBox NewStudent.Behaviour     ' Enum output are in numbers start from 0, use SELECT CASE _
                                        to display as string value

Worksheets("Type Declaration").Activate

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

ActiveCell.value = NewStudent.Name
ActiveCell.Offset(0, 1).value = NewStudent.Hindi
ActiveCell.Offset(0, 1).value = NewStudent.Eng
ActiveCell.Offset(0, 1).value = NewStudent.Math
ActiveCell.Offset(0, 1).value = NewStudent.Science

End Sub

Sub TestContact()

Dim C As Contact

C.FirstName = "Candy"
C.HomeAddress.NameNumber = "90"

MsgBox C.FirstName
MsgBox C.HomeAddress.NameNumber
End Sub

Text File

''TextStream Object
'
'Description
'
'Facilitates sequential access to file.
'
'Syntax
'
'TextStream.{property | method}
'
'The property and method arguments can be any of the properties and methods associated with the TextStream object. Note that in actual usage TextStream is replaced by a variable placeholder representing the TextStream object returned from the FileSystemObject.
'

Option Explicit
'Create Text file on desktop and write few lines and close it.
Sub CreatingNewTextFile()

Dim fso As Scripting.FileSystemObject
Dim txt As Scripting.TextStream

Set fso = New Scripting.FileSystemObject
Set txt = fso.CreateTextFile(Environ("userprofile") & "\desktop\test1.txt")

    txt.Write "Created on: " & Now & vbNewLine
    txt.WriteLine "Created by: " & Environ("username")
    txt.WriteBlankLines (2)
    txt.Write ("Data starts from here:")
     txt.WriteBlankLines (2)
txt.Close

Set fso = Nothing

End Sub
'Append a text file and write from the excel file
Sub AddDataToaTextFile()

Dim fso As Scripting.FileSystemObject
Dim txt As Scripting.TextStream
Dim counter As Integer
Dim r As Range

Set fso = New Scripting.FileSystemObject
Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.txt", ForAppending, False)

Sheet2.Activate

For Each r In Range("a1", Range("a1").End(xlDown))

    For counter = 1 To Range("a1", Range("a1").End(xlToRight)).Cells.Count
        txt.Write r.Offset(0, counter - 1).value
            If counter < Range("a1", Range("a1").End(xlToRight)).Cells.Count Then txt.Write vbTab
    Next counter
   
    txt.WriteLine
   
Next r
txt.Close
Set fso = Nothing

End Sub

'Append a CSV file and write from the excel file
Sub AddDataToaCSVFile()

Dim fso As Scripting.FileSystemObject
Dim txt As Scripting.TextStream
Dim counter As Integer
Dim r As Range

Set fso = New Scripting.FileSystemObject
Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.csv", ForAppending, True)

Sheet2.Activate

For Each r In Range("a1", Range("a1").End(xlDown))

    For counter = 1 To Range("a1", Range("a1").End(xlToRight)).Cells.Count
        txt.Write r.Offset(0, counter - 1).value
            If counter < Range("a1", Range("a1").End(xlToRight)).Cells.Count Then txt.Write ","
    Next counter
    txt.WriteLine
   
Next r
txt.Close
Set fso = Nothing

End Sub
'Reading from text file and pasting it on excel
Sub ReadfromTextFile()

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Dim txt As Scripting.TextStream
Dim tabposition As Integer
Dim textline As String

Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.txt", ForReading)
'txt.SkipLine

Worksheets.Add

Do Until txt.ReadLine = "Data starts from here:"
Loop

Do Until txt.AtEndOfStream

     textline = txt.ReadLine
     tabposition = InStr(textline, vbTab)
   
     Do Until tabposition = 0
        ActiveCell.value = Left(textline, tabposition - 1)
        ActiveCell.Offset(0, 1).Select
        textline = Right(textline, Len(textline) - tabposition)
        tabposition = InStr(textline, vbTab)
     Loop
   
    ActiveCell.value = textline
    ActiveCell.Offset(1, 0).End(xlToLeft).Select
   
Loop

txt.Close
Set fso = Nothing

End Sub

'Reading from text file and pasting on excel via text to column
Sub ReadfromTextFileEasierMethod()

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Dim txt As Scripting.TextStream


Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.txt", ForReading)

Worksheets.Add
Do Until txt.AtEndOfStream

   
        ActiveCell.value = txt.ReadLine
       
        ActiveCell.Offset(1, 0).Select
   
Loop
Range("A:A").TextToColumns Tab:=True
txt.Close
Set fso = Nothing

End Sub
'Easiest method to open text file in excel
Sub ReadfromTextFileEasiestMethod()

Workbooks.OpenText filename:=Environ("userprofile") & "\desktop\test1.txt", Tab:=True

End Sub

cell Selection

'Selecting and Activating Cells
'
'When you work with Microsoft Excel, you usually select a cell or cells and then perform an action, such as formatting the cells or entering values in them. In Visual Basic, it is usually not necessary to select cells before modifying them.
'
'For example, if you want to enter a formula in cell D6 using Visual Basic, you do not need to select the range D6. You just need to return the Range object for that cell, and then set the Formula property to the formula you want, as shown in the following example.
'

Option Explicit

Sub TestSelectCells()

Range("a8").Select
ActiveCell.value = 11

Cells(8, 2).Select
ActiveCell.value = "vinay"

[d10].Select
ActiveCell.value = "New Way to refer cells"

End Sub

Sub Name_SelectedCells()

Range("Vendor").Font.Color = rgbBlue
[segment].Font.Color = rgbRed
Range("year").Font.Color = rgbGreen
[value].Font.Color = rgbDarkRed

'relative cell refernce
Range("L5").End(xlDown).Offset(1, 0).Select
ActiveCell.value = ActiveCell.Offset(-1, 0).value + 1
Range("i5", Range("i5").End(xlDown).End(xlToRight)).Interior.Color = rgbAliceBlue

'using current region
Range("a1").CurrentRegion.Select
Selection.Copy
Worksheets("Sheet6").Select
Range("a1").Select
Range("a1").PasteSpecial
Range("a1").PasteSpecial xlPasteColumnWidths

End Sub

Case Statement

'
'Select Case Statement
'
'Executes one of several groups of statements, depending on the value of an expression.
'
'Syntax
'
'Select Case testexpression
'[Case expressionlist-n
'[statements-n]]
'
'...
'
'
'[Case Else
'[elsestatements]]
'
'End Select

Option Explicit
'Example Select Case
Sub TestSelectCase()

Dim ProductPrice As Integer

ProductPrice = InputBox("What is the price of product?")

    Select Case ProductPrice
           
            Case Is <= 2000
                MsgBox "Discount Percent:5% and discounted amount is: " & ProductPrice * 0.05
           
            Case Is <= 5000
                MsgBox "Discount Percent:10% and discounted amount is: " & ProductPrice * 0.1
           
            Case Else
                MsgBox "Discount Percent:15% and discounted amount is: " & ProductPrice * 0.15
           
    End Select
           
    End Sub

Object Variable

'Object variable not set (Error 91)
'
'There are two steps to creating an object variable. First you must declare the object variable. Then you must assign a valid reference to the object variable using the Set statement. Similarly, a With...End With block must be initialized by executing the With statement entry point. This error has the following causes and solutions:
'
'
'You attempted to use an object variable that isn't yet referencing a valid object.
'Specify or respecify a reference for the object variable. For example, if the Set statement is omitted in the following code, an error would be generated on the reference to
'

Option Explicit
Sub test_objectvariable()

Dim testname As Range
Set testname = Range("a7", Range("a7").Offset.End(xlDown).Offset.End(xlToRight))

testname.Font.Color = rgbBlue
Range("a7", Range("a7").Offset.End(xlDown).Offset.End(xlToRight)).Select
testname.Interior.Color = vbRed
testname.Interior.ColorIndex = 23
End Sub

If Statement

'If...Then...Else Statement
'
'Conditionally executes a group of statements, depending on the value of an expression.
'
'Syntax
'
'If Condition Then [statements] [Else elsestatements]
'
'Or, you can use the block form syntax:
'
'If Condition Then
'[statements]
'
'[ElseIf condition-n Then
'[elseifstatements]
'
'...
'
'
'[Else
'[elsestatements]]
'
'End If
'
'The If...Then...Else statement syntax has these parts:
'

Option Explicit

'IF Statement, display message as user inputs
Sub TestIfStatement()

Dim mnt As Integer
mnt = InputBox("Enter the month in number.")

    If mnt >= 13 Or mnt <= 0 Then Exit Sub
   
    If mnt <= 3 Then
        MsgBox "Thandi hai Bhai"
    ElseIf mnt <= 7 Then
        MsgBox "Garmi hai Bhai"
    Else
        MsgBox "Barish hai Bhai"
    End If
   
End Sub

User Defined Function

'
'Function Statement()
'
'Declares the name, arguments, and code that form the body of a Function procedure.
'
'Syntax
'
'[Public | Private | Friend] [Static] Function name [(arglist)] [As type]
'[statements]
'[name = expression]
'[Exit Function]
'[statements]
'[name = expression]
'
'End Function
'
'The Function statement syntax has these parts:
'
'Part Description
'Public Optional. Indicates that the Function procedure is accessible to all other procedures in all modules. If used in a module that contains an Option Private, the procedure is not available outside the project.
'Private Optional. Indicates that the Function procedure is accessible only to other procedures in the module where it is declared.
'Friend Optional. Used only in a class module. Indicates that the Function procedure is visible throughout the project, but not visible to a controller of an instance of an object.
'Static Optional. Indicates that the Function procedure's local variables are preserved between calls. The Static attribute doesn't affect variables that are declared outside the Function, even if they are used in the procedure.
'name Required. Name of the Function; follows standard variable naming conventions.
'arglist Optional. List of variables representing arguments that are passed to the Function procedure when it is called. Multiple variables are separated by commas.
'type Optional. Data type of the value returned by the Function procedure; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String, or (except fixed length), Object, Variant, or any user-defined type.
'statements Optional. Any group of statements to be executed within the Function procedure.
'expression Optional. Return value of the Function.
'
'The arglist argument has the following syntax and parts:
'
'[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue]
'
'Part Description
'Optional Optional. Indicates that an argument is not required. If used, all subsequent arguments in arglist must also be optional and declared using the Optional keyword. Optional can't be used for any argument if ParamArray is used.
'ByVal Optional. Indicates that the argument is passed by value.
'ByRef Optional. Indicates that the argument is passed by reference.ByRef is the default in Visual Basic.
'ParamArray Optional. Used only as the last argument in arglist to indicate that the final argument is an Optional array of Variant elements. The ParamArray keyword allows you to provide an arbitrary number of arguments. It may not be used with ByVal, ByRef, or Optional.
'varname Required. Name of the variable representing the argument; follows standard variable naming conventions.
'type Optional. Data type of the argument passed to the procedure; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported) Date, String (variable length only), Object, Variant, or a specific object type. If the parameter is not Optional, a user-defined type may also be specified.
'defaultvalue Optional. Any constant or constant expression. Valid for Optional parameters only. If the type is an Object, an explicit default value can only be Nothing.
'
'Remarks
'
'If not explicitly specified using Public, Private, or Friend, Function procedures are public by default. If Static isn't used, the value of local variables is not preserved between calls. The Friend keyword can only be used in class modules. However, Friend procedures can be accessed by procedures in any module of a project. A Friend procedure does't appear in the type library of its parent class, nor can a Friend procedure be late bound.
'
'
'  Caution
'Function procedures can be recursive; that is, they can call themselves to perform a given task. However, recursion can lead to stack overflow. The Static keyword usually isn't used with recursive Function procedures.
'
'
'All executable code must be in procedures. You can't define a Function procedure inside another Function, Sub, or Property procedure.
'
'The Exit Function statement causes an immediate exit from a Function procedure. Program execution continues with the statement following the statement that called the Function procedure. Any number of Exit Function statements can appear anywhere in a Function procedure.
'
'Like a Sub procedure, a Function procedure is a separate procedure that can take arguments, perform a series of statements, and change the values of its arguments. However, unlike a Sub procedure, you can use a Function procedure on the right side of an expression in the same way you use any intrinsic function, such as Sqr, Cos, or Chr, when you want to use the value returned by the function.
'
'You call a Function procedure using the function name, followed by the argument list in parentheses, in an expression. See the Call statement for specific information on how to call Function procedures.
'
'To return a value from a function, assign the value to the function name. Any number of such assignments can appear anywhere within the procedure. If no value is assigned to name, the procedure returns a default value: a numeric function returns 0, a string function returns a zero-length string (""), and a Variant function returns Empty. A function that returns an object reference returns Nothing if no object reference is assigned to name (using Set) within the Function.

Option Explicit

Function dateformat() As String
dateformat = Now()
End Function

Function dateformaparameter1(dte As Date) As String
dateformaparameter1 = Format(dte, "dd-mmm-yyyy")
End Function

Function dateformaparameter2(dte As Date, tme As Boolean) As String
dateformaparameter2 = Format(dte, "dd:mmm:yyyy:hh:mm:ss")
End Function

Function dateformaparameter3(dte As Date, tme As Boolean) As String

If tme = True Then
    dateformaparameter3 = Format(dte, "dd:mmm:yyyy:hh:mm:ss")
Else
    dateformaparameter3 = Format(dte, "dd:mmm:yyyy")
End If
End Function

Function dateformaparameter4(dte As Date, Optional tme As Boolean = False) As String

If tme = True Then
    dateformaparameter4 = Format(dte, "dddd, mmm yyyy hh:mm:ss")
Else
    dateformaparameter4 = Format(dte, "dd:mmm:yyyy")
End If
End Function

Function salarystatus(salaryrange As Long) As String
    If salaryrange < 25000 Then
        salarystatus = "Low"
    ElseIf salaryrange < 30000 Then
        salarystatus = "Medium"
    ElseIf salaryrange < 35000 Then
        salarystatus = "High"
    ElseIf salaryrange < 40000 Then
        salarystatus = "Excellent"
    End If


End Function

Sub readingfromtable()
Sheet3.Select
Range("A3").Select
    Do
        ActiveCell.Offset(0, 5).value = _
        salarystatus(ActiveCell.Offset(0, 4).value)
        ActiveCell.Offset(1, 0).Select
    Loop Until ActiveCell.value = ""
End Sub

File System Object

'FileSystemObject Object
'
'Description
'
'Provides access to a computer's file system.
'
'Syntax
'
'Scripting.FileSystemObject
'
'Remarks
'
'The following code illustrates how the FileSystemObject is used to return a TextStream object that can be read from or written to:

Option Explicit
'List all files in a given folder to sheet1
Sub listfilesinfolder()

Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fl As Scripting.File
Dim fldname As String
Dim counter As Integer

Set fso = New Scripting.FileSystemObject
Set fld = fso.GetFolder(fldname & "\")

fldname = Application.InputBox("Enter the folder name to display all the files under it", "Folder Name")

Sheet1.Activate
Range("a1").Activate

For Each fl In fld.Files
    ActiveCell.value = fl.Name
    ActiveCell.Offset(0, 1).value = fl.Type
    ActiveCell.Offset(0, 2).value = fl.Size
    ActiveCell.Offset(1, 0).Select
Next
End Sub

'Creating a folder using file system object and restricting if folder exist
'Copying files to a destination and checking if already exist
Sub UsingTheScriptRunTimeLibrary()

Dim fso As Scripting.FileSystemObject
Dim NewFolderPath As String
Dim OldFolderPath As String

Set fso = New Scripting.FileSystemObject

NewFolderPath = Environ("UserProfile") & "\Desktop\SRTL"
OldFolderPath = Environ("UserProfile") & "\Desktop\Akshit - Rhyams"

If Not fso.FolderExists(NewFolderPath) Then

    fso.CreateFolder (NewFolderPath)
   
End If

If fso.FileExists(OldFolderPath & "\Mamy poko pants.flv") Then
        fso.CopyFile Source:=OldFolderPath & "\Mamy poko pants.flv", _
            Destination:=NewFolderPath & "\Mamy poko pants.flv", overwritefiles:=True
End If

Set fso = Nothing
End Sub

'Creating a folder using file system object and restricting if folder exist
Sub UsingTheScriptRunTimeLibrary2()

Dim fso As Scripting.FileSystemObject
Dim fil As Scripting.File
Dim NewFolderPath As String
Dim OldFolderPath As String

NewFolderPath = Environ("UserProfile") & "\Desktop\SRTL"
OldFolderPath = Environ("UserProfile") & "\Desktop\Akshit - Rhyams"

Set fso = New Scripting.FileSystemObject

If Not fso.FolderExists(NewFolderPath) Then

    fso.CreateFolder (NewFolderPath)
   
End If

If fso.FileExists(OldFolderPath & "\Mamy poko pants.flv") Then
   
Set fil = fso.GetFile(OldFolderPath & "\Mamy poko pants.flv")
   
    If fil.Size > 2000000 Then
        fil.Copy NewFolderPath & "\" & fil.Name
   
    End If
   
End If

Set fso = Nothing
End Sub
'Filerting mp4 file through FileSystem Object
Sub UsingTheScriptRunTimeLibrary3()

Dim fso As Scripting.FileSystemObject
Dim fil As Scripting.File
Dim OldFolder As Scripting.Folder
Dim NewFolderPath As String
Dim OldFolderPath As String

NewFolderPath = Environ("UserProfile") & "\Desktop\SRTL"
OldFolderPath = Environ("UserProfile") & "\Desktop\Akshit - Rhyams"

Set fso = New Scripting.FileSystemObject

If fso.FolderExists(OldFolderPath) Then

    Set OldFolder = fso.GetFolder(OldFolderPath)

        If Not fso.FolderExists(NewFolderPath) Then
            fso.CreateFolder (NewFolderPath)
        End If

            For Each fil In OldFolder.Files
                If Left(fso.GetExtensionName(fil.Path), 3) = "mp4" Then
                    fil.Copy NewFolderPath & "\" & fil.Name
                End If
            Next fil
End If

Set fso = Nothing

End Sub

Filters

'Filter Object
'Represents a filter for a single column.
'Remarks
''
'The Filter object is a member of the Filters collection. The Filters collection contains all the filters in an autofiltered range.
''
'Example
''
'Use Filters(index), where index is the filter title or index number, to return a single Filter object. The following example sets a variable to the value of the On property of the filter for the first column in the filtered range on the Crew worksheet.


Option Explicit

Sub a1()
Sheet4.Activate
Range("1:1").AutoFilter field:=6, Criteria1:="Binders", _
    Operator:=xlOr, Criteria2:="Pencils"
Range("1:1").AutoFilter field:=4, Criteria1:="Alberta"

End Sub

File Dialog Box

'FileDialog Object
'Provides file dialog box functionality similar to the functionality of the standard Open and Save dialog boxes found in Microsoft Office applications.
'Remarks
''
'Use the FileDialog property to return a FileDialog object. The FileDialog property is located in each individual Office application's Application object. The property takes a single argument, DialogType, that determines the type of FileDialog object that the property returns. There are four types of FileDialog object:
'
'Open dialog box - lets users select one or more files that you can then open in the host application using the Execute method.
'SaveAs dialog box - lets users select a single file that you can then save the current file as using the Execute method.
'File Picker dialog box - lets users select one or more files. The file paths that the user selects are captured in the FileDialogSelectedItems collection.
'Folder Picker dialog box - lets users select a path. The path that the user selects is captured in the FileDialogSelectedItems collection.
'Each host application can only create a single instance of the FileDialog object. Therefore, many of the properties of the FileDialog object persist even when you create multiple FileDialog objects. Therefore, make sure that you set all of the properties appropriately for your purpose before you display the dialog box.
''
'Example
''
'To display a file dialog box using the FileDialog object, you must use the Show method. Once a dialog box is displayed, no code executes until the user dismisses the dialog box. The following example creates and displays a File Picker dialog box and then displays each selected file in a message box.
'

Option Explicit

'File Dialog box to OPEN a file
Sub OpenAFile()

Dim fd As FileDialog
Dim ChooseAFile As Boolean

Set fd = Application.FileDialog(msoFileDialogOpen)

            With fd
                .Title = "Choose file from the list"
                .AllowMultiSelect = False
                .InitialFileName = Environ("UserProfile") & "\Desktop"
                .ButtonName = "Open/Go!!"
                .Filters.Clear
                .Filters.Add " Any Excel Files", "*.xl*"
                .Filters.Add " Old Excel Files", "*.xls"
                .Filters.Add " New Excel Files", "*.xlsx"
                .Filters.Add " Macro Enabled Excel Files", "*.xlsm"
                .FilterIndex = 3
            End With
           
ChooseAFile = fd.Show
If Not ChooseAFile Then
    MsgBox "You didn't choose a file!"
Else
    fd.Execute
End If
Set fd = Nothing
End Sub

'File Dialog box to SAVE a file
Sub SaveAFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogSaveAs)
    With fd
        .Title = "Save File"
        .InitialFileName = Environ("UserProfile") & "\Desktop\" _
            & ThisWorkbook.Name
        .FilterIndex = 1
        .Show
        .Execute
    End With
End Sub

'Procedure to choose FOLDER and FILE to copy
Sub PickAFile()
Dim fd As FileDialog
Dim copyfilepath As String
Dim counter As Integer
Dim choosenfile As Boolean
Dim folderpath As String

'pick up the folder
Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    With fd
        .Title = "Choose folder to copy files with in"
        .AllowMultiSelect = False
        .InitialFileName = Environ("UserProfile") & "\Desktop"
    End With
   
    choosenfile = fd.Show
       
        If Not choosenfile Then
            MsgBox "You didn't choose folder"
            Exit Sub
        Else
          folderpath = fd.SelectedItems(1)
 
        End If
   

'choose files to copy
Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .Title = "Choose multiple file to Copy"
        .AllowMultiSelect = True
        .InitialFileName = Environ("UserProfile") & "\Desktop"
    End With
   
        choosenfile = fd.Show
       
        If Not choosenfile Then
            MsgBox "You didn't choose file"
            Exit Sub
        Else
            For counter = 1 To fd.SelectedItems.Count
                copyfilepath = fd.SelectedItems(counter)
                Call CreateCopyofFile(copyfilepath, folderpath)
            Next
        End If
       
           
End Sub
'Refernce procedure to copy files in choosen foder/destination
Sub CreateCopyofFile(copyfilepath As String, folderpath As String)

Dim fso As Scripting.FileSystemObject
Dim fl As File


Set fso = New Scripting.FileSystemObject
Set fl = fso.GetFile(copyfilepath)

fso.CopyFile copyfilepath, folderpath & "\" & fl.Name

End Sub

Loop

Option Explicit

'Endless do loop

Sub TestDoLoop()

    Range("a1").Select
  Do
    ActiveCell.Offset(1, 0).Select
  Loop
 
End Sub

'Do until active cell is not blank
Sub TestDoUntil()

Range("a1").Select

    Do Until ActiveCell.value = ""
    ActiveCell.Offset(1, 0).Select
    Loop
   
End Sub
'Loop until active cell is not blank
Sub TestLoopUntil()
 
   Range(Range("a1"), Range("a1").End(xlToRight)).Copy Range("a17")
   Range("c2").Select
   
    Do
        If ActiveCell.value = "Male" Then
            Range("a" & ActiveCell.Row, ActiveCell.End(xlToRight)).Copy _
                Range("a" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
       
    ActiveCell.Offset(1, 0).Select
   
    Loop Until ActiveCell.value = ""
   
End Sub

'Do While active cell is not blank
Sub TestDoWhile()
   Range(Range("a1"), Range("a1").End(xlToRight)).Copy Range("a17")
   Range("c2").Select
   
    Do While Not ActiveCell.value = ""
        If ActiveCell.value = "Male" Then
            Range("a" & ActiveCell.Row, ActiveCell.End(xlToRight)).Copy _
                Range("a" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    ActiveCell.Offset(1, 0).Select

    Loop
   
End Sub

'Loop While active cell is not blank
Sub TestLoopWhile()
Range(Range("a1"), Range("a1").End(xlToRight)).Copy Range("a17")
Range("c2").Select

Do
        If ActiveCell.value = "Male" Then
            Range("a" & ActiveCell.Row, ActiveCell.End(xlToRight)).Copy _
                Range("a" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    ActiveCell.Offset(1, 0).Select

Loop While Not ActiveCell.value = ""

End Sub


Array

'Declaring Arrays
'
'Arrays are declared the same way as other variables, using the Dim, Static, Private, or Public statements. The difference between scalar variables (those that aren't arrays) and array variables is that you generally must specify the size of the array. An array whose size is specified is a fixed-size array. An array whose size can be changed while a program is running is a dynamic array.
'
'Whether an array is indexed from 0 or 1 depends on the setting of the Option Base statement. If Option Base 1 is not specified, all array indexes begin at zero.
'
'Declaring a Fixed Array
'In the following line of code, a fixed-size array is declared as an Integer array having 11 rows and 11 columns:

'Using Arrays
'
'You can declare an array to work with a set of values of the same data type. An array is a single variable with many compartments to store values, while a typical variable has only one storage compartment in which it can store only one value. Refer to the array as a whole when you want to refer to all the values it holds, or you can refer to its individual elements.
'
'For example, to store daily expenses for each day of the year, you can declare one array variable with 365 elements, rather than declaring 365 variables. Each element in an array contains one value. The following statement declares the array variable
'
'curExpense
'
'with 365 elements. By default, an array is indexed beginning with zero, so the upper bound of the array is 364 rather than 365.
'
'Dim curExpense(364) As Currency


Option Explicit

'1. Write a VBA code to display months in array format from Jan-Dec.
Sub ArrayExp()

Dim i As Integer
Dim months_array(12) As String

    For i = 1 To 12
        months_array(i) = Sheets("Arrays").Cells(i, 1)
        MsgBox months_array(i)
    Next
   
End Sub

'2. Fixed Array to store value and place it to respective destination
Sub FixedSizeArray()

Dim testname(1 To 3) As String
Sheets("Arrays").Activate
   
    testname(1) = Range("a1").value
    testname(2) = Range("a2").value
    testname(3) = Range("a3").value
   
    Range("b1").value = testname(1)
    Range("b2").value = testname(2)
    Range("b3").value = testname(3)

Erase testname

End Sub
'3. Loopover Array to demonstrate storing values in array through FOR loop
Sub LoopOverArray()

Dim topnames(1 To 13) As String
Dim counter As Integer

Sheet5.Activate
    For counter = LBound(topnames) To UBound(topnames)
        topnames(counter) = Range("c" & counter + 1).value
    Next
         Range("d2").Select
 
    For counter = UBound(topnames) To LBound(topnames) Step -1
        ActiveCell.value = topnames(counter)
        ActiveCell.Offset(1, 0).Select
    Next
 Erase topnames

End Sub
'4. Example of multidimentional array to store 2 dimention array
Sub MultiDimensionArray()

Dim topname(0 To 9, 0 To 6) As Variant
Dim dimension1 As Integer, dimension2 As Integer
Sheet5.Activate

For dimension1 = LBound(topname, 1) To UBound(topname, 1)
    For dimension2 = LBound(topname, 2) To UBound(topname, 2)
        topname(dimension1, dimension2) = Range("e2").Offset(dimension1, _
            dimension2).value
    Next dimension2
Next dimension1

Range("m2").Select

For dimension1 = LBound(topname, 1) To UBound(topname, 1)
    For dimension2 = LBound(topname, 2) To UBound(topname, 2)
       ActiveCell.Offset(dimension1, dimension2).value = topname(dimension1, dimension2)
    Next dimension2
Next dimension1

Erase topname

End Sub
'5. Example of dynamic multi dimention array where rows and columns are flexible according to data
Sub DynamicMultiDimensionArray()

Dim topname() As Variant
Dim dimension1 As Integer, dimension2 As Integer

dimension1 = Range("a17", Range("a17").End(xlDown)).Cells.Count - 1
dimension2 = Range("a17", Range("a17").End(xlToRight)).Cells.Count - 1

ReDim topname(0 To dimension1, 0 To dimension2)
Sheet5.Activate

For dimension1 = LBound(topname, 1) To UBound(topname, 1)
    For dimension2 = LBound(topname, 2) To UBound(topname, 2)
        topname(dimension1, dimension2) = Range("a17").Offset(dimension1, dimension2).value
    Next dimension2
Next dimension1

Range("m17").Select

For dimension1 = LBound(topname, 1) To UBound(topname, 1)
    For dimension2 = LBound(topname, 2) To UBound(topname, 2)
       ActiveCell.Offset(dimension1, dimension2).value = topname(dimension1, dimension2)
    Next dimension2
Next dimension1

Erase topname

End Sub
'6. Storing array directly from the range and pasting it at destination
Sub QuickDynamicMultiDimensionArray()

Dim topname() As Variant
Sheet5.Activate

topname = Range("a31", Range("a31").End(xlDown).End(xlToRight))
Range("m31").Select


Range(ActiveCell, ActiveCell.Offset(UBound(topname, 1) - 1, UBound(topname, 2) - 1)).value = topname

Erase topname

End Sub

'7. Store calculation in array and paste it at destination
Sub CalculateWithArray()

Dim testheight() As Variant
Dim answers() As Variant
Dim counter As Long, dimension1 As Long

Sheet5.Activate

testheight = Range("B46", Range("B46").End(xlDown))
dimension1 = UBound(testheight, 1)

ReDim answers(1 To dimension1, 1 To 2)

For counter = 1 To dimension1
    answers(counter, 1) = Int(testheight(counter, 1) / 60)
    answers(counter, 2) = testheight(counter, 1) Mod 60
Next counter

Range("c46", Range("c46").Offset(dimension1 - 1, 1)).value = answers

Erase testheight
Erase answers

End Sub


Input Box - User Input




'Application.InputBox Method
'Displays a dialog box for user input. Returns the information entered in the dialog box.
'Syntax
'expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
'expression   A variable that represents an Application object.
'The following table lists the values that can be passed in the Type argument. Can be one or _
    a sum of the values. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.

'Value                                      Meaning
'0                                          A Formula
'1                                          A Number
'2                                          Text (a string)
'4                                          A logical value (True or False)
'8                                          A cell reference, as a Range object
'16                                         An error value, such as #N/A
'                                           64 An array of values

'Toggle between gridlines

Sub ToggleGridlines()

ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

End Sub

Sub TestAppInputBox()

Dim mnt As String
Dim id As Long
Dim myformula As String
Dim formulacell As Range
Dim copyrange As Range
Dim destinationrange As Range

mnt = Application.InputBox("Enter the month")       'use of application inputbox
Range("a1").End(xlDown).Offset(1, 0).value = mnt

id = Application.InputBox("Enter the Id no", , , , , , , 1)         'putting validation in application inputbox
Range("d1").End(xlDown).Offset(1, 0).value = id

myformula = Application.InputBox("Enter the Formula:", Type:=0, _
                Default:="=Sum(")    'putting formula by user for calculation _
                    also use of default type
Range("k2").FormulaLocal = myformula

Set formulacell = Application.InputBox("Enter the formulacell", Type:=8)        'entering the formula cell
formulacell.FormulaLocal = myformula

Set copyrange = Application.InputBox("Choose range to copy", Type:=8)       'Using inputbox to copy and paste
Set destinationrange = Application.InputBox("Choose destination cell", Type:=8)

copyrange.Copy destinationrange

End Sub

Sub ReturnArray()           'User input in array and performing calculation based on array

Dim testarray() As Variant
Dim counter As Long
Dim destinationrange As Range

testarray() = Application.InputBox("Choose the range of cells", Type:=64)
For counter = LBound(testarray, 1) To UBound(testarray, 1)
   
    testarray(counter, 1) = Int(testarray(counter, 1) / 60) & _
    " Hours & " & (testarray(counter, 1) Mod 60) & " Minutes"

Next counter

Set destinationrange = Application.InputBox("Choose the destination", Type:=8)
Set destinationrange = Range(destinationrange, destinationrange.Offset(UBound(testarray, 1) - 1, 0))

destinationrange = testarray

End Sub

*INTERVIEW QUESTIONS

* Ques 01. What is the difference between ByVal and ByRef and which is default ? Ans-  ByRef : If you pass an argument by reference when...