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.