Showing posts with label VBA-Macros. Show all posts
Showing posts with label VBA-Macros. Show all posts

Monday, 30 April 2018

Disable Pop up in Excel - Parts of your document may include personal information that can't be removed by the Document Inspector

Hi,

Are you facing the below pop up when you try to save an Excel file?

Message : Be Careful! Parts of your document may include personal information that can't be removed by the Document Inspector



You can disable this pop by doing below change:

Excel Options->Trust Centre->Trust Centre Setting at Right Middle->Privacy Options - >A checkbox "Remove Personal ..." at middle.Uncheck it.

or

by VBA

Thisworkbook.RemovePersonalInformation=False

Tuesday, 17 April 2018

How to get full name of windows user VBA | VBA code to get windows user full name



How to get full name of windows user VBA :

Function GetWinUsers_Fullname()
Dim objSystemInfo As Object
Dim strLDAPName As String
 
Set objSystemInfo = CreateObject("ADSystemInfo")
strLDAPName = objSystemInfo.UserName
Set objSystemInfo = Nothing

GetWinUsers_Fullname = GetUserName(strLDAPName)
End Function

Function GetUserName(strLDAPName)
  Dim objUserName As Object
  Dim strFullName As String
  Dim arrLDAPName() As String
  Dim intIndex As Integer
 
  On Error Resume Next
  strFullName = ""
  Set objUserName = GetObject("LDAP://" & strLDAPName)
  If Err.Number = 0 Then
    strFullName = objUserName.Get("givenName") & Chr(32) & objUserName.Get("sn")
  End If
  If Err.Number <> 0 Then
    arrLDAPName = Split(strLDAPName, ",")
    For intIndex = 0 To UBound(arrLDAPName)
      If UCase(Left(arrLDAPName(intIndex), 3)) = "CN=" Then
        strFullName = Trim(Mid(arrLDAPName(intIndex), 4))
      End If
    Next
  End If
  Set objUserName = Nothing
 
  GetUserName = strFullName
 
End Function

Saturday, 14 April 2018

How to send an email from another mailbox in Excel VBA | Use on behalf in VBA to send mail from another account | VBA email automation


If you wish to send a mail from an another email account, you can add the account name to SentOnBehalfOfName property of your mail object.

Note : Sometimes, the mailboxes takes time to respond, so I added a while loop to make sure that its' been added. You may add a counter or time variable to exit from the while loop if your mailbox is taking too long to respond, so you will not end up in a infinite loop.

Example :


Dim OutMail As Object, Cell As Range, OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = "abx@gmail.com"
        .CC = "abx@gmail.com"
        '.BCC = "abx@gmail.com"
        .Subject = "subject"
        'check errors & timing
        On Error Resume Next
        .SentOnBehalfOfName = "Sendfrom@gmail.com"
        Do While Err.Number <> 0
            Err.Clear
            .SentOnBehalfOfName = MailBody.Range("B3")
        Loop
        On Error GoTo 0
           
        .HTMLBody =  "this is body"
        .Save

    End With


Sunday, 8 April 2018

How to find next working day in Excel VBA macros


VBA doesn't have an inbuilt function to find the next working day / previous working day.

You can use below code to find the next working day in VBA.


Function GetNextWorkingDay(InptDate As Date)
GetNextWorkingDay = InptDate + IIf(Weekday(InptDate) > 5, 9 - Weekday(InptDate), 1)
End Function


Note : Alternatively You can use the Workday function in Excel if you are using a higher version of Excel as explained in below link. But if you are creating a product which is going to be used in multiple versions of Excel, you should use above code.

http://macromatician.blogspot.in/2018/03/workday-formula-using-excel-vba-how-to.html

Wednesday, 4 April 2018

Guidlines of structured programming in RPA



The base composition of any type of structured programming includes three fundamental elements.

The first is sequencing, which has to do with the logical sequence provided by the statements in the program. As they are executed, each step in the sequence must logically progress to the next without producing any undesirable effects.

The second element is selection. This step allows the selection of any number of statements to execute in the program. These statements will contain certain keywords that can identify the sequence as a logically ordered executable. These terms are “if," “then," “endif," or “switch."

A third element is repetition. As a program proceeds, a select statementcontinues to be active until the program gets to the point where some other actionneeds to take place. The keywords include "repeat," “for," or “do…until." The repetition factor dictates instructions to the program about how long to continue the operation before requesting further instructions.

Depending on the purpose and function of the program, the exact nature of structured programming will vary. For example, most forms of structured programming will have a single entry point but may have more than one exit point. In another form, called modular programming, the creation of modules within the overall structure of the program will interact with one another, depending on the type of code that is executed.


Rule 1: Follow the Style Guide
Every programming language has a style guide that tells you in great detail how to indent your code, where to put spaces and braces, how to name stuff, how to comment—all the good and bad practices. For example, the style guide tells you the 12 mistakes lurking in this code snippet:
for(i=0 ;i<10 ;i++)

Read the guide carefully, learn the basics by heart, look up corner cases, apply the rules religiously, and your programs will be better than those written by the majority of university graduates.

Many organizations customize style guides to reflect the organization's specific practices. For instance, Google has developed and released style guides for more than a dozen languages. These guides are well thought out, so check them out if you're looking for help programming for Google. Guides even include editor settings to help you apply a programming style, and custom tools can verify that your code adheres to that style. Use these tools.

Rule 2: Create Descriptive Names
Constrained by slow, clunky teletypes, programmers in the past used to contract the names of their variables and routines to save time, keystrokes, ink, and paper. This culture persists in some communities, in the name of backward compatibility; consider C's tongue-twisting wcscspn (wide character string complement span) function. But there's no excuse for this practice in modern code.
Use long descriptive names, like complementSpanLength, to help yourself, now and in the future, as well as your colleagues to understand what the code does. The only exception to this rule concerns the few key variables used within a method's body, such as a loop index, a parameter, an intermediate result, or a return value.

Even more importantly, think long and hard before you name something. Is the name accurate? Did you mean highestPrice, rather than bestPrice? Is the name specific enough to avoid taking more than its fair share of semantic space? Should you name your method getBestPrice, rather than getBest? Does its form match that of other similar names? If you have a method ReadEventLog, you shouldn't name another NetErrorLogRead. If you're naming a function, does the name describe what the function returns?

Finally, there are some easy naming rules. Class and type names should be nouns. Methods names should contain a verb. In particular, if a method returns a value indicating whether something holds true for an object, the method name should start with is. Other methods that return an object's property should start with get, and those that set a property should start with set.

Rule 3: Comment and Document
Start every routine you write (function or method) with a comment outlining what the routine does, its parameters, and what it returns, as well as possible errors and exceptions. Summarize in a comment the role of each file and class, the contents of each class field, and the major steps of complex code. Write the comments as you develop the code; if you think you'll add them later, you're kidding yourself.

In addition, ensure that your code as a whole (for example, an application or library) comes with at least a guide explaining what it does; indicating its dependencies; and providing instructions on building, testing, installation, and use. This document should be short and sweet; a single README file is often enough.

Rule 4: Don't Repeat Yourself
Never copy-and-paste code. Instead, abstract the common parts into a routine or class (or macro, if you must), and use it with appropriate parameters. More broadly, avoid duplicate instances of similar data or code. Keep a definitive version in one place, and let that version drive all other uses. Following are some good examples of this practice:

Creation of API reference guides from comments, using Javadoc or Doxygen
Automatic detection of unit tests through an annotation or a naming convention
Generation of both PDF and HTML documentation from a single markup source
Derivation of object classes from a database schema (or the opposite)

Rule 5: Check for Errors and Respond to Them
Routines can return with an error indication, or they can raise an exception. Deal with it. Don't assume that a disk will never fill up, your configuration file will always be there, your application will run with the required permissions, memory-allocation requests will always succeed, or that a connection will never time out. Yes, good error-handling is hard to write, and it makes the code longer and less readable. But ignoring errors and exceptions simply sweeps the problem under the carpet, where an unsuspecting end user will inevitably find it one day.

Rule 6: Split Your Code into Short, Focused Units
Every method, function, or logical code block should fit on a reasonably-sized screen window (25–50 lines long). If it's longer, split it into shorter pieces. An exception can be made for simple repetitive code sequences. However, in such cases, consider whether you could drive that code through a data table. Even within a routine, divide long code sequences into blocks whose function you can describe with a comment at the beginning of each block.

Furthermore, each class, module, file, or process should concern one single thing. If a code unit undertakes diverse responsibilities, split it accordingly.

Rule 7: Use Framework APIs and Third-Party Libraries
Learn what functionality is available through an API in your programming framework, and also what's commonly available through mature, widely adopted third-party libraries. Libraries supported by your system's package manager are often a good bet. Use that code, resisting the temptation to reinvent the wheel (in a dysfunctional square shape).

Rule 8: Don't Overdesign
Keep your design focused on today's needs. Your code can be general to accommodate future evolution, but only if that doesn't make it more complex. Don't create parameterized classes, factory methods, deep inheritance hierarchies, and arcane interfaces to solve problems that don't yet exist—you can't guess what tomorrow will bring. On the other hand, when the code's structure no longer fits the task at hand, don't shy away from refactoring it to a more appropriate design.

Rule 9: Be Consistent
Do similar things in similar ways. If you're developing a routine whose functionality resembles that of an existing routine, use a similar name, the same parameter order, and a comparable structure for the code body. The same rule applies to classes: Give the new class similar fields and methods, make it adhere to the same interface, and match any new names with those already used in similar classes. Make the order and number of case statements or if else blocks follow the corresponding definition in the specifications you're using. Keep unrelated items in alphabetical or numerical order.

Your code should adopt the conventions of the framework in which you're programming. For instance, it's common practice to represent ranges half-open: closed (inclusive) on the left (the range's beginning), but open (exclusive) on the right (the end). If there's no a convention for a particular choice, establish one and follow it religiously.

Rule 10: Avoid Security Pitfalls
Modern code rarely works in isolation. Therefore it will inevitably risk becoming the target of malicious attacks. They don't have to come from the Internet; the attack vector could be data fed into your application. Depending on your programming language and application domain, you might have to worry about buffer overflows, cross-site scripting, SQL injection, and similar problems. Learn what these problems are, and avoid them in your code. It's not difficult.

Rule 11: Use Efficient Data Structures and Algorithms
Simple code is often more maintainable than equivalent code hand-tuned for efficiency. Fortunately, you can combine maintainability with efficiency by utilizing the data structures and algorithms provided by your programming framework. Use maps, sets, vectors, and the algorithms that work on them, and your code will be clearer, more scalable, faster, and memory-frugal. For example, if you keep a thousand values in an ordered set, a set intersection will find the values common with another set in a similar number of operations, rather than performing a million comparisons.

Rule 12: Include Unit Tests
The complexity of modern software makes it expensive to deploy a system and difficult to test it as a black box. A more productive approach is to accompany every small part of your code with tests that verify its correct function. This approach simplifies debugging by allowing you to catch errors early, close to their source. Unit testing is indispensable when you program with dynamically typed languages such as Python and JavaScript, because they'll only catch at runtime any errors that that a statically typed language such as Java, C#, or C++ would catch at compile time. Unit testing also allows you to refactor the code with confidence. You can use an xUnit framework to simplify writing these tests and automate running them.

Rule 13: Keep Your Code Portable
Unless you have some compelling reason, avoid using functionality that's available only on a specific platform or framework. Don't assume that particular data types (such as integers, pointers, and time) are of a given width (for example, 32 bits), because this differs between various platforms. Store the program's messages separately from the code, and don't hardcode cultural conventions such as a decimal separator or date format. Conventions need to change when your code runs in other countries around the world, so keep this adaptation as painless as possible.

Rule 14: Make Your Code Buildable
A single command should build your code into a form that's ready for distribution. The command should allow you to perform fast incremental builds and run the required tests. To achieve this goal, use a build automation tool like Make, Apache Maven, or Ant. Ideally, you should set up a continuous integration system that will check, build, and test your code every time you make a change.

Rule 15: Put Everything Under Version Control
All elements of your system—code, documentation, tool sources, build scripts, test data—should be under version control. Git and GitHub make this task cheap and hassle-free, but many other similarly powerful tools and services are available. You should be able to build and test your program on a properly configured system, simply by checking out the code from the repository.

Rule 16: Do not use Goto, Break, etc to control the program flow
Your code should have a sequence and a flow and it should end at the bottom of the code. Use If, For, While, etc to control the program flow. This makes the code easier to read and follow for others.

Sunday, 1 April 2018

Saturday, 31 March 2018

VBA to paste text from clipboard | VBA program to paste text from clipboard

You can use below code to paste data from clipboard.


Sub PasteFromClipboard(b As Boolean)
    Dim clipboard As MSForms.DataObject
    Dim str1 As String

    Set clipboard = New MSForms.DataObject
   
    clipboard.GetFromClipboard
    str1 = clipboard.GetText
End Sub

Thursday, 29 March 2018

VBA to copy text to clipboard | VBA program to copy text to clipboard

You can use below code to copy a text to clipboard.

Sub CopyToClipboard(strCopy As String)
On Error Resume Next
    Dim clipboard As MSForms.DataObject
    Dim strSample As String

    Set clipboard = New MSForms.DataObject
    clipboard.SetText strCopy
    clipboard.PutInClipboard
On Error GoTo 0
End Sub

Wednesday, 28 March 2018

How to get a list of all the sheets in an Excel file in Excel VBA Macros | VBA code to get list of sheet names


How to get a list of all the sheets in an Excel file in Excel VBA Macros | VBA code to get list of sheet names :

Sub GetsheetNames()
Dim sht As Worksheet
'for backup
For Each sht In ActiveWorkbook.Sheets
    Selection.Value = sht.Name
    Selection.Offset(1, 0).Select
Next sht

End Sub

Thursday, 22 March 2018

Add attachments to an email object Excel VBA | Email automation | Insert Attachments VBA


Sometimes when you work on bulk emailing using VBA, you have to insert attachments to the email.

In such cases, you can use the Add method under mail item to insert attachment to the email object.

Please refer the code below.


Sub AddAttachment() Dim MailItem As Outlook.MailItem Dim MailAttachments As Outlook.Attachments Set MailItem = Application.CreateItem(olMailItem) mailItem.Attachments.Add "C:\Documents\abcd.xlsx" mailItem.Display End Sub

Ask user to select a file during the macro VBA | How to get pop up window to select a file in VBA


Ask user to select a file during the macro VBA :


Function GetAFileName(StrPath As String) As String
Dim Fldr As FileDialog
Set Fldr = Application.FileDialog(msoFileDialogFilePicker)
With Fldr
    .Title = "Select a File"
    .AllowMultiSelect = False
    .InitialFileName = StrPath
    If .Show <> -1 Then MsgBox "You Should Select a File", , "Warning": GetAFileName (StrPath)
    GetAFileName = .SelectedItems(1)
End With
Set Fldr = Nothing
End Function

Wednesday, 21 March 2018

How to block users opening an Excel file in VBA | Secure Excel file with VBA


If you want to secure your file to make sure that only you can open it, then you can add the below code in Workbook_Open function in your Excel file.

Note : Make sure you enter some thoughtful condition to make sure that only you open the file referring the code below.


Private Sub Workbook_Open()
    If Environ("username") <> "mm" Then ThisWorkbook.Close False
End Sub

Tuesday, 20 March 2018

Ask user to select a folder during the macro VBA | How to get pop up window to select a folder in VBA


Ask user to select a folder during the macro VBA | How to get pop up window to select a folder in VBA


Function GetFolder(StrPath As String) As String
Dim Fldr As FileDialog
Set Fldr = Application.FileDialog(msoFileDialogFolderPicker)
With Fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = StrPath
    If .Show <> -1 Then
    MsgBox "You Should select a Folder!", , "Warning": GetFolder (StrPath)
    End If
    GetFolder = .SelectedItems(1)
End With
Set Fldr = Nothing
End Function

Tuesday, 13 March 2018

Workday formula using Excel VBA / How to find next working day in Excel VBA


VBA doesn't have an inbuilt function to find the next working day / previous working day.

You can use below code if you are using a higher version of Excel.

Function FindWorkDay(InpDate As Date, DaysCount As Long)
FindWorkDay = WorksheetFunction.WorkDay(InpDate, DaysCount)
End Function



Sunday, 4 March 2018

How to get computer name using VBA Excel MS Access/ VBA code to find the computer's name





Sometimes you might require to find user's computer name various scenarios like to track which computer has accessed the file or database, etc.

We can find the computer's name using below VBA code.

Function FindComputerName() as String
      FindComputerName = environ(6)
End Function


Note: Copy paste above function to your module, You can use it as an Excel function or call from a sub procedure.

Sunday, 25 February 2018

How to close an Excel file without saving in VBA | VBA workbook close without saving


Sometime you will have to disable the SAVE AS pop up on your Excel files to stop users asking to save the file.

You can use below code to do that.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Saved = True
End Sub

Wednesday, 14 February 2018

How to delete all named ranges in Excel | Remove named range in Excel VBA


Sometimes you have to remove the named ranges from your Excel file, on such scenarios like copy pasted from another workbook or project scope is changed , etc.

It's a tedious task to remove the range names manually.

You can use below code to easily remove all named ranges in Excel file.


Sub RemoveNamedRanges()
    nm As Name 
    On Error Resume Next
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next
    On Error Goto 0
End Sub 

Sunday, 14 January 2018

How to disable saving of an Excel file | Stop user from saving an Excel file

If you wish to stop a user from saving a file, you can use below in the workbook code.

Note : Make sure you put some thoughtful conditions before the 'Cancel = True' , so only you or dedicated people are allowed to save the file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Environ("username") <> "mm" Then Cancel = True
End Sub

Wednesday, 10 January 2018

How to add a new sheet to a Excel workbook using VBA code


We can insert a new worksheet to an Excel file using below code.

ThisWorkbook.Sheets.Add

Syntax : Workbook.Add ( Before, After, Count, Type) As Object

You can mention the position of the new worksheet by playing with Before and After.

If you wish to add multiple sheets, then you can mention the number in Count

Type is required if you want to add a chart sheet.


Sunday, 31 December 2017

How to validate a textbox while entering data in VBA | Data validation in VBA user forms

You can add below code on the event of textbox_keypress, textbox_enter, etc to validate the entry while user enters the data into the textbox.


Function ValidateTextboxForNumbersOnly(ByRef tb As MSForms.TextBox)
'Purpose : To validate and clear data in text box if value is not numeric
    If IsNumeric(tb.Value) = False Then tb.Value = ""
End Function