How to check the size of a picture in bytes in EXCEL VBA

To check the size in bytes of your pictures, use the "FileLen()" function of VBA:

Sub Check_pic_size(mypic)

Set mypic = ActiveSheet.Shapes.AddPicture(pp, msoFalse, msoTrue, ActiveCell.Left, ActiveCell.Top, -1, -1)

If FileLen(pp) > 100000 Then
     MsgBox ("The size of your file is too big, please reduce to 100,000 bytes. Current size is " &      FileLen(pp) & " bytes.")
     mypic.Delete
     Exit Sub
End If

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

Good practice when using EXCEL comparison function (VLOOKUP(), MATCH(), INDEX()) of values in text and number format

The "TRIM()" function in EXCEL helps to prevent error when comparing values in number format with value in text format. This "TRIM()" function will remove all the "space characters: in front of the value and will also convert any number format into a text format.
 



 

This function is particularly interesting when a large number of data need to be compared. It will ensure that you are comparing correctly text to text.

 

One of the good practice is therefore to add one more column to trim the keys of your database, and then to search the trimmed value into this column (works also with MATCH() function, INDEX() function, etc.)

 

 

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 remove the hyperlinks from selected range in EXCEL

There is a built in function in EXCEL to remove the hyperlinks: Select the designated area and go to "Home" ribbon > "Clear" dropdown menu > and press "Clear Hyperlinks" :
 

 

 

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 modify the reference cell in an Excel formula using the indirect function

The "indirect" function allows to decompose your formula and enter the reference cells outside of the formula.

For example if you want to multiply the cells A1 per 10 then you can write the text "A1" in a separate cell  (in C1 in the example below) and then simply enter the formula:

=INDIRECT(C1)*10

This function is particularly interesting if you want to apply a certain logic on the reference cells and to control them "outside" of the formula.

The example below show how to retrieve the value of cells A1s of each sheet of the workbook: enter the sheet names in column A, the cells reference in column B and enter the formula:

=INDIRECT(A3&"!"&B3)

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