Why Excel Macros Important to learn? | Macro सिखना क्यू जरुरी हैं | VBA | Excel Samadhaan

VBA Macros समय की बचत करते हैं, क्योंकि वे दोहराए जाने वाले कार्यों को स्वचालित करते हैं। यह visual basic for applications प्रोग्रामिंग कोड का एक टुकड़ा है जो एक्सेल वातावरण में चलता है, और मैक्रोज़ प्रोग्राम करने के लिए या कोड का आनंद लेने के लिये आपको कोडर ही होने की आवश्यकता नहीं है | लेकिन, मैक्रो में अपने हिसाब से changes करने के लिए आपको VBA की बेसिक knowledge होना बहुत जरुरी है।

यदि आप उत्सुक हैं कि मैक्रोज़ क्या हैं या वास्तव में उन्हें कैसे बनाया जाए, तो कोई बात नहीं - हम आपको पूरी प्रक्रिया के बारे में बताएंगे।

एक्सेल में कुछ काम होते हैं जो हम रोजाणा करते हैं। क्या यह बेहतर नहीं होगा कि एक बटन दबाने का कोई जादुई तरीका हो और हमारे रिपोर्ट के सभी नियमित कार्य चुटकियो मे हो जाएं? 

मैं आपको हाँ कहते हुए सुन रहा हूँ। 

एक्सेल में मैक्रो आपके इसी काम को हासिल करने में हमारी मदद करता है। एक नॉर्मल MIS की भाषा में कहू तो, एक्सेल में मॅक्रो आपके नियमित काम को रिकॉर्डिंग के रूप में लिख लेता है जिसे आप एक बटन का उपयोग करके फिर से चला सकते हैं।

Example के तौर पर कहू तो, आप आपके किसीं डेटा को एक्सेल में इम्पोर्ट करते हैं और उसे अपने रिपोर्ट के जरूरतो के अनुसार फॉरमॅट करते हैं। अगले दिन आपको वही सेम काम करने की आवश्यकता होगी। रोज रोज सेम काम, यह जल्द ही बोअरिंग और थकाऊ हो जाएगा। मैक्रोज़ ऐसे नियमित कार्यों को स्वचालित करके ऐसी समस्याओं का समाधान करते हैं। आप के इसी काम को रिकॉर्ड करने के लिए आप मैक्रो का उपयोग कर सकते हैं| जैसे की, डेटा इम्पोर्ट करना और अपनी रिपोर्टिंग की आवश्यकताओं को पूरा करने के लिए इसे formating करना|

 एक्सेल में मॅक्रोस एक ऐसा benefit हैं जो स्वचालित रूप से आपके लिए कोड को उत्पन्न करती हैं, आपके हर स्टेप्स को रेकॉर्ड कर कर के, Source कोड दे देती है! 

हम छोटे छोटे स्टेप्स से लेकर कॉम्प्लेक्स रिपोर्टिंग प्रक्रियाओं तक कई फाइलों के साथ सब कुछ Automated करने के लिए VBA मैक्रोज़ का उपयोग कर सकते हैं| 

Highlight Cells with Misspelled Words

Excel doesn’t have a spell check as it has in Word or PowerPoint. While you can run the spell check by hitting the F7 key, there is no visual cue when there is a spelling mistake.

Use this code to instantly highlight all the cells that have a spelling mistake in it.

'This code will highlight the cells that have misspelled words
Sub HighlightMisspelledCells()
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
If Not Application.CheckSpelling(word:=cl.Text) Then
cl.Interior.Color = vbRed
End If
Next cl
End Sub

Note: that the cells that are highlighted are those that have text that Excel considers as a spelling error. In many cases, it would also highlight names or brand terms that it doesn’t understand.

How to Get Only the Numeric Part from a String in Excel

If you want to extract only the numeric part or only the text part from a string, you can create a custom function in VBA.

You can then use this VBA function in the worksheet (just like regular Excel functions) and it will extract only the numeric or text part from the string.

Something as shown below:

Below is the VBA code that will create a function to extract numeric part from a string:

'This VBA code will create a function to get the numeric part from a string
Function GetNumeric(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumeric = Result
End Function

You need place in code in a module, and then you can use the function =GetNumeric in the worksheet.

This function will take only one argument, which is the cell reference of the cell from which you want to get the numeric part.

Similarly, below is the function that will get you only the text part from a string in Excel:

'This VBA code will create a function to get the text part from a string
Function GetText(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetText = Result
End Function

So these are some of the useful Excel macro codes that you can use in your day-to-day work to automate tasks and be a lot more productive.

यदि आप एक्सेल मैक्रोज़ को सेव्ह करना चाहते हैं, तो आपको अपनी वर्कबुक को Macro-Enable टाईप में सेव करना होगा (.xlsm)

Create New Toolbar VBA

 

Option Explicit

Sub Auto_Open_ToolBar()

    Dim oToolbar As CommandBar

'    Dim oButton As CommandBarButton

    Dim oButton1 As CommandBarButton

    Dim oButton2 As CommandBarButton

    Dim MyToolbar As String

    ' Give the toolbar a name

    MyToolbar = "Macro Control"

    On Error Resume Next

    ' so that it doesn't stop on the next line if the toolbar's already there

    ' Create the toolbar; Word will error if it already exists

    Set oToolbar = CommandBars.Add(Name:=MyToolbar, _

        Position:=msoBarFloating, Temporary:=True)

    

    If Err.Number <> 0 Then

          ' The toolbar's already there, so we have nothing to do

          Exit Sub

    End If

    On Error GoTo ErrorHandler

    ' Now add a button to the new toolbar

'    Set oButton = oToolbar.Controls.Add(Type:=msoControlButton)

'

'    ' And set some of the button's properties

'

'    With oButton

'

'         .DescriptionText = "Add The Source data for Mail Merge"

'          'Tooltip text when mouse if placed over button

'

'         .Caption = "Add The Source data for Mail Merge"

'         'Text if Text in Icon is chosen

'

'         .OnAction = "Pick_The_Source_File"

'          'Runs the Sub Pick_The_Source_File() code when clicked

'

'         .Style = msoButtonIconAndCaptionBelow

'          ' Button displays as icon, not text or both

'

'         .FaceId = 8

'          ' chooses icon #7 from the available Office icons

'

'    End With

    

    Set oButton1 = oToolbar.Controls.Add(Type:=msoControlButton)

    With oButton1

         .DescriptionText = "Run Macro to Export to PDF"

          'Tooltip text when mouse if placed over button

         .Caption = "Run Macro to Export to PDF"

         'Text if Text in Icon is chosen

         .OnAction = "Run_Confirmation"

          'Runs the Sub Run_Confirmation() code when clicked

         .Style = msoButtonIconAndCaptionBelow

          ' Button displays as icon, not text or both

         .FaceId = 7

          ' chooses icon #7 from the available Office icons

    End With

    

    

    Set oButton2 = oToolbar.Controls.Add(Type:=msoControlButton)

    With oButton2

         .DescriptionText = "Run Macro to send Mail"

          'Tooltip text when mouse if placed over button

         .Caption = "Run Macro to Send Mail"

         'Text if Text in Icon is chosen

         .OnAction = "Run_Confirmation_Outlook"

          'Runs the Sub Run_Confirmation_Outlook() code when clicked

         .Style = msoButtonIconAndCaptionBelow

          ' Button displays as icon, not text or both

         .FaceId = 6

          ' chooses icon #6 from the available Office icons

    End With

    ' Repeat the above for as many more buttons as you need to add

    ' Be sure to change the .OnAction property at least for each new button

    ' You can set the toolbar position and visibility here if you like

    ' By default, it'll be visible when created. Position will be ignored in WORD 2007 and later

    oToolbar.Top = 150

    oToolbar.Left = 150

    oToolbar.Visible = True

    

NormalExit:

    Exit Sub   ' so it doesn't go on to run the errorhandler code

ErrorHandler:

     'Just in case there is an error

     Msgbox Err.Number & vbCrLf & Err.Description

     Resume NormalExit:

End Sub

Sub Run_Confirmation()

' This code will run when you click Run_Confirmation () added above

' Add a similar subroutine for each additional button you create on the toolbar

    Dim MsgAns As String

    Dim MsgAns1 As String

    

    MsgAns = Msgbox("Do you want to Run macro?", vbQuestion + vbYesNo)

        If MsgAns = vbYes Then

            Call ExporttoPDF

        End If

        

    MsgAns1 = Msgbox("Do you want to send Mails for recently generated PDF?", vbQuestion + vbYesNo)

    

        If MsgAns1 = vbYes Then

            Call send_oulook_mail

        End If

    

End Sub

Sub Run_Confirmation_Outlook()

' This code will run when you click Run_Confirmation () added above

' Add a similar subroutine for each additional button you create on the toolbar

    Dim MsgAns As String

     

    MsgAns = Msgbox("Do you want to Run macro?", vbQuestion + vbYesNo + vbCritical)

        If MsgAns = vbYes Then

            Call send_oulook_mail

        End If

        

End Sub

Sub Delete_MyToolbar()

' Delete the unwanted toolbar that is attached to this workbook.

    Dim cmdbar As CommandBar

    For Each cmdbar In Application.CommandBars

        If cmdbar.Name = "Macro Control" Then

            cmdbar.Delete

        End If

    Next

    

End Sub

add below code in the new Class module

Option Explicit

Private Sub Document_Close()

    

    Call Delete_MyToolbar

    

End Sub

Private Sub Document_Open()

    Call Auto_Open_ToolBar

   

End Sub

Mail Merge - Create PDF files and Send outlook Mail with VBA Code

Option Explicit

Sub ExporttoPDF()

    

    ' Variable Declaration

    Dim Counter As Long

    Dim LastCounter As Long

    Dim Starttime As Date

    Dim endtime As Date

    Dim Duration As Date

    Dim MsgAns As String

    

    ' Getting Start Time of Macro

    Starttime = Now

    

    'Making Sure Preview button is pressed on mailing tab

    Application.ScreenUpdating = True

    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = Not wdToggle

    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle

    

    ' Set Active Record to First Record

    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord

    

    ' Loop to get Last Record number of the data

    LastCounter = ActiveDocument.MailMerge.DataSource.RecordCount

    For Counter = 1 To LastCounter

    '   Export to PDF

    

'        ActiveDocument.SaveAs2 ThisDocument.Path & "\Attachment_Base\" _

'            & ActiveDocument.MailMerge.DataSource.DataFields(1).Value _

'            , wdFormatPDF

        

        ActiveDocument.ExportAsFixedFormat _

            OutputFileName:=ThisDocument.Path & "\Attachment_Base\" _

            & ActiveDocument.MailMerge.DataSource.DataFields(1).Value _

            , ExportFormat:=wdExportFormatPDF _

            , OpenAfterExport:=False

            

        ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord

        ' Selecting next Record for next PDF creation

        

    Next Counter

    'turning off Preview button on mailing tab

    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle

    

    ThisDocument.Save

    

    ' Getting End Time and calculating the Duration Taken by macro

    Application.ScreenUpdating = False

    endtime = Now

    Duration = endtime - Starttime

    

    ' End of Macro Notification

    Msgbox "Time taken to run this macro " & Format(Duration, "h:m:s") _

            & vbNewLine & "End of Macro." & vbNewLine _

            & "Macro have exported to PDF" _

            , vbInformation, "End of Macro"

             

End Sub

Sub send_oulook_mail()

' Early Binding

'Step 1: declaring the variable

Dim outapp As Outlook.Application

Dim outmail As Variant

Dim LISTOFRANGE As Long

Dim filename As String

Dim Starttime As Date

Dim endtime As Date

Dim Duration As Date

Dim Counter As Long

    Starttime = Now

'Step 2: Loop the Macro

    Application.ScreenUpdating = False

    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = Not wdToggle

    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle

    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord

    LISTOFRANGE = ActiveDocument.MailMerge.DataSource.RecordCount

    For Counter = 1 To LISTOFRANGE

'Step 3:Open the Outlook and new mail item

    Set outapp = New Outlook.Application

    Set outmail = outapp.CreateItem(olMailItem)

    

'Step 4: Populate the outlook fields

    With outmail

        .BodyFormat = olFormatHTML

        .To = ActiveDocument.MailMerge.DataSource.DataFields(21).Value

    '   .CC = "Some2@somewhere.com"

        .Subject = "Renewal Intimation- ManipalCigna Health Insurance Co Ltd - " & ActiveDocument.MailMerge.DataSource.DataFields(1).Value

        .HTMLBody = "Dear " & ActiveDocument.MailMerge.DataSource.DataFields(13).Value & ",<BR><br>" _

            & "Thank you for choosing ManipalCigna Health Insurance as your trusted health insurance partner. Your policy bearing no: " _

            & ActiveDocument.MailMerge.DataSource.DataFields(1).Value & " is due for renewal on " _

            & ActiveDocument.MailMerge.DataSource.DataFields(76).Value & ",<BR><br>" _

            & "To renew your policy now" & "<A hREF =" & ActiveDocument.MailMerge.DataSource.DataFields(77).Value & "> click here </a> <br>" _

            & "For any further queries related to renewal: <br><br>" & "Call 1800 102 4465 (toll-free) OR<br>" _

            & "Write to us at " & "<A href = Mailto:renewals@manipalcigna.com> renewals@manipalcigna.com</a> <br>" _

            & "Assuring you of our best services at all times.<br>" _

            & "We look forward to serve you further.<br>" _

            & "Yours sincerely,<br>" & "ManipalCigna Renewal Team<br>" _

            & "(Formerly Known as Cigna TTK Health Insurance Company Ltd)<br>"

         

            filename = ThisDocument.Path & "\Attachment_Base\" & ActiveDocument.MailMerge.DataSource.DataFields(1).Value & ".PDF"

        .Attachments.Add filename

        .Send

    End With

    

'Step 5: End Loop the Macro

    Set outmail = Nothing

        

    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord

    

    Next Counter

    

'Step 6: Informing User of end of macro

    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = Not wdToggle

    Application.ScreenUpdating = True

    endtime = Now

    Duration = endtime - Starttime

    

    Msgbox "Time taken to run this macro is " & Format(Duration, "h:m:s") _

        & vbNewLine & "Mail Sent. If mail not received contact Anil Devre."

        

End Sub

SQL (Structured Query Language)

 --- to Add New Column in Table---
ALTER TABLE dbo.[{Table Name}] add [{Column Name}] Nvarchar Null 

 --- to DELETE Column from data Table---
 ALTER TABLE [{Table Name}] DROP COLUMN [{Column Name}]; 

 --ALTER COLUMN data TYPE---
 ALTER TABLE [dbo].[{Table Name}] ALTER COLUMN [{Column Name}] Data-type Null 

 --- Rename Column name in data Table---

 exec sp_rename '[{Table Name}].[{Column Name}]','New Column Name','COLUMN' 

 ---****You can easily change the column name without recreating the table or losing ur records.
 ---Tools---
-----> Options 
--> Designers 
--> Table and database designers 
--> Uncheck 
-->Prevent saving changes that required table re-creation.


Cast([Receipt Date] as date)'Date' 

--Return '2021-12-17'

Cast([Receipt Date] as Time)'Time'

--Return '14:03:24' 

 ------- Add days into current date for future date or past Date 
Select DATEAdd(DAY,10, Cast(Getdate() as date)) 'Day 10 Added' 

Select DATEAdd(Month,-3, Cast(Getdate() as date)) 'Previous 3 Month' 

Select DATEAdd(YEAR,1, Cast(Getdate() as date)) 'Year 1 Added' 


 ----Day of Year(Number) 
Select DATEPART(DAYOFYEAR, Getdate())

 ----Day of Week 
Select DATEName(WEEKDAY, Getdate()) 
Select DATEName(DW, Getdate()) 

 -----date difference 
select DATEDIFF(day,'1983-03-14',Getdate()) 'Days Count' 

select DATEDIFF(yy,'1983-03-14',Getdate()) 'Year' 

 ---'Start date of last 4 Month' 
Select DATEAdd(Month,-4, cast(Getdate()-Day(Getdate())+1 as date)) 'Start date of last 4 Month' 

 ---'First date End Date of the Month' 
Select cast(Getdate()-Day(Getdate())+1 as date) 'Start date of the Month' 

Select EOMONTH(Getdate()) 'End date of the Month' 

 -----Replace Foumula 

Replace([Column Name],'{Removing Part of String}','{New Part to add in String}')