Archive for Formatting


Leave a Comment

Convert Decimal to RGB Color Value

– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
Last week , somebody sent me a mail asking if there is a way to convert decimal values of colors to rgb values. I thought excel solver addin already provides different ways to convert binary, decimal, hex values, so the function to convert decimal values to rgb values should be available in solver. But then I was not able to get function to convert decimal to rgb. I tried some functions in excel and made the function to convert decimal to rgb. I am not too sure if this is absolutely correct; but I have tried this with some of the decimals and I am getting the correct rgb value.


Let me know if you have any comment or feedback on this.

Private Function Convert_Dec2RGB(ByVal myDECIMAL As Long) As String
  Dim myRED As Long
  Dim myGREEN As Long
  Dim myBLUE As Long

  myRED = myDECIMAL And &HFF
  myGREEN = (myDECIMAL And &HFF00&) \ 256
  myBLUE = myDECIMAL \ 65536

  Convert_Dec2RGB = CStr(myRED) & "," & CStr(myGREEN) & "," & CStr(myBLUE)
End Sub

Leave a Comment

Reduce Excel File Size

-Selva V Pasupathy, HSBC Global Resourcing, Hyderabad1

Option Explicit
'       Written by Selva V Pasupathy
'       You are free to use the code and make changes
'       contact me at selvavinaygam (at) GMail (dot) com
Dim j               As Long
Dim k               As Long
Dim lRow         As Long
Dim lCol                  As Long
Dim cFormula      As Range
Dim rFormula      As Range
Dim cValue        As Range
Dim rValue        As Range
Dim myShape             As Shape
Dim mySheet              As Worksheet

Sub ExcelDiet()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 For Each mySheet In Worksheets
   With mySheet
    On Error Resume Next
    Set cFormula = .Cells.Find(What:="*", _
            After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, _
    Set cValue = .Cells.Find(What:="*", _
            After:=Range("A1"), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, _
    Set rFormula = .Cells.Find(What:="*", _
            After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRomySheet, _
    Set rValue = .Cells.Find(What:="*", _
            After:=Range("A1"), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRomySheet, _
    On Error GoTo 0
    If cFormula Is Nothing Then
        lCol = 0
        lCol = cFormula.Column
    End If
    If Not cValue Is Nothing Then
        lCol = Application.WorksheetFunction. _
                Max(lCol, cValue.Column)
    End If
    If rFormula Is Nothing Then
        lRow = 0
        lRow = rFormula.Row
    End If
    If Not rValue Is Nothing Then
        lRow = Application.WorksheetFunction. _
                Max(lRow, rValue.Row)
    End If
    For Each myShape In .Shapes
        j = 0
        k = 0
        On Error Resume Next
        j = myShape.TopLeftCell.Row
        k = myShape.TopLeftCell.Column
        On Error GoTo 0
        If j > 0 And k > 0 Then
            Do Until .Cells(j, k).Top > _
                    myShape.Top + myShape.Height
                j = j + 1
            If j > lRow Then
                lRow = j
            End If
            Do Until .Cells(j, k).Left > _
                    myShape.Left + myShape.Width
                k = k + 1
            If k > lCol Then
                lCol = k
            End If
        End If
    .Range(Cells(1, lCol + 1).Address & ":IV65536").Delete
    .Range(Cells(lRow + 1, 1).Address & ":IV65536").Delete
   End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Leave a Comment

Remove Unprintable Character & Spaces from cells

There are times when you get text that has unprintable characters in them or extra spaces, typically from downloaded data or perhaps extracted data. This routine cleans your selected cells of thes characters.
-Selva V Pasupathy, HSBC Global Resources, Hyderabad.

Original Source: Ivan F Moala’s Site
Option Explicit

Sub Clean_Trim()
‘// From Help Files:
‘// CLEAN > Removes all nonprintable characters from text.
‘// Use CLEAN on text imported from other applications that
‘// contains characters that may not print with your
‘// operating system.
‘// For example, you can use CLEAN to remove some low-level
‘// computer code that is frequently at the beginning and end
‘// of data files and cannot be printed.

‘// TRIM > Removes all spaces from text except for single
‘// spaces between words. Use TRIM on text that you have
‘// received from another application that may
‘// have irregular spacing.

Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox “No data to clean and Trim!”: Exit Sub

For Each oCell In CleanTrimRg
oCell = Func.Clean(Func.Trim(oCell))

End Sub

Leave a Comment

Add an Image to Cell Comments

Add a bit of spice to your comments look by adding an Image file.
See results below.
Supported image file formats = .bmp; .gif; .tif; .jpg
-Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
(Original Source: by Ivan Moala’s Site: )

Option Explicit

Const ImgFileFormat = "Image Files (*.bmp;*.gif;*.tif;*.jpg;*.jpeg)," & _

Sub AddPicturesToComments()
Dim HasCom
Dim Pict As String
Dim Ans As Integer

Set HasCom = ActiveCell.Comment
If Not HasCom Is Nothing Then ActiveCell.Comment.Delete
Set HasCom = Nothing

Pict = Application.GetOpenFilename(ImgFileFormat)
'Note you can load in, almost any file format
If Pict = "False" Then End

Ans = MsgBox("Open : " & Pict, vbYesNo + vbExclamation, "Use this Picture?")
If Ans = vbNo Then GoTo GetPict

With ActiveCell
    .Comment.Visible = False
    .Comment.Shape.Fill.Transparency = 0#
    .Comment.Shape.Fill.UserPicture Pict
End With

End Sub

Leave a Comment

Camera Function in VBA

The following macro asks you to give a range to be copied, it then copies it as a picture, and prompts user to specify the area to paste it. The lasta part of the macro makes the “photo” dynamic, as if it is done manually with the camera tool. The PasteSpecial pastes the picture, and the picture remains selected. Setting the Formula property for the selection (the picture) results in getting the “photo” dynamic.
-Selva V Pasupathy, HSBC Global Resourcing, Hyderabad.

Option Explicit
Private i, j, k As Integer
Private strMyRange As String
Private sTitle As String
Private myRange As Range
Private NwRange As Range

Sub DoCamera()
Application.ScreenUpdating = True
strMyRange = “Select the range you would like to capture.”
sTitle = “User Input Required”
On Error Resume Next

Set myRange = Application.InputBox(Prompt:=strMyRange, _
Title:=sTitle, Default:=ActiveCell.Address, Type:=8)
If myRange Is Nothing Then End

On Error GoTo 0
strMyRange = “Select the range on which you would like to paste.”
sTitle = “User Input Required”
On Error Resume Next
Set NwRange = Application.InputBox(Prompt:=strMyRange, _
Title:=sTitle, Default:=ActiveCell.Address, Type:=8)

If NwRange Is Nothing Then End
On Error GoTo 0
Selection.Formula = myRange.Address
End Sub

Leave a Comment