Welcome to the website.

I'm Macromatician, Freelancer based in Bangalore, India.
I undertake freelance Projects in MS Excel, MS Access, VBA Macros, Powerpoint, Outlook, etc. .
I worked more than 8 years in reporting using advanced Excel tools, MS Access and automating reports by writing VBA Macros.
I'm Expert in writing VBA Macros, Creating Dashboards, Report Templates, MS Access Databases, Graphs and Charts, etc
Are you looking to break the Excel file password? Then I will do it for you.
I created this site to share my experience with you. I would be happy if my tips helps you to make your life easier.
Mail me: macromatician[at]gmail.com .(Replace [at] with @ while sending).

Wednesday, 27 April 2016

How To Check If Outlook Application is open by using VBA code

When you play around with outlook macros to send mails, get mail data, etc, You will face an issue whether the end user has not opened the outlook.

If the outlook is not open when you run the code, you cannot get the desired results.

I made a simple code to check if the outlook is open while you run the code.

This can be used from Both Excel or Acces.


Sub CheckOutlookIsOpen()
    Dim oOutlook As Object
    On Error Resume Next
    Set oOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If oOutlook Is Nothing Then
        MsgBox "Error : Outlook is not open! This cannot be run without opening outlook", vbCritical, "Error"
        End
    End If
End Sub

Friday, 10 January 2014

Excel VBA Macros Interview Questions & Answers - Part II


1. What is the difference between ByVal and ByRef and which is default ?
Solution: ByRef: If you pass an argument by reference when calling a procedure the procedure access to the actual variable in memory. As a result the variable's value can be changed by the procedure.
ByVal: If you pass an argument by value when calling a procedure the variable's value can be changed with in the procedure only outside the actual value of the variable is retained.
ByRef is default: Passing by reference is the default in VBA. If you do not explicitly specify to pass an argument by value VBA will pass it by reference.

2. What is the meaning of Option Explicit and Option Base?
Solution:

Option Explicit makes the declaration of Variables Mandatory while Option Base used at module level to declare the default lower bound for array subscripts. For eg. Option Base 1 will make the array lower bound as 1 instead of 0.

3. What are various data type and their size?
Solution:

i) The Boolean data type has only two states, True and False. These types of variables are stored as 16-bit (2 Byte) numbers, and are usually used for flags.
ii) The Byte data type is an 8-bit variable which can store value from 0 to 255.
iii) The Double data type is a 64-bit floating point number used when high accuracy is needed.
iv) The Integer data type is a 16-bit number which can range from -32768 to 32767. Integers should be used when you are working with values that can not contain fractional numbers. In case, you're working over 32767 rows use Long as data type.
v) The Long data type is a 32-bit number which can range from -2,147,483,648 to 2,147,483,647.
vi) The Single data type is a 32-bit number ranging from -3.402823e38 to -1.401298e-45 for negative values and from 1.401298e-45 to 3.402823e38 for positive values. When you need fractional numbers within this range, this is the data type to use.
vii) The String data type is usually used as a variable-length type of variable. A variable-length string can contain up to approximately 2 billion characters. Each character has a value ranging from 0 to 255 based on the ASCII character set.

4. Code to find a Last used Row in a column or Last used column of a Row.
Solution:
Last Row in a column can be find using End(xlUp) from the last Cell and Last Column in a row can be find using End(xlToLeft). For e.g. Range("A1048576").End(xlUp).Row gives last used row of Column A.

5. Difference between ActiveX and Form Controls.
Solution:

i) Forms controls can be used on worksheets and chart sheets. Forms controls can also be placed within embedded charts in Classic Excel (though not in Excel 2007). ActiveX controls can only be used on worksheets. ActiveX controls do not work in MacExcel.
ii) The Forms controls aren’t very complicated, and they have been part of Excel for longer (they were used in Excel 5/95’s dialog sheets) than the Controls Toolbox (Excel 97), so it stands to reason that they’d be more seamlessly integrated. Being newer, the ActiveX controls have richer formatting possibilities. Both can link to cells and ranges in the worksheet.

6. What is the difference b/w Functions and Subroutines?
Solution: There are so many differences. I noted few.
i) Subroutines never return a value but function does return values.
ii) A function could not change the values of actual arguments whereas a subroutine could change them.
iii) A subroutine cannot be used in a worksheet
iv) A subroutine can manipulate the any objects. A Function Cannot.
v) A function will not be listed in the names of macros
 
7. How to debug a VBA code?
Solution:
Using Breakpoints(F9), Step-by-step execution (F8), Debug.Print & Immediate Window and Watch window.

8. Draw basic Excel Object Model.
Solution:
Application --> Workbooks --> Worksheets --> Range / Chart

9. What are properties, methods, events and objects?
Solution:
For details click here --> http://msdn.microsoft.com/en-us/library/ms172576%28VS.80%29.aspx
All the controls in the ToolBox except the Pointer are objects in Visual Basic. These objects have associated properties, methods and events.
A property is a named attribute of a programming object. Properties define the characteristics of an object such as Size, Color etc. or sometimes the way in which it behaves.
A method is an action that can be performed on objects. For example, a cat is an object. Its properties might include long white hair, blue eyes, 3 pounds weight etc. A complete definition of cat must only encompass on its looks, but should also include a complete itemization of its activities. Therefore, a cat's methods might be move, jump, play, breath etc.
Visual Basic programs are built around events. Events are various things that can happen in a program. Let us consider a TextBox control and a few of its associated events to understand the concept of event driven programming. The TextBox control supports various events such as Change, Click, MouseMove and many more that will be listed in the Properties dropdown list in the code window for the TextBox control. We will look into a few of them as given below.
* The code entered in the Change event fires when there is a change in the contents of the TextBox
* The Click event fires when the TextBox control is clicked.
* The MouseMove event fires when the mouse is moved over the TextBox

10. Union is used for _____________ ?
Solution:
To unite the different ranges depending on the logic. It is similar to set union, here range works as set. For eg. Set nrange = Union(rng1,rng2)

Click here to see more Excel VBA Macros Interview Questions

A Great Milestone

This is the day we reached 10,000 visitors in our blog. Thanks for your support.

I started this blog as a back up of day to day work. But the busy work schedule has blocked me from spending time on this. Thanks for your mails with suggestions and requests. As per increasing demands, I will be sharing more resources to learn and master VBA Macros.

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

Thursday, 9 January 2014

Excel VBA Macros Interview Questions & Answers - Samples

I would like to share some Excel VBA Macros Interview Questions & Answers, which I had used to take technical interviews.

The Questions has been prepaired keeping in mind that the candidate should have good 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 begginner in Excel VBA, I would not suggest this for you. 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 sharing more when I have time. Click on the below links to goto each pages.

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



Thursday, 24 October 2013

Excel VBA Macros Interview Questions & Answers - I

Excel VBA Macros Interview Questions & Answers

1. What is the file extension of excel workbooks with macros in Excel 2003, 2007, 2010 ?
Ans : Excel 2003 = xls Excel 2007 = xlsm Excel 2010 = xlsm

2. 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)

3. What is the shortcut to goto VBA screen ?
Ans: ALT + F11

4. Can we record a looping statement ? Give some examples of looping statements ?
Ans: No. For loop, Do While Loop, Do Until Loop

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

6. a,What is the meaning of "Option Explicit"? b, Where it should be used ?
Ans : a, Option Explicit makes the declaration of Variables Mandatory (it forces us to declare all variables used in our codes) Line explicit function makes the compiler to identify all the variables which are not specified by the dim statement. This command significantly reduces the problem of type errors. This is used extensively because VBA deals with information rich applications in which type errors are common.
b. It can be used inside a module, before starting any sub procedures

7. How we will hide a sheet permanently ? (How to hide a worksheet so that a normal user cannot unhide it)?
Ans :
Use Sheet's visible property and set it to xlSheetVeryHidden. We can do it in 2 ways.
1. VBA screen > worksheet properties > visible > change to xlSheetVeryHidden
or 2. Use code sheet1.Visible = xlSheetVeryHidden

8. a, If I require a macro to run everytime when a workbook opens, can this be recorded ?
Ans: No
b, So, where should I add codes for this ?
Ans : goto VBA screen > double click on THISWORKBOOK > then select > Workbook_Open

9. What is the difference between thisworkbook and activeworkbook?
Ans: ThisWorkbook refers to the workbook where code is being written while ActiveWorkbook refers to the workbook which is in active state with active window. In case of only one workbook open, ActiveWorkbook is same as ThisWorkbook

10. Now I have an object variable named "Wkbook", tell the code to assign the activeworkbook to this object ?
Ans : SET Wkbook = ACTIVEWORKBOOK

Click here for more in Excel VBA  Macros Interview Questions


Tuesday, 19 February 2013

Important concepts in VBA Macros


Essential concepts to remember

In this section, I note some additional concepts that are essential for would-be VBA gurus.

  • Objects have unique properties and methods.
Each object has its own set of properties and methods. Some objects, however, share
some properties (for example,
Name) and some methods (such as Delete).
  • You can manipulate objects without selecting them.
This might be contrary to how you normally think about manipulating objects in Excel.
The fact is that it’s usually more efficient to perform actions on objects without selecting
them first. When you record a macro, Excel generally selects the object first. This is
not necessary and may actually make your macro run more slowly.
  • It’s important that you understand the concept of collections.
Most of the time, you refer to an object indirectly by referring to the collection that it’s
in. For example, to access a
Workbook object named Myfile, reference the
Workbooks
collection as follows:Workbooks(“Myfile.xlsx”)

This reference returns an object, which is the workbook with which you are concerned.

  • Properties can return a reference to another object.

For example, in the following statement, the
Font property returns a Font object contained
in a
Range object. Bold is a property of the Font object, not the Range object.
Range(“A1”).Font.Bold = True

  • There can be many different ways to refer to the same object.
Assume that you have a workbook named

Sales, and it’s the only workbook open.
Then assume that this workbook has one worksheet, named
Summary. You can refer to
the sheet in any of the following ways:

Workbooks(“Sales.xlsx”).Worksheets(“Summary”)
Workbooks(1).Worksheets(1)
Workbooks(1).Sheets(1)
Application.ActiveWorkbook.ActiveSheet
ActiveWorkbook.ActiveSheet
ActiveSheet

The method that you use is usually determined by how much you know about the workspace.
For example, if more than one workbook is open, the second and third methods
are not reliable. If you want to work with the active sheet (whatever it may be), any of
the last three methods would work. To be absolutely sure that you’re referring to a specific
sheet on a specific workbook, the first method is your best choice.

VBA code to close all Excel workbooks except the active workbook


VBA code to close all Excel workbooks except the active workbook:

If you had worked in Excel with several workbooks at a time, you might have came across a question like this.

How to close all other workbooks except the active workbook in one shot instead of going to each workbook and close ?

Microsoft Excel Does'nt provide a direct way to close several workbooks at a time.

But we can use VBA codes to accomplish this task.

How :

  1. Select the workbook you wish to retain
  2. Go to VBE
  3. Add one Module
  4. Copy paste the below sub procedure to a module in your code
  5. Run the Macro Whenever you want to close other workbooks
  6. Remember to Save the file as MacroEnabled
Sub CloseAllSheetsExActive()
Dim WBs As Workbook
For Each WBs In Application.Workbooks
If Not WBs.Name = ThisWorkbook.Name Then WBs.Close (False)
Next WBs
End Sub


Note : Macro will close all the workbooks ignoring all the changes you have made without asking whether to save it or not. If you use TRUE instead of FALSE after close method, it will save all the changes without asking. If you don't use TRUE or FALSE, a dialog box will show to ask whether you wish to save or close without saving.

How to add formula to a worksheet range using VBA Codes

How to add formula to a worksheet range using VBA


Adding formulas to ranges is common task when you workaround with Excel VBA.

If you are good in entering formulas in Excel, you can become an expert in adding VBA formulas too.

Eg :

We have some values in a range A1:A20 and We have some values in B1:B20 also. We need to get the difference of all values in B column with corresponding Cell in Column A, in the Column C.

In Excel we will enter a formula = B1-A1 in the cell C1 and do a fill down or a copy paste till C20. It's the simplest formula when we work in Excel.

But How to get the same formulas in C1:C20 using VBA codes ?

You can use two ways.

1. Enter Activesheet.Range("C1:C20").Value = "RC[-1] + RC[-2]" in your code.
2. Enter Activesheet.Range("C1:C20").FormulaR1C1= "RC[-1] + RC[-2]" in your code.

Both codes with do the same task. Note, VBA is using R1C1 reference in entering the formulas to Excel. It's better to Get a basic idea about how R1C1 reference style works, before you start working with formulas in VBA.

Solution: If you have any doubt in VBA, first try to record the task which you want to automate and see the codes. Same way, you record the task of entering the above formulas using macro recorder. See the codes.

Wednesday, 13 February 2013

Find the total number of sheets in an Excel Workbook


VBA Code to Get the count of all sheets in an Excel Workbook

Sometime I come across with Excel reports with numerous worksheets / tabs.

Sometimes you can't find the starting or ending of the workbook.

Whenever I see those kind of Excel reports, I always wonder how many sheets will be there in that workbook !

Of Course, You can count the Excel sheets one by one. Select one sheet, then press CTRL + Tab to goto next sheet, keep on counting in your head. :) Very good time pass.

Excel is not providing a way to find how many worksheets are existing in a workbook.

If you want to know the count of all sheets in an Excel workbook, we have an easy way.

Steps:
  1. Goto VBE, by pressing ALT + F11 or Click on VisualBasic Icon from the Develepor Tab.
  2. Activate Immediate window by Pressing CTRL + G
  3. ?activeworkbook.Sheets.Count   Copy paste the highlighted VBA code to immediate window and press Enter
  4. You can see the count of the sheets in the active Excel workbook has printed just below the code which you have entered.

    msgbox activeworkbook.Sheets.Count If you use this code, a message box will be shown with count of sheets in the workbook.

This code becomes very important when you write VBA routines and loops.

Monday, 21 January 2013

What is the use of Option Explicit while writing VBA Code/ How to avoid typing mistakes on variable names in VBA Code / How to find misspelled variable in a VBA procedure


What is the use of Option Explicit while writing VBA Code/ How to avoid typing mistakes on variable names in VBA Code / How to find misspelled variable in a VBA procedure ?


We use so many variables while writing VBA codes and we will do so many tasks using these variables, like assigning a value, retrieving the assigned value, etc.

Probably, a variable will be used several times in a sub. If you have made a spelling mistake while typing the variable name, VBA will not detect it automatically. VBA will consider the misspelled word as an object variable.

To avoid this we can use the key word Option Explicit. This should be used out side any procedure and before starting of any sub.

If we have used this, while running any sub under that VBA will detect any undeclared variables in that sub and stop running the sub asking to declare it. Without delcaring the variable, VBA will not allow you to execute the sub.

Note: You don't need to type this keyword always. Goto VBE > Tools > Options > Click on 'Require variable declaration'. VBA will automatically add this keyword to any modules.

Wednesday, 2 January 2013

How to exit from a sub / How to stop executing a sub while running a macro


Everyone knows we can use the key combination CTRL + BREAK to stop running the execution of macros.

But while coding you will come across the following scenarios.

1. How to stop a macro in between a sub without executing further codes.

2. How to exit the execution upon one condition

3. How to stop running the current sub and return to the main sub


Solution : We have two key words 'Exit Sub' and 'End'

When to use this ?

Very simple ......

If we use 'END' in codes, all the executions will be stopped. No more codes will be processed.

But, The 'Exit Sub' can be used to stop running a sub and return to the place where it's called.

Eg :  Here we have 2 subs, The Main Sub calls the Process1 sub while running. In Process1 sub, we have a condition. If condition does not meet, it will return to the main sub and execute further codes. If I would have used 'END' instead of Exit Sub, the macro will stop running and no more codes will be processed.

Sub Main()
     Process1
     codes ....
     codes...
End Sub

Sub Process1()
If Activecell.value <> 1 Then Exit Sub
Codes .....
Codes.....
End Sub



Wednesday, 11 July 2012

How to get username in a cell / Function to get the username


How to get username in a cell / Function to get the username


Add the below codes in a module of your project.

Public Function CurrentUser()
CurrentUser = Application.UserName
End Function


This function can be called from your worksheet or from a macro you have written.

I don't suggest this to validate the user as the Excel username can be easily changed .

But still there are lots of uses for this function in realtime. Like you can surprise the user by greeting him , etc.

How to Speed Up the macro performance / Improve efficiency of macro


How to Speed Up the macro performance / Improve efficiency of macro ?

How to stop blinking Excel while running a macro ?


You can see magic on your macro if you add the below line just after starting of your code.

Application.ScreenUpdating = False

By default, this setting will be 'True' mode.

Then, Excel will update the screen for each task. (Eg : copy paste, delete, filter, etc...)

Macro running speed is very fast, but the screen updation speed is very low.

So it will end up in annoying blinking of excel and it affects macro performance very badly.

If you disable the screen updation, macro will run smoothly and Excel will hide all the screen updations.

I bet, The speed will make you suprised.....


Note : When the macro finishes running, this setting will automatically change to TRUE. However ever it's a good practice adding a line before end of the sub to the Make this setting TRUE.

How to change the module name in VBA / Edit Module Name In VBA


How to change the module name in VBA / Edit Module Name In VBA


You might have noticed that when you create a module, VBA allocates a default name (Module1, Module2, so on).

You can personalise your module.

We can change this default name very simply.

Steps

Open VBE (ALT + F11)

Select the Module from your project explorer.

You can see the Name property of the selected module in the properties box.

Edit the name and give as you wish.


Notes :

If properties window is not visible, press F4 to make it visible.

The naming criteria is subject to VBA's naming parameters.

Tuesday, 10 July 2012

How To disable scrolling in an Excel sheet / Make a Static Page in Excel

How To disable scrolling in an Excel sheet / Make a Static Excel Page:

Most of the Excel Geeks might have faced this problem.
How to make an Excel page static ?
How to lock the scrolling or disable the scrolling of the excel sheet along with mouse ?
How to make a static front page for your dashboard or excel report ?
How to prevent the end user from scrolling the sheets?
Even I have faced this issue.
I found a solution after long experiments.

 Click on the this link to open the sample file for Reference. 
Download the file. (Note: The password is blank to unprotect the sheet)

Did you like the way it's built ? It's the billing file I had built for myself to note the time spent on projects and to send to clients for payments. You can see the file has made in a professional way. A Normal user will get wondered thinking what kind of file is this. So many clients had asked me that which software I had used to make this billing file.

It's a simple excel file. You can see the Column headers & Row headers are hidden, Scroll bars are not available, Sheet tabs are hidden. You can click or select only the cells which requires any changes. It's all basic Excel tricks.

But you can see that you can't scroll the mouse. You can't use PageUp Or PageDown. The page is completely fixed only the screen. I will explain how to do this.

Do the following steps:

1. First of all, hide the vertical or horizondal scroll bars or both as per your need.
    File > Excel Options > Advanced > Display options for this workbook >
   Untick the Show horizondal scroll Scroll bar and vertical scroll bar.
   Click OK, it will hide the bars. But it's not the solution. You can see still the scrolling is working.
2. Then Goto VBE ( Press Alt + F11)
3. Open Project Explorer > VBAproject > MS Excel objects > This workbook
4. In VBA code Window
                  From 'General' drop down , select 'Workbook'
                  Then, from the 'Declaration' drop down, select 'Open'
You will get a default code like this.
Private Sub Workbook_Open()
End Sub

5. Enter ThisWorkbook.Sheets("DashBoard").ScrollArea = "A1:E20"  into the above sub.
Final code will be like this :
Private Sub Workbook_Open()
ThisWorkbook.Sheets("DashBoard").ScrollArea = "A1:E20"
End Sub


This code will run when you open the workbook and the page will be static. User will not be able to scroll or select any cell out of the given range above.

Notes :
  • You should change name of the sheet and the range in the above code as per your workbook. 
  • If the current workbook is not macro enabled workbook, you have to save the file as macro enable workbook. 
  • Make sure the macro is enabled on your excel
  • You cannot unfreeze the scrolling once it's run the code while opening the file. If you want to unfreeze the scrolling, first comment the vba code and then save the file. So the code will not run while opening the file. Then Re-Open the file.
How to Comment the VBA code:

Add the comment operator (') before starting of the line as shown below. The Single Quote has placed before ThisWorkbook. You can see the commented line in a different color.

Private Sub Workbook_Open()
'ThisWorkbook.Sheets("DashBoard").ScrollArea = "A1:E20"
End Sub

Queries! Contact Me...

For any queries, send a mail to macromatician[at]gmail.com .(This is to avoid spam mails, replace [at] with @ while sending the mail).

Services:

Undertakes freelance Projects in MS Excel, MS Access, VBA Macros, Powerpoint, Outlook, etc.

Creating Dashboards, Report Templates, MS Access Databases, Graphs and Charts, etc

Are you looking to break the Excel file password? Then I will do it for you

Contact Form

Name

Email *

Message *

Popular Posts

Twitter Delicious Facebook Digg Stumbleupon Favorites More