Video example: Import and size pictures in EXCEL VBA

This video show an example of contact file where up to 30 profiles can be added with profile info and autosized profile pic.

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to display all the elements of an array in a message box VBA

If you want to know what is contained in your VBA array simply use the Join method:

Join(my_array,vbCr)

Example: if you want to add the following data into an array and then display the array

Sub print_array_example()

Dim arr() As Variant

ReDim arr(1 To 5) As Variant

For i = 1 To 5

    arr(i) = Cells(i, 1)

Next

  

MsgBox Join(arr, vbCr)

End Sub

 

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to autofilter multiple values in Excel based on a range using a VBA macro

The autofilter of Excel is very powerful, however when you want to filter multiple items at once you'll have to select each ones individually:

This macro below will filter the first column of the database sheet based on a user range in sheet "Filter range". 

Sub filter_range()

Sheets("database").Range("$A$2:$B$2").AutoFilter Field:=1, Criteria1:=Application.Transpose(Sheets("Filter range").Range("A2:A8")), Operator:=xlFilterValues

End Sub

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to create an array in VBA from an Excel range

If you have some data in Excel you want to retrieve in VBA under an Array, just use the "=" sign:

Sub create_array()

newarray = Sheets("Sheet1").Range("A1:B10").Value

MsgBox newarray(6, 2)

End Sub

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to find a value in an Excel range and return its address in VBA

If you want to find the word "DoMyExcel" in a range of your spreadsheet and return its location / address, create a module with this code:

Sub Return_Search_Result_Adress()

    Dim My_Range As Range
    Set My_Range = Sheets("Sheet1").Range("A1:A10000").Find("DoMyExcel", lookat:=xlPart)
    If Not My_Range Is Nothing Then
           MsgBox "Found at " & My_Range.Address
    End If
End Sub

• xlPart = looks at the text in the cell for any match. If you search for any part of the text (such as "Excel"), it will return "Found at $A$6".
• xlWhole = looks at the entire/exact entry in the cell to see if it matches.

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to retrieve the Excel sheet name of a range in VBA

To retrieve the name of the sheet ws where your range "My_Range" is located, simply write:

ws = Range(My_Range).Parent.Name

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to retrieve a manual selection of Excel in VBA and apply a color code to all cells of the selection

This VBA code below helps to apply a specific color coding formatting to a specific selection without using the conditional formatting of excel.
Note: in this example a user interface is used with the the userform command RefEdit to allow the user to manually select the range of cells he wants to apply the formatting on. 

Private Sub CommandButton1_Click() 'macro to release when the button "Put color" is pressed

Dim rng As Range
Dim c As Range

Set rng = Range(RefEdit1.Value) 'RefEdit1 refers to the textbox refedit of the userform

For Each c In rng 'loop to go through all the cells of the selected range

    On Error Resume Next
    If Application.WorksheetFunction.IsNumber(c.Value) And c.Value <> "" Then 'if the cell content is a number and not empty...
        If c.Value >= 0 Then 'then depending on its value, we apply a fill color
        c.Interior.ColorIndex = 43 'green
        Else
        c.Interior.ColorIndex = 46 'red
        End If
    ElseIf c.Value <> "" Then 'if the cell content is a text
        c.Interior.ColorIndex = 34 'blue
    Else 'if the cell is empty
        c.Interior.ColorIndex = 2
    End If

Next

End Sub

Before Macro (click on "color code macro"):

Userform opening:

Click on the cell selection button (control "refedit") and manual selection.

Click on "Put Color" button to apply specific color coding:

(green for positive values, red for negative values, blue for text, rest in white)

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to work with named ranges in Excel and VBA macros

Managing multiple named ranges in Excel

Using named range is very useful to manage huge amount of data. The button “Name manager” in menu “Formulas” permits to control all the named range you created: you can edit, rename and delete them easily.

The following code example clears the content of all the 16 named range of the above spreadsheet (“CAT_1”, “CAT_2”, etc):

Sub clear_content_of_named_ranges()
For i = 1 To 16 'code to clear all the cells of the 16 named ranges of the Excel spreadsheet without modifying the cell format
Range("CAT_" & i).ClearContents
Next
End Sub

Created by DoMyExcel.com

Customized Excel Solutions starting at $29!

Code syntax: charles.racaud.free.fr