VBA Thick Border Around Range

Option Explicit

Sub ThickBorderAround(rng As Range)

    rng.Borders(xlDiagonalDown).LineStyle = xlNone

    rng.Borders(xlDiagonalUp).LineStyle = xlNone

    With rng.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

    With rng.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

    With rng.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

    With rng.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

End Sub

VBA Replace Text In Range in Excel

Sub ReplaceTextInRange(sht As Worksheet, rngSearch As Range, strFindText As String,  strReplaceText As String)

    Dim cellFound As Range
    Dim firstAddress As String

    ActiveWorkbook.Sheets(sht.Name).Activate
 
    With rngSearch
        Set cellFound = .Find(strFindText, LookIn:=xlValues)
        If Not cellFound Is Nothing Then
            firstAddress = cellFound.Address
            Do
                cellFound.Value = VBA.Replace(cellFound.Value, strFindText, strReplaceText)
                Set cellFound = .FindNext(cellFound)
''            Loop While cellFound.Address <> firstAddress
            Loop Until cellFound Is Nothing
        End If
    End With

End Sub

Sheet Password Breaker

Sub PasswordBreaker()

'Breaks worksheet password protection.

Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

Limitation of VBA

The VBA environment is installed when we install the host application in the computer. The VBA environment is dependent on the host application. When host application runs, the VBA environment is setup and quitting the host application closes the VBA environment associated with it.

It means that we will always have to open a host application to run VBA. Therefore, it is not possible to build a stand-alone applications with VBA, but we can display the user forms created in any Office application by hiding the host application. This creates the illusion of a stand-alone application. The VBA environment runs under the hood and the top environment is that of the host application.

VBA Header Setup in Excel sheets

Option Explicit

Sub ReplaceAllHeaders()
    Dim i As Long
    For i = 1 To ActiveWorkbook.Sheets.Count
        With Sheets(i).PageSetup
            If Sheets(i).PageSetup.CenterHeader = "" Then
            Sheets(i).PageSetup.CenterHeader = "Header " & i
            Else
                Sheets(i).PageSetup.CenterHeader = Replace( _
                Sheets(i).PageSetup.CenterHeader, "Header", "AppliedK")
            End If
        End With
    Next i
End Sub

VBA Disable right click on all sheets of workbook

DISABLE RIGHT CLICK ON ALL SHEETS OF WORKBOOK

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Cancel = True   '' disable right click on all sheets
End Sub

DISABLE RIGHT CLICK ON ALL SHEET TABS OF WORKBOOK

Private Sub Workbook_Deactivate()
    Application.CommandBars("Ply").Enabled = False
End Sub

Excel- Radar chart

By Ajeet Kumar

RADAR CHART

In radar chart, the categorical variable is displayed as spikes radiating from a central point. The values of a numeric variable is measured along the spikes and by joining these points, we get the radar chart for that. In above table, Month is a categorical or classifying variable and GROUP-A is a numeric variable. The radar chart is plotted for this Group. This is repeated for another numeric variable GROUP-B also.In Business Analysis, Radar Chart is useful to compare two brands etc.

Excel- Symbols

GENERATING SYMBOLS IN EXCEL

To generate symbols in MS-Excel, Press/Hold down the ALT key and then enter the numeric values using Numeric Keys. When the ALT key is released, the desired symbol is generated. For example, some selective symbols are tabulated below.

Press down ALT Key & Use Numeric Key	SYMBOL
1	☺
2	☻
3	♥
4	♦
5	♣
6	♠
7	•
8	◘
9	○
10	◙
11	♂
12	♀
13	♪
14	♫
15	☼
16	►
17	◄
18	↕
19	‼
20	¶
21	§
22	▬
23	↨
24	↑
25	↓
26	→
27	←
And so on

Excel- Create Dynamic range

By Ajeet Kumar 

EXCEL: CREATING DYNAMIC LIST

GIVEN: Suppose you have a list in column A of Sheet1 of a workbook. The list can grow or shrink as per need.

OBJECTIVE: You want to load all these data in a list. To achieve this objective, we can do the following steps:

STEPS:

1. Choose a cell on any sheet of the workbook.
2. Click Data > Data Validation > Data Validation…
3. The Data Validation dialog box appears.
4. In Validation Criteria, select List
5. Write the following formula in the Source text box and click OK.

When you add or remove data from the list, the list will get updated.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)