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