Thursday, August 28, 2014

How to Apply Border Around or Outside Border to the Selection in Excel VBA Macro

Excel VBA Macro to Apply Border Around or Outsider Border to the Selection
The following simple macros shows the setting borders to the section of cells.
Sub BorderAround()
Selection.BorderAround , ColorIndex:=xlAutomatic, Weight:=xlMedium
'For Thin Border
'Selection.BorderAround , ColorIndex:=xlAutomatic, Weight:=xlThin
'For Thick Border
'Selection.BorderAround , ColorIndex:=xlAutomatic, Weight:=xlThick
End Sub

Example :
--------------------------------------------------------------------------------------------
Macro to Change the Border Styles :
Sub CellsBorderStyles()

Selection.Borders.LineStyle = xlContinuous
Selection.Borders.LineStyle = xlDouble
End Sub

Thanks, TAMATAM

Worksheet Event Worksheet_Activate to Hide the Blank Data Rows

Worksheet_Activate Event to Hide the Blank Data Rows with VBA Macro
Worksheet_Activate is one of the Worksheet Event , this Event occurs when the Worksheet get activated and then Macro code will Execute.

The following macro will Hide the Data Rows whose Data in C column range is either '0' or blank as given in below example.

Private Sub Worksheet_Activate()
Dim WB As Workbook
Dim WS As Worksheet
Dim X As Integer

Set WB = ThisWorkbook
Set WS = WB.Worksheets(ActiveSheet.Name)

WS.Rows("1:20").Hidden = False

For X = 1 To 20

MyVal = WS.Range("C" & X).Value
If MyVal = 0 Or MyVal = "" Then WS.Rows(X).Hidden = True

Next X
End Sub


Example :
Data before Event Occur :
Data after Event Occur :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Tuesday, August 26, 2014

How to Create User Defined Functions in Excel VBA

User Defined Functions in Excel VBA
In Excel VBA , We can create our own Functions known as User Defined functions , as Excel Built-In Functions to perform several tasks easily.

Creating user-defined functions allows you to write simpler code instead of having to use complicated formulas that come with MS Excel,thus allows you to accomplish a job easier. The user-defined functions can be entered into any cell or on the formula bar of the spreadsheet just like entering the built-in formulas of the MS Excel spreadsheet.

Syntax of User-defined Function :
Public Function FunctionName (Arg As DataType,……….) As DataType
or
Private Function FunctionName (Arg As DataType,……….) As DataType


* Public indicates that the function is applicable to the whole project while Private indicates that the function is only applicable to a certain module or procedure.

Here :
'Arg' is the Argument Variable that you are passing into the Function.
In order to create a user-defined function in Excel VBA, you need to go into the Visual Basic Editor in MS Excel Spreadsheet.
In the Visual Basic Editor, click on Insert on the menu bar to insert a module into the project, as shown in Figure.
---------------------------------------------------------------------------------------------------------------------
Example - I :
Let us see the below Example where we are creating a User-Defined-Function called "Grade".
The grade will automatically compute examination grades based on the marks that a student obtained. The code is shown below:

Function Grade(Marks As Integer) As Variant
Select Case Marks
Case Is >= 85
Grade = "A"
Case Is >= 65
Grade = "B"
Case Is >= 50
Grade = "C"
Case Is >= 35
Grade = "D"
Case Is < 35
Grade = "E"
End Select
End Function

Now you can see the  User-Defined-Function called "Grade" in Worksheet Functions and it gives the results like as below:


Result :
-----------------------------------------------------------------------------------------------------------------------
Example - II :
Let us see the below Example where we are creating a User-Defined-Function called "YoY_Growth" to calculate the YoY Growth Percentage.

Function YOY_Growth(X As Double, Y As Double)

YOY_Growth = Format((Y - X) / X, "0%")

End Function

Result :


Thanks, TAMATAM

Friday, August 8, 2014

How To Delete Filtered Rows from a Range in Excel VBA

Excel VBA Macro to Delete Filtered Rows from a Range in a Sheet
Sub Del_Filter()
MyRange = ActiveSheet.UsedRange.Rows.Count
'MsgBox MyRange

ActiveSheet.Range("$A$1:$A$" & MyRange).AutoFilter Field:=1, Criteria1:="Invalid" _
        , Operator:=xlOr, Criteria2:="#NA"

With ActiveSheet.AutoFilter.Range
    .Offset(1).Resize(MyRange - 1).EntireRow.Delete
End With

'Removing the Auto Filter 
ActiveSheet.Range("A1").Select

        If ActiveSheet.AutoFilterMode Then Selection.AutoFilter

End Sub

Example :

Suppose We have the Data of Months in Column A , which contains two Invalid values known as "Invalid","#N/A" . If you want to Remove these two Invalid values from the below data , we can use the above Macro.
Month Name
Month Number
Quarter
Quarter Number
Aug
1
Q1
1
Sep
2
Q1
1
Oct
3
Q1
1
Nov
4
Q2
2
Dec
5
Q2
2
#NA
6
Q2
2
Feb
7
Q3
3
Mar
8
Q3
3
Invalid
9
Q3
3
May
10
Q4
4
Jun
11
Q4
4
Jul
12
Q4
4

Macro Result:
Month Name
Month Number
Quarter
Quarter Number
Aug
1
Q1
1
Sep
2
Q1
1
Oct
3
Q1
1
Nov
4
Q2
2
Dec
5
Q2
2
Feb
7
Q3
3
Mar
8
Q3
3
May
10
Q4
4
Jun
11
Q4
4
Jul
12
Q4
4

Thanks.,TAMATAM       

Featured Post from this Blog

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

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

Popular Posts from this Blog