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