MS Excel

VBA refer to Chart

Dim cht As Chart
Dim ser As Series
'For a chart in a Worksheet
Set cht = Worksheets("MySheet").ChartObjects("MyChart").Chart
'For a chart in a Chartsheet
Set cht = Charts("MyChart")
Set ser = cht.SeriesCollection(1)

Call a private sub

Use Application.Run "Subroutine Name"

e.g.

use
Application.Run "SetUnProtect"

to run
Private Sub SetUnProtect()

Method to hide a macro in the macro window

Declare a Private subroutine instead of a Public subroutine

e.g.

use
Private Sub SetUnProtect()

instead of
Sub SetUnProtect() or Public Sub SetUnProtect()

Copy Conditional Formatting Only

Select the cell currently with conditional formatting
Go to Home tab ==> Conditional Formatting ==> Manage Rules
Click on "Applies to" section and select the cells you want to paste the conditional formatting to. You can use Ctrl + mouse click to select multiple cells and don't forget to include the Original Cells you select with conditional formatting.

Allow only Text or only Numbers Entered into a Cell in Excel - Data Validation

Go Data Validation, choose custom formula and enter below

=IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))

Change A2 to the cell and change 0123456789abcdefghijklmnopqrstuvwxyz to something else if need to.

Fix excel treat "" as nonblank for a range by using vba code

Fix excel treat "" as nonblank for a range by using vba code

ws.Range("M18:GJ517").Value = ws.Range("M18:GJ517").Value

Reset the last cell or data point of Excel worksheet

Use vba code below to Reset the last cell of Excel worksheet

ActiveSheet.UsedRange

Update values when linked data validation list changes

Use Change event in VBA to achieve this

example below data validation list source in F7:F10, B3:B4 are two dropdowns

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim count_cells As Integer
Dim new_value As String
Dim old_value As String
For count_cells = 1 To Range("F6").CurrentRegion.Rows.Count - 1
    If Intersect(Target, Range("F" & count_cells + 6)) Is Nothing Then
    Else
        Application.EnableEvents = False
        new_value = Target.Value
        Application.Undo
        old_value = Target.Value
        Target.Value = new_value
        Range("B3:B4").Select
        Selection.Replace What:=old_value, Replacement:=new_value, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Target.Select
    End If
Next count_cells
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

command button auto resize fix

Command button auto resize fix
1) In design view, select all the controls in the sheet.
2) Right click and select Group->Group to group the controls, then save the workbook.

Use of symbol # in vba code

The type-declaration character for Double is the number sign (#). Also called HASH
Other type declaration characters are:

Integer %
Long &
Currency @
Single !
Double #
String $

Don't understand the significance of # here.

It implies that when the expression is evaluated, the number in front of the type declaration character is treated as a specific data type instead of as a Variant.
See this example, which are basically the same.

Sub Sample1()
    Dim a#

    a = 1.2

    Debug.Print a
End Sub

Sub Sample2()
    Dim a As Double

    a = 1.2

    Debug.Print a
End Sub

EDIT
Let me explain it a little more in detail.
Consider this two procedures

Sub Sample1()
    Dim a As Double, b As Integer

    b = 32767
    a = b * 100

    Debug.Print a
End Sub

Sub Sample2()
    Dim a As Double, b As Integer

    b = 32767
    a = b * 100#

    Debug.Print a
End Sub

Question: One of them will fail. Can you guess which one?
Ans: The 1st procedure Sub Sample1() will fail.
Reason:
In Sample2, when you do b * 100# the result of calculation will be of type Double. Since it is within the limits of Double, so the calculation succeeds and the result is assigned to variable a.
Now in Sample1, when you do b * 100 the result of calculation will be of type Integer, since both the operands are of type integer. But the result of calculation exceeds the limits of Integer storage. As a result it will error out.