Remove Special Character from a String :


If You want to Remove Extra Character from you String , You want to Keep only Alphabets and Numeric Character You can use the Below Function : Public Function SheetName(Shname As String) As String Dim Cod As Integer Dim ShN As String             For i = 1 To Len(Shname)                 Cod = Asc(Mid(Shname, i, 1))…

Copy Data WithOut Decimal Value :


If you want to Copy your date with out Decimal Value You can use this Code : Option Base 1Sub CopyPasteWithOutDecimalPlace()Dim rngCopy As RangeDim rngPaste As RangeDim arr() As StringDim cell As RangeDim iVal As StringDim Rw As IntegerDim Col As IntegerDim cellvalue As Variant        Set rngCopy = Application.InputBox(“Select the Range to…

Return Range Value in Reverse Order :


if you want  Value of selected Range in Reverse Order : Sub Reverse()  Dim fRng As Range  Dim Couter As Integer  Dim cell As Range         Set fRng = Application.InputBox(“Please Choose A Range”, , , , , , , 8)     Set cell = Application.InputBox(“Please Choose a Cell for Output”, , , , ,…

Use of Range.Characters


If you want to highlight Character which meet with your Criteria , You can use the Below Code : Sub HighLight() Application.ScreenUpdating = False On Error Resume Next     Dim cell As Range     Dim rng As Range     Dim st As String     Dim loc As Integer Dim n as string     st =…

Looping in VBA


See the Same Result with Different Type of Loops  Sub FLoop() Dim i As Integer  Range(“A1”).Value = “Example By For Loop”  Range(“A2”).Select     For i = 1 To 20                ActiveCell.Value = “i am In For Loop ” & i & ” time and My value is ” & i …

Get Wrong KeyWord On Another Sheets :


If You have Wrong KeyWord in Your data and You want to Get them on Another Worksheet  You can  Use this Code Public MyTest As BooleanPublic myWord As StringSub GetText()On Error Resume Next            Dim ws As Worksheet            Dim st As String         …

Showing Error Description In Status Bar :


Sometime we need to see the Program Execution Status : if you need to see what is Running inside the program you can use status bar to show Massage and Error Description : Try the Following Code : Sub showMsg()    On Error GoTo Err:    a = 1 / 0Err:    If Err.Number <> 0…

Generate All Possible Combination :


If you want to Generate All Possible Combination of 9 Digit (1 to 9) , You can use this : Sub ss() For One = 1 To 9     For Two = 1 To 9         For three = 1 To 9             For Four = 1 To 9                 For Five = 1 To…

Copy Data from Word to Excel By VBA


Sub CopyTextFromWord()     Dim wApp As Word.Application     Set wApp = New Word.Application     wApp.Visible = True     wApp.Activate     wApp.Documents.Open “YourFilePath\SampleWord.docx”     wApp.Selection.WholeStory     wApp.Selection.Copy     Range(“A1”).Activate     ActiveSheet.Paste End Sub

Filling Array With Loop :


if you have Data in A1:C5 Range and You Need to Store that in A Variable , You can take help with this Macro : Option Base 1 Sub StoreInArrya()     Dim Arr() As Variant     Dim rng As Range     Dim Rw As Integer     Dim Cl As Integer         Set rng =…

Debug.Print Statement And Immediate Window


Debug.Print Statement is Very useful Feature in VB which allow us to track the program .. as you can see the ScreenShot. In this Example , When Error will comes Execution will Go to Err: Label where it will check if  Number of Error Does not equal to Zero if Error Found  Debug.Print Statement Will…

Adding Controls On Worksheet in RunTime :


If you need to Add Controls On Worksheet in Run Time : Sub MakeComponentinRunTImeOnSpreadSheet()On Error Resume NextDim cb As OLEObjectn = 5For i = 1 To 10 Set cb = ActiveSheet.OLEObjects.Add(classtype:=”Forms.Checkbox.1″)                With cb                    .Top = n         …

Make Component In Run Time :


Sometime we need to Create UserForm Component at Run Time , We can do this By Following Codes : Sub AddInRunTime()‘On Error Resume NextDim Cb As Control    ThisWorkbook.VBProject.VBComponents.Add vbext_ct_MSForm        Set Cb = ThisWorkbook.VBProject.VBComponents(“UserForm1”).Designer.Controls.Add(“Forms.CommandButton.1”)        Cb.Left = 50        Cb.Top = 50        Cb.Caption = “Click…

Unzip FilesBy VBA


If you need to Unzip files From a Zip Folder you can use this Codes : Option Explicit Sub UnzipAFile()    Dim ShellApp As Object    Dim TargetFile    Dim ZipFolder ‘   Target file & temp dir    TargetFile = Application.GetOpenFilename _        (FileFilter:=”Zip Files (*.zip), *.zip”)    If TargetFile = False…

How to Zip File by VBA


If You want to Zip any Files Use this Code : Option Explicit Sub ZipFiles()    Dim ShellApp As Object    Dim FileNameZip As Variant    Dim FileNames As Variant    Dim i As Long, FileCount As Long ‘   Get the file names    FileNames = Application.GetOpenFilename _        (FileFilter:=”All Files (*.*),*.*”,…

Embaded Flash Object in Excel :


Follow the Process – When you need a flash File in Your Excel Sheet. 1). Insert the Shock wave Flash Object In your Excel Workhseet 2). Download or make Your  .Swf file and save it on Hard Drive.3). Right Click on ShockWave Flash Object and Click to Properties3) do Enable Movie Option  = True4) Give…

Vlookup By VBA


Sub VlookupByCodes()Dim ResultRange As RangeDim SearchRange As RangeDim ColNum As IntegerDim LookUpValue As RangeDim cellResult As RangeDim CellSearch As RangeDim i As Integer         Set ResultRange = Application.InputBox(“Select the Range Where You want Output”, , , , , , , 8)        Set LookUpValue = Application.InputBox(“Select the Range of searchable…

Power Point to Excel


Copy Power Point  Table Data into Excel. Sub PowerPointToExcel() ‘PowerPoint To ExcelDim ppt As PowerPoint.ApplicationSet ppt = New PowerPoint.Applicationppt.Visible = msoTrueDim sh As Worksheetppt.Presentations.Open “D:\ppt1.pptm” For j = 1 To ppt.ActivePresentation.Slides.Count               For i = 1 To ppt.ActivePresentation.Slides(j).Shapes.Count                ppt.ActivePresentation.Slides(i).Shapes(i).Copy         …

Access To Excel and Excel to Access to VBA


Access to Excel Data Retrieving : ‘SearchButtonPrivate Sub CommandButton1_Click()On Error GoTo Err: Application.StatusBar = “Connecting….”Dim sh As WorksheetDim cn As ADODB.Connection, rs As New ADODB.Recordset, r As Long    Set cn = New ADODB.Connection    cn.Open “Provider=Microsoft.ACE.OLEDB.12.0; ” & _    “Data Source=\\192.168.1.159\D\ReceiptDataBase.accdb;”       rs.Open “Sheet1”, cn, adOpenStatic, adLockOptimistic, adCmdTableSet sh = Sheets(“InterFace”) c = 0While…

Drive information :


Getting Drive Information In Excel : Private Declare Function GetDriveType32 Lib “kernel32” _    Alias “GetDriveTypeA” (ByVal nDrive As String) As Long Private Declare Function GetLogicalDriveStrings Lib “kernel32” _  Alias “GetLogicalDriveStringsA” (ByVal nBufferLength As Long, _  ByVal lpBuffer As String) As Long Private Declare Function GetDiskFreeSpaceEx Lib “kernel32” Alias “GetDiskFreeSpaceExA” _    (ByVal lpRootPathName As…

Get Key Status :


How To Know HOT Key Status … if they are pressed or Not?? ‘declare window API’s FunctionDeclare Function GetKeyState Lib “user32” (ByVal nVirtKey As Long) As Integer ‘   Constants for the keys of interest    Const VK_SHIFT As Integer = &H10    Const VK_CONTROL As Integer = &H11    Const VK_MENU As Integer =…

Binary Combination :


If You want to make binary Digit Combination . You can use this  Macro :- Sub MakeCobmination() Application.ScreenUpdating = False    Dim t As Double    Dim Digit As Double    Dim SS As Double    Dim j As Double    Combination = InputBox(“Enter Digit “)    SS = 2 ^ Combination       If…

Creating Codes By Codes


How to Create and manipulate Exist Code in VBA CodeModule:- Sub ss()Dim sh As WorksheetSet sh = ActiveSheetDim wb As WorkbookSet wb = Workbooks(“Book1”)        For i = 1 To sh.OLEObjects.Count                Set sh = ActiveSheet                wb.VBProject.VBComponents(sh.Name).CodeModule.AddFromString (“‘Private Sub DTPicker”…

How to Use ParamArray :


Use of ParamArray :- Param array is the collection of variant type variable : its allow to pass Voluntarily Variable to a Function, See the Below Mention Ratio Function, We can give any number of Arguments to this Function and can get Ratio: Function Ratio(ParamArray arr()) As String Dim Arr2(1) As Integer        …

Changing Date Format:


if you have date in below Mention Format : 24/12/1121/1/1130/9/11 and you want to Convert it on Regular Date format, You can use below mention Function For the Same =Redate() Function Redate(dat As String, div As String) ‘ Defining variables   Dim da As String       Dim mo As String       Dim fst As String …

Clear Clip Board :


Clear Clip Board By VBA:- Public Declare Function OpenClipboard Lib “user32” (ByVal hwnd As Long) As LongPublic Declare Function EmptyClipboard Lib “user32” () As LongPublic Declare Function CloseClipboard Lib “user32” () As Long Sub ClearClipboard()    OpenClipboard (0&)    EmptyClipboard    CloseClipboardEnd Sub

Creating HyperLinks of All Sheets :


If you want to make Quick Hyperlinks of All Sheets on a Single Sheets.. You can Try this : Sub CreatingHyperLink()For i = 1 To Sheets.CountActiveSheet.Hyperlinks.Add Anchor:=Range(“A” & i), Address:=””, SubAddress:= _        Sheets(i).Name & “!A1”, TextToDisplay:=Sheets(i).NameNextEnd Sub

Size of Excel Worksheet :-


How Big Is a Worksheet? It’s interesting to stop and think about the actual size of a worksheet. Do the arithmetic (16,384 ×1,048,576), and you’ll see that a worksheet has 17,179,869,184 cells. Remember that this is injust one worksheet. A single workbook can hold more than one worksheet. If you’re using a 1024 × 768 video mode…

Creating Pivot Table :-


Sub Create_Pivot_Table_From_Cache() Dim oPC As PivotCacheDim oPT As PivotTable Set oPC = ActiveWorkbook.PivotCaches.Create(xlDatabase, ActiveSheet.UsedRange)Sheets.Add.Name = “Pivot”oPT = oPC.CreatePivotTable(ActiveSheet.Range(“A1”), “Pivot1”, True) End Sub

Compiling Workbooks :-


if you have Many Workbooks with Data in Same Format and You want to Compile all them with in a Single workbook you can use below Mention Code :- Sub Compile()On Error GoTo Err_Clear:Application.ScreenUpdating = FalseApplication.DisplayAlerts = FalseDim Fso As New Scripting.FileSystemObjectDim Path As String Application.FileDialog(msoFileDialogFolderPicker).Title = “Select Folder to Pick Downloaded Bills”Application.FileDialog(msoFileDialogFolderPicker).ShowPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)…

Compiling Worksheets : –


Sub CompileWOrksheets()    Dim wksSheet As Worksheet    With ThisWorkbook    .Worksheets.Add.Name = “Compiled”    For Each wksSheet In .Worksheets        If wksSheet.Name <> “Compiled” Then            wksSheet.UsedRange.Copy .Worksheets(“Compiled”).Range(“A” & .Worksheets(“Compiled”).Range(“A” & Rows.Count).End(xlUp).Row + 1)        End If    Next wksSheet    End WithEnd Sub

Sheet Sorter


Sort your sheets in Alphabets Order.. Sub SheetSorter()Application.ScreenUpdating = FalseApplication.EnableCancelKey = xlDisabled Dim count As IntegerDim ws As WorksheetDim oldactive As ObjectDim ws1 As WorksheetDim sheetName1 As String If ActiveWorkbook.ProtectStructure = True Then        MsgBox “Workbook is protected “        Exit SubEnd IfSet oldactive = ActiveSheet If ActiveWorkbook Is Nothing Then…

Excel Error Values :


Explanation of Error Returned By An Excel Function :- #DIV/0! The formula is trying to divide by 0 (zero) (an operation that’s notallowed on this planet). This error also occurs when the formulaattempts to divide by a cell that is empty. #N/A The formula is referring (directly or indirectly) to a cell that uses theNA worksheet…

Converting Text Case in Excel VBA :-


If You want to Convert Text Case in Selection :- Sub ConvertProperCase()Application.ScreenUpdating = False    Dim Rng As Range    For Each Rng In Selection.Cells        If Rng.HasFormula = False Then             ‘Use this line for ProperCase text; change UCase to LCase for LowerCase text.         …

Saving Msgbox Answer in A Variable


Sub Result()ans = MsgBox(“Do you want to Continue”, vbYesNo)If ans = vbYes Then MsgBox “You have selected yes”, vbInformationEnd Sub