Assign Vb Macro (Message Box) msgbox

The MsgBox is a dialog box in Excel VBA we can use to inform the users of your program. There are 5 arguments which we can use with MsgBox function.

 
1) Prompt - This is  required argument where user writes the Text / Warning to display

2) buttons - Optional Argument - User can select Ok/Cancel etc buttons from this argument

3) title - Optional Argument - String Expression displayed in the title bar of message

4)helpfile - Optional Argument - User can attach the help file

5)context - Optional Argument - Require Numeric Expression to assing context in Help file. If context is provided then helpfile must also be provided
 
 
We can achieve above task by following steps

1) Open Excel
2) Select Developer Tab
3) Select Button Under Insert Link (Control Tab)
4) Drag Button on Excel Sheet
5) Right Click on Button
6) Select Assign Macro





MsgBox "Welcome to Learn IT with Sanjay"

Message with Cell Value

MsgBox "Welcome to Learn IT with Sanjay, you have Entered " & Range("I8").Value

New Line Message

MsgBox Range("I8").Value & vbNewLine & Range("I9").Value

See you with next LEARNING

Sanjay
You can contact me  @ mesanjaybakshi@gmail.com for Queries

Assign VBA Macro (Running Business Validation Check Length of Excel Cell)

Yesterday one person asked that how can we run a business validation in Excel? I have given below example and same I sharing with you all.

In last post of VBA we have learnt "How to add two numbers via VBA" now what if I change the situation and mention that if Excel Cell values are empty system should run the business validation and display an error message if user is not passing any values in Cell.

We will follow below steps to check whether user is passing the values or not.

Sub Button1_Click()
If Len(Range("I7").Value) = "0" Then //Check the length of Cell I7
MsgBox ("Please Enter Value in Cell I7") //Display warning message to User
Exit Sub//Block the statement until above condition does not pass the validation
End If

If Len(Range("I8").Value) = "0" Then//Check the length of Cell I8
MsgBox ("Please Enter Value in Cell I8")//Display warning message to User
Exit Sub//Block the statement until above condition does not pass the validation
End If


Range("I9").Value = Range("I7").Value + Range("I8").Value

Len - Function return the length of specified string.
MsgBox - Displays a message in dialog box, wait for user to click the button

As a basic concept we are checking the length of particular cell and making an If condition.

Now this helps user to run the basic validations in Excel.

Keep Learning
Sanjay
mesanjaybakshi@gmail.com

Assign VBA Macro (Adding two Numbers)

A question has been asked in an Interview that if you have to add two numbers in Excel what will be the methods will we use? Most of us will answer that either we can use sum formula or we can use auto sum functions.

Now what if Interviewer has asked to solve above situation via VBA. So answer should be YES and you can write a very simple macro by using VBA. Here we go with small Example

1) Enable Developer Tab
2) Click on Insert Button and Select one Button

3) Change the Text to "Add Numbers"

4) Select Add Numbers and click on View Code button under Control Ribbon

5) Now as per question we have to execute our event on Button Click so we will write our code on Button Click.

Sub Button1_Click()
Range("I9").Value = Range("I7").Value + Range("I8").Value

End Sub

Range is actually an object which represents the Cell and .Value represents the text of that range.

 So our solution is ready, change the value of first no and second no, click on button and your answer will be ready.

In next post we will learn about

1) Working on Business Validation 
2) Display of Warning Message

Keep Reading and Keep Calm

Sanjay
mesanjaybakshi@gmail.com

Excel Macro (How to Create a Macro)

With Excel VBA (Visual Basic for Applications) we can automate tasks in Excel by writing Macros. By default when you open Excel you will not get Developer Tab where actually we writes Macro.

1) Select Office Button.
2) Click on Office Options.
3) Click on Show Developer tab in Ribbon.

Above settings will make user enable to write the code for Macro. We will cover basic code functionality for writing macro in next posts.

Until than Keep Reading and Keep Calm.

Thanks,
Sanjay
mesanjaybakshi@gmail.com

Excel Tips (3) with Sanjay Bakshi

Q. If we have a large excel file and wanted to select whole row not just a cell
A. Select Cell and click shift + space Bar

Q. Provide a way to selecting Range of Cell
A. Select first cell and click on F8 selection range process will be activated and then use arrow keys

Q. How can we make column visible as we Scroll
A.  Select the row immediately below the row which we wanted to Freeze
      In the Window menu click the Freeze Panes

Q. You want 00111 to be display but when you enter Excel convert this to 111, what to do here?
A.  Type 00111 in Cell
     Right Click on Cell 
     Select Format Cells
     Select Number TAB
     In category list box select TEXT

   Q. Create reminder process in Excel which will help Doctor to track today's appointment
   A. =IF(E2=TODAY(),"Today's Appointment","")

Q. What is count If and provider one example
A. Count the No of Cells based on Given Criteria. Count if function need two parameter, first is cell range and second is Criteria

Below picture shows a matrix where user wanted to know the no of Employees achieving 100% & Above Target. So in this scenario we will select the cell and in criteria we will write ">=100%". We can use wildcard criteria also in Count If

=COUNTIF(H3:H8,">=100%")

Q. Provide some example of Count If which follow Wild Card Criteria

1) =COUNTIF(D3:D8,"S*") -- This will help user to count the number of cells starting with S

2) Calculate Absenteeism % for one group
    =COUNTIF(F3:F8,"N")/ROWS(F3:F8)

Keep Reading & Keep Calm...

Sanjay Bakshi
mesanjaybakshi@gmail.com

SQL Post 7 ( Database Shrinking)

Situation

There are two database administrators are in company and both are working in Shifts.First Database admin keeps his index DE fragmented in day time so that high level queries can perform well but every next day he sees logical scan fragmentation to 90%.What's going on here?

Answer

Automatic Data Shrink Job was running every night.

So the question is what is the problem with Shrinking?

When we click on Shrink Database actually we are instructing SQL to remove unused space from database files. In this process SQL does index fragmentation and that affects performance of SQL in long run.

Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of SQL Queries that search a range of Index. To eliminate the fragmentation, consider re building the indexes after shrinking.

So question arises, Should we never shrink? No, we can shrink, but the feature there is for emergency or one time events. If we get a load of 500 GB on 1 TB data once a year we can use shrink once a while.

When we shrink we should use shrink file and target specific file and for a specific reason not as a part of regular maintenance.

Happy Reading and Keep Learning

Sanjay Bakshi
mesanjaybakshi@gmail.com

SQL Post 6 (Database Re Naming)

Sometime there is a need to change the database name in SQL. There could be multiple reasons where DBA can face this situation.

1) Project name got changed and DBA has to change the Database Name

2) DBA is restoring is database name as temp datbase and need to change the database name

SQL Server provides couple of options to rename the database.

Rename the database name from SSMS

1) Open SSMS

2) Select Database Name and Right Click

3) Select ReName Option

*********************************************************************

 Rename the database name from Attach and De Attach

1) Open SQL Server Query Analyzer

2) Exec sp_detech_db 'Old_Database_Name','true'

3) Exec sp_attach_db @dbname='New_Database_Name'

                                          @File_Name1='Provide path of MDF file Name'

                                          @File_Name2='Provide path of LDF file Name'

*********************************************************************

Rename the database name from Query

1) Open SQL Server Query Analyzer

2) Sp_renamedb 'old_database_name','New_Database_Name'

*********************************************************************

Keep Reading and Happy Monday

Sanjay Bakshi

mesanjaybakshi@gmail.com