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

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