Thursday 14 June 2018

Story of a tomato seller | How to stay focused on crisis | Motivation


This video shows an interesting story of a tomato seller | How to stay focused on crisis.



A jobless man applied for the position of 'office boy' at Microsoft. The HR manager interviewed him, then gave him a test: clean the floor. The man passed the test with flying colors. "You are hired," HR manager informed the applicant, "give me your e-mail address, and I'll send you the application for employment, as well as the date you should report for work. The man replied " I don't have a computer, or an email!" "I'm sorry," said the HR manager. "If you don't have an email, that means you do not exist. And we cannot hire persons who do not exist." The man was very disappointed. He didn't know what to do. He only had $10 with him. Once that is spent, he won't have any money to buy any food. He went to the supermarket and bought a crate of tomatoes with his $10. He went from door to door and sold the tomatoes in less than two hours. He doubled his money. He repeated the operation three times, and returned home with $60. He realized that he can survive this way. He started to go everyday earlier, and return late. He doubled or tripled his money every day. Soon, he bought a cart, then a truck. In a very short time, he had his own fleet of delivery vehicles. Five years later, the man became one of the biggest food retailers in the U. S. He started to plan his family's future, and decided to have a life insurance. He called an insurance broker, and chose a protection plan. At the end of the conversation, the broker asked him for his email address. The man replied: ' I don't have an email.' The broker was dumbfounded. "You don't have an email, and yet have succeeded in building an empire. Can you imagine what you could have been if you had an email?," he exclaimed. The man thought for a while, and replied, "an office boy at Microsoft!" If you just lost your Job or Just failed an Interview Don't worry be Optimistic..... Good days are on the way and something better is reserved for you. All you have to do is Explore, Innovate and Explore !!!

Wednesday 16 May 2018

Logic behind Drawing Robot | How to make a robot which draws picture | Robotics Tricks

Hi Guys,

I posted a video before showing how you can use a robot to draw a picture.

The whole purpose that video was just to showcase the power of robotics and how UiPath robot can control the mouse even at pixel level.

The response to that video was really great and lot of people had requested me to explain the logic behind the painter robot.In this post, I wish to show step by step analysis of the drawing robot workflow.

Below video explains the logic in detail:




Let's see how this idea came to my mind.

Even though I'm not an active social media person, I use LinkedIn couple of hours a week to get updated on the technology news. Couple of months back, A LinkedIn post made me surprised. It was the post of Alekh Barli, CEO of INTELLIBOT.IO where he showcased the capability of Intellibot Robot to control the mouse at pixel level by drawing a picture in MS Paint.

https://www.linkedin.com/feed/update/urn:li:activity:6384009728531496960/

His post reminded me what I had done 10 years back when I was exploring VBA in Excel. At that time, I did a fun project to map a picture in Excel by coloring the Excel cells using VBA.

In my Excel image mapping project, I used VBA code to read the XY cordinates and RGB color combination of each pixel of a picture into an array, then I colored each cell in a worksheet using the array as if it is a pixel of a picture. Then if you reduce the zooming of the worksheet to 10%, it will exactly looks like the same picture.

This made me thinking that why can't I upgrade my old project to a robot, so I can showcase the power of robots while I'm presenting to my client or in my training sessions.

In fact, UiPath also uses the Microsoft .NET background, So it should be an easy task to upgrade my VBA project to UiPath.

The I assigned 2 hours to do this task.

But when it comes to reality, there were few constraints:

> I was using Excel, here I need to use Microsoft Paint

> I was reading the actual colors of the picture in my old project and making the same color in Excel cell. Since I'm using MS Paint to draw the picture, there is NO easy shortcut available to change the color of the brush everytime when the color changes. If I use the UI elements in Paint to change the color on each color, my robot will take a day to paint a picture. So I decided to read only the cordinates of the darker shades of the picture and map only those dots in Paint to imitate the picture.

The outcome is not so fantastic like my old project, but still it's enough to showcase the power of the UiPath robot.

Now let us go to the actual logic of the robot workflow.


Before starting, you should know how a computer saves a picture. In a common uncompressed bitmap, the image is stored as a series of dots which is also known as pixels. Each pixel is a very tiny dot or a square and a color is assigned to each pixel. Then the pixels are arranged in a pattern to form an image. You can see the pixels easily by opening a picture in Paintbrush and zoom In. So each pixel has some properties like it's position on the image, it is saved as XY scatter points. Then each pixel will have it's color. In easy way, you can see each color is a combination of Red, Green and Blue which varies from zero to 255.

In short, if you know the XY cordinates of each pixel in an image and also the color of each pixel, you can easily recreate an image.

In our project, you have to write a code to get the cordinates only since we are not going to use the color. Then write a code to mark each cordinate in Paintbrush.

Enough of gyan. Now let us look into the UiPath workflow.


You can download our project workflow from this link http://zipansion.com/hA5S


In first step, I inserted an activity to ask the user to select the picture he wished to sketch. Currently, I put a filter for only J P G files. You can change the filter as you wish. But make sure that the image you select is not a compressed bitmap.
Then the file path is saved to a string variable.

Our next step is to read the cordinates of the selected image. I create a small module for doing this task. I pass the image file path into this module and the module will return a Datatable with the cordinates to be marked.

Now let us look how this module works .

In first, I created a datatable object and created two columns for saving X and Y cordinate values.

Then we create a bitmap object using the image file we selected.

Then we need to find the height and width of the image and saves into variables.

Next, create nested loops to loop through each pixels of the image.

Within the loop, on each pixel, we use Get Pixel method to find the color of that pixel.

The color is assigned to a color variable.

The color variable has the properties to find its RED, Green and Blue of the color.

As we told earlier, RGB varies from zero to 255, we are finding only the darker shades of the picture. So we check if the pixel's Red, Green and Blue are less than 20, which are more darker. Then save the cordinates to a new row in the datatable.

If you want to get more pixel quality to your picture, you can increase the color range from 20, but the robot will take more time to finish the drawing since it will increase the number of pixels to be marked.

When robot finishes the loops, you have a datatable with all the cordinates to be marked.

Now let us go back to the main workflow.

In next step, I used Open application activity to open Paintbrush.

the next step is to change the color of the brush, if you want the robot to sketch the entire picture in a specific color.

In the next step, we have a loop to go through each row in our datatable.

Inside the loop, we use click activity to mark each pixel in paintbrush by changing X and Y values in the cursor position properties.

When the loop finishes, your robot will finish marking of all the pixels of your image.
You can download the workflow from the link below if you wish to refer. But I would suggest you to understand the logic and try it yourself.

Thanks for reading my post how you can use a UiPath robot to draw a picture.

Saturday 5 May 2018

Trick to stop UiPath Robot while running | Break the robot in middle | shortcut to stop UiPath robot

Hi Guys,

When you are doing the test runs of your robots during development, it's really important to know various methods to stop the robot in middle when you feel like there is something going on wrong.

It helps you to save your testing time and also to stop the robot in making big mistakes which cannot be reverted.

Let's see what all the options UiPath provides to break a running robot.

I explained this topic in short video below.





There are 4 ways to do it.


  • First method is by clicking on the stop button available at the task bar of your desktop which is available when you hover your mouse on the uipath studio. You can also click on the close button of the robot executor which appears next to your Uipath studio.


  • Second method is by pressing F12 from U i Path studio. You have to activate the Uipath studio and press F12 button.You can also click on the stop button available in U i path studio.


The first and second options are provided by U i Path themselves.

Sometimes you work on projects which has lot of activities including clicks or typetext where robot takes the control of your mouse quite often.

On such cases, you would not get enough time to click on the stop button or to activate the uipath studio to press F12.

Then you have to find your own ways to stop the robot.


  • One good trick to stop a robot is by closing the active window on which robot is currently processing. You just need to press ALT + F4 to close the current window.

The robot will stop processing as the robot is not able to find the objects which are used in the current window.

But this method also not so great.

Sometimes your application would not stop by pressing the shortcut, it show another pop ups.

Also, if you are stopping the robot to review the progress which has gained in the current window, you cannot review the application.

So all three tricks which we discussed are not quite successful on various scenarios.

As you might be thinking, it would be great to have a global shortcut which can be used to stop the robot.

I found a trick to get this done.

In this method, we will create a script to stop the robot and assign a shortcut key to run the script 

whenever you want to stop the robot.



You can set up this by doing simple 5 steps.

1. Open Notepad, then enter the script which is shown in the video.

2. Save the notepad file to your drive with an extension .bat

Click here to download the script file if you need

3. Open the folder where you have saved your script file, then create a shortcut to your desktop.

4. Goto desktop, then open properties of your shortcut file

5. Assign a shortcut key and press apply.

Now your hotkey is ready. You can stop your robot by pressing this hotkey.

But remember to assign a shortcut key which is not used regularly in other applications, otherwise your script will not run when your press the hotkey.


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

Monday 23 April 2018

Started a YouTube Channel

Hi Everyone,

I'm very thankful for your support since then I started this blog in 2012.

As per your requests, I started a new YouTube channel.

I will be posting videos with tips and tricks in various technologies.

Please subscribe if you like my posts.

Here is the link of the Youtube channel







Thursday 19 April 2018

How robot draws a picture | Robot drawing sketch | Sketching Robot

Hi Guys,

In this post, I wish to show how a UiPath robot draws a picutre in Microsoft Paint. 

I really got wondered when I saw the post of alekh Barli, CEO of INTELLIBOT.IO where he showcased the capability of Intellibot RPA to control the mouse at pixel level by drawing a picture in MS Paint. Here is his the post in LinkedIn. 

Because it's the same process I automated a decade back using a different technology.  At that time in 2008, I did a fun project to map a picture by coloring the Excel cells using VBA. Basically, I used a VBA code to read all the XY cordinates and color of a picture into an array and I colored each cell in a worksheet using the array imagining it is a pixel of a picture. Then if you keep the zooming of the worksheet to 10%, it will exactly looks like the same picture. 

This made me thinking that why can't I upgrade my project to a robot. In fact, UiPath also works on the Microsoft .NET background. So it's not a big task to to upgrade VBA to UiPath.

Then after spending couple of hours, I managed to upgrade the sketching project to UiPath. 

But there is a big change I made to overcome the constraints :

I was reading the actual colors of the picture in my old project and making the same color in Excel cell. Since I'm using MS Paint to draw the picture, there is easy shortcut available to change the color or the brush for each pixel. If I use the UI elements in Paint to change the color on each color, my robot will take a day to paint a picture. So I just read only the cordinates of the darker shades of the picture and mapped only those dots in Paint to imitate the picture.

The outcome is not so great like my old project since the we are not coloring everything, but still it's enough to showcase the power of the UiPath robot.

You can see the magic in a short video below




Note :


  • The workflow is created using UiPath RPA software
  • Robot reads the coordinates of the picture and points the coordinates in Paintbrush to recreate the picture.
  • I will share the workflow and logic in a different video very soon.


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

Thursday 5 April 2018

Top Excel VBA Macros Interview Questions & Answers - Samples from a hiring expert



I spent 11+ years of my career in various version of Excel and VBA. I used to hear from lot of people (even VPs & top leaders) that Excel is going to get outdated. Funny part is, I've been listening the same words again and again past 10 years, but nothing happened.

Excel is still a primary tool for data analysis and reporting and we cannot avoid VBA when you talk about Excel. I see that it will still have an important role for at least next 10 years as we don't have an alternative available which provides simple way to get complex things done.

I would like to share some Excel VBA Macros Interview Questions & Answers, which I had used to conduct technical interviews behalf of my clients to hire candidates for them.

The Questions has been prepared keeping in mind to evaluate the candidate's depth of knowledge in Excel VBA. This question bank is helpful for both Interviewee and Interviewer as it provides a quick channel of questions and answers covering major topics of Excel and VBA.

If you are a beginner in Excel VBA, I would not suggest this for you. Because in most of the cases the interviewer will not stop asking questions by giving a one line answer. He may ask you to describe more about the facts of the topic he asked to understand how good you are in the tools.

Kindly share the feedback in comments or contact me directly macromatician[at]gmail.com

I will be updating more when I have time. Click on the below links to go to each pages.


Basic Excel VBA Macros Interview Questions

1. Basic Excel VBA Macros Interview Questions - Set 1 - January 2018 -  10 Questions
2. Basic Excel VBA Macros Interview Questions - Set 2 - February 2018 -  10 Questions


Advanced Excel VBA Macros Interview Questions:

1. Excel VBA Macros Interview Questions Part I - 10 Questions
2. Excel VBA Macros Interview Questions Part II - 10 Questions



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.

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



Monday 12 March 2018

Tips to crack RPA interview for freshers


Hi RPA Aspirants,

In this post I'm giving important tips to RPA freshers who wanted to pursue RPA as their career :

First of all let's look into RPA technology.

Robotic process automation (RPA) is the latest technology to automate business processes. In traditional desktop automation tools, a developer automate a list of actions to automate a business process by writing codes in scripting language and also connects to the back-end system using internal application programming interfaces (APIs) or scripts. But in RPA, systems develop the action list by watching the user perform that task in the application's graphical user interface (GUI), and then perform the automation by repeating those tasks directly in the GUI. This can lower the barrier to use of automation in products that might not otherwise feature APIs for this purpose.


Important Tips

1) Be thorough on basics: 

If you are new to RPA, the interviewer will more concentrate to test your knowledge on the basics of PRA and also in general programming. This may include questions regarding the technology, RPA products, basic programming concepts, basics actions on the RPA tool which you trained.

2) Refer Frequently asked Questions: 

Do a very good research in Google and also in RPA forums to find the frequently asked questions. I will also post FAQs very soon in my blog.

For example in Blueprism, most important parts are Login agent, Work queue configuration and Scheduler.

In Automation Anywhere, you should prepare for questions in Meta Bots, Task Bots and Object Cloning.

In UiPath, key elements are workFlow, Sequence and Orchestrator.


3) Practice sample projects: 

Major RPA vendors are providing trial versions and also a discussion forums or community. You should refer the use cases in the forums and do small projects yourself.

4) Sell yourself: 

Like any other interviews, the presentation really matters.

5) Be yourself. 

A certificate from a RPA vendor will not land you a job. RPA is very wide and deep. If you act like a pro in front of the interviewer, he might tend to ask more questions from real world scenarios which you cannot answer if you actually don't have a hand on real time projects.

I will be posting more RPA interview stuff very soon in my Blog. Best of luck.

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 

Friday 2 February 2018

How to Create a Notepad diary / Notepad Scheduler / Notepad Logbook

Here are the simple steps to use the Windows Notepad as a simple digital diary /  Scheduler

  1. Just open the Notepad and type .LOG on the first line and press enter.
  2. Save the Notepad file with any name of you want.
  3. After saving it, Close the Notepad and open it again.
  4. Surprisingly you can see the current date and time being inserted automatically after the  .LOG line.
  5. Now write your daily/hourly activities easily in the next lines
  6. After that save the file & Close
  7. When Your open that file next time, you can see current date & Time in the next line again automatically

Note : If you want to bring the current date and time just press shortcut key F5, Its another option

Thursday 1 February 2018

Basic Excel VBA Macros Interview Questions & Answers - II

Part 2

11) How to add a module to a VBA project?
Ans: Right Click on VBA project in VBA screen > Insert > Click on Module

12) Why Developer tab is not visible in my application, How to enable it?
Ans : By default, the Developer is disabled in Office Applications. We can enable Developer tab from Customise Ribbon section in the Options window of Office Applications.

13) How to record a macro?
Ans : First of all, you need to enable the Developer tab. Then you can either click on the record button on the status bar or the record button on the Developer tab.

14) Why I’m not able to record macros in my application?
Ans : First of all, you need to enable the Developer tab. Also check the macro security section to Enable macros.

15) What is a comment, What all are the types of comments used in VBA ?
Ans : Comments are used to document the program logic and the user information with which other programmers can work seamlessly on the same code in future.
There are mainly two methods in VBA to represent comments.
Any statement that begins with a single quote is treated as comment
Or you can use statement REM instead of single quotation mark (‘)

16) Difference between VB6, VBScript, VBA, VB.NET?
Ans : VB6: Visual Basic is Windows Application Development Programming language developed by Microsoft.
VBScipt: Visual Basic Script is mainly developed for Internet Explorer. Similar to Java-script.
VBA: Visual Basic for Applications is a Macro language available in all MS Office Tools.
VB.NET is a one of the .NET programming language from Microsoft. We can develop COM Add-ins for MS Office using VB.NET or C#.NET.

17) Why VBA is called as Object Based Language, instead of Object Oriented Programming Language?
Ans :  VBA Does not support all the OOPS techniques (VBA will support Polymorphism, Encapsulation and not Inheritance). So, its generally called as Object Based Programming Language.

18) My colleague sent me a macro file. But I’m not able to run it. What’s the issue?

Ans :

There can be multiple reasons.

: - Sometimes when we open a file which is received from someone else, it opens in ‘Protected View’. We should click on Enable editing and try running the macro again.

:- Other scenario can be caused by Macro security. If the macro security is on ‘Disable macros without notification’, then we cannot run the macros.

19) How to protect a macro with a password
Ans : Go to VBA IDE, then right click on the VBA project in Project explorer, then Enter the password in Protection tab in the pop up window.

20) Mention the difference between the Subroutines and Functions in VBA?
Ans :
The difference between the Subroutines and Functions are
Subroutines never return a value, but functions does return values
Subroutines could change the values of the actual arguments while a function could not change the value of actual arguments
Subroutines cannot be called from Excel formulas, but a function can be used as an Excel formula
Subroutines are visible in Macro list (if there are not arguments given), but functions are not visible in Macro list.


Click Here to View more Excel VBA Macros Interview Questions & Answers

Monday 15 January 2018

A Great Milestone - 400,000 visitors

It's great to start 2018 seeing 400,00 visitors to my blog.

I started this blog as a back up of day to day work in 2010. 

Then later I decided to make my posts public in 2012 so it can be useful to others as well. But the busy work schedule has blocked me from spending time on this. 

I was completely away from this blog for couple of years.Thanks for your mails with suggestions and requests. As per your demands, I will be sharing more resources to learn and master VBA Macros and other technologies as well.

If you like the posts in my blog, kindly share the posts with your friends in Facebook, Google+, etc.

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.


Monday 1 January 2018

Basic Excel VBA Macros Interview Questions & Answers - I




1) Explain what is VBA or Visual Basic for Applications?
Ans: VBA stands for Visual Basic for Applications. It is an event driven programming language developed by Microsoft. It is mainly used to automate tasks in Microsoft office applications like MS-word, MS-Access, and MS-Excel.

2) What is macro? 
Macro is another name of a VBA program. It’s is a set of programming instructions written in VBA code which can be used to eliminate the need to repeat the steps of commonly performed tasks over and over again.

3) What is Data-types in VBA?

Data types helps to declare Variables with specific data, this helps to VBA to know the type of the data and assign the memory based on the DataType of the Variable.

4) Name some of the data types?

1. Boolean
2. Byte
3. Currency
4. Date
5. Double
6. Integer
7. Long
8. LongLong
9. LongPtr
10. Object
11. Single
12. String
13. Variant

5) Where or How you can write VBA program or Macro?

VBA programs are written in the code window of IDE (Integrated Development Environment) which is a part of all Office Applications. You can access the IDE by entering the shortcut ALT + F11 or by clicking on Visual Basic on Developer tab.

6) What are the file extension of excel workbooks with macros in Excel 2003, 2007, 2010, 2013 and 2016 ?
Ans : Excel 2003 = xls, Excel 2007 = xlsm, From Excel 2010 = xlsm

7) Why we are using macros ?
Ans : If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. Moreover, macro can perform lots of tasks which we can't do manually (like looping statement)

8) What is the shortcut to go to VBA screen ?
Ans: ALT + F11

9) Can we record a looping statement ? 
Ans : NO

10) Give some examples of looping statements ?
Ans: For loop, Do While Loop, Do Until Loop