There are lot of charts in excel in different width and length sizes. I need to resize them to same size, in order to use them in specific document, presentation or on website. To resize charts, i will have to select each chart and if there are more than 6 charts, it will become difficult to select charts by scrolling or keeping the Ctrl key pressed during selection.
Also, if i have charts in multiple sheets, i will have follow the below steps for each sheet, which will be really a time consuming task.
Select all charts on sheet, by first selecting one chart and then select other using mouse click by keeping Ctrl key pressed
Go to chart tools or Drawing tools(in case multiple charts are selected) menu
Click format
Check on the size menu on the right end of menu bar
Change the shape height and shape width to the required size
Done
Follow above steps on each sheet independently.
Instead of spending time on each sheet, we can write a VBA macro which can be used to resize all charts at once, on all sheets.
–Code start
Sub ResizeCharts()
‘ Define the chart variable as chartobject, which will identify the charts in the activesheet Dim chart As ChartObject
‘Write FOR NEXT loop to select each chart and rename in active sheet For Each chart In ActiveSheet.ChartObjects
‘Using With and End with method to execute the required code, till the FOR NEXT loop is completed With chart.Parent
‘ change the numbers in the below brackets (2) to change the size of the chart. The number reflect the chart size in inches
chart.Height = Application.InchesToPoints(2)
chart.Width = Application.InchesToPoints(4)
End With
Next
MsgBox “All charts in the active sheet are resized !”
End Sub
–Code end
Using the above code we can resize all charts in active sheet to same sizes. If you observe above code, there are two lines which decide the size of charts and we only need to change the numbers between brackets (x). In above code, we have option to resize the size in inches. If you are not comfortable with inches, we can tweak the above code by using centimeters (cm). Observe below two lines of codes to tweak.
So, now we will be able to use the above code to resize all charts on sheet. If we want to use the above code to resize all charts in all worksheets within a workbook, we can further write a FOR NEXT loop to run for each worksheet and execute the above code for each worksheet. Check this link on how to write FOR NEXT Loop.
Thank you for reading. Happy learning !
Please write comments if you like my posts and also write if you want me to post solutions on any problems which you face on excel.
This function is most of the time used, when you need to determine whether the value is True or False and based on that condition, show some output or execute some action. We will learn how to use this function using few examples.
Below is the standard syntax used by IF function: =IF(logical_test, value_if_true, value_if_false)
below is the break up and details of the above syntax:
logical_test: here we need to mention the logic that we are going to test. for e.g. if we need to find out if a cell value is greater than 100 (>100), then True else False. We can also test for text value, e.g. if the cell value contains INDIA, then True else False.
value_if_true: if our logical test is True, whatever value we write here, will be the output. e.g. =if(A2>100,”Yes”) here if the value in cell A2 is greater than 100, then the output value will be Yes, else the output value will be False.
value_if_false: if our logical test is False, whatever value we mention here, will show in output. e.g, =if(A2>100,”Yes”,”No”) here if the value in cell A2 is less than 100, then the output value will be No, else the output value will be Yes.
Now we know what exactly the syntax of IF function means. Using this function we can check multiple cell values, based on our logical conditions and use multiple IF conditions, which we call as Nested IF formula. There is limit to use nested IF, which is 64 nested conditions can be tested in single formula.
Lets use one example to test the IF formula. In below example we have 10 employees, for whom we need to find out who all are eligible for Bonus. The bonus eligibility criteria is, if productivity score is greater than 80 then Yes, else no.
The formula which we have used in above example, under column Eligible for Bonus is:
=IF(productivity score>80,”Yes”,”No”), while the productivity score is the cell reference, see below image, in our case the productivity score is starting from cell C5.
There might be a requirement, you have built a VBA macro or excel vba tool and it will be used by a group of people or different teams. While, once you have delivered your macro or tool, you need to track the usability. In order to track it, we will need how many users are using your macro or tool, how frequently they are using your macro or tool, in this case we will need date time stamps too.
We will first learn how to get the login user name, whoever is the current logged in user in the system. We will try this first in excel sheet and once we know, we will write a macro which will create a text file and write the usage details on every execution or opening of the file.
There is inbuilt function in Excel vba to get user name which is : Environ(“UserName”)
Below is the vba code, which we will use to get logged in user id:
–Code start
Sub UserName()
‘declare GetName as string variable, we will use this to assign the login id Dim UserName As String
‘assign the user id to our variable GetName
UserName = Environ(“username”)
‘write a message box code to display a pop-up message to show the line mentioned below, with the logged in user name
MsgBox “Logged in User Name is : ” & UserName
End Sub
–Code End
Now we can test and run the code, we will receive a msgbox screenshot as shown below.
Now that we have tested the above code. We now know how to get the logged in or current user id. We will now write a macro which will create a log using the above code.
To create a log we will have to create a macro, which will create a text file and keep writing lines, whenever the macro is used. For this we will create a sub procedure, which will run along with above code.
–Code start
Sub UserName()
Dim UserName As String
UserName = Environ(“username”)
MsgBox “Logged in User Name is : ” & UserName
‘I have removed all green comments from above code, rest is same. The only change is below line, to run as sub procedure.
Logtracker UserName & “|UserNameMacro|” & Date & ” ” & Time
‘Below are details for above line: ‘Logtracker = sub procedure name ‘UserName = the variable with log in user id ‘ UserNameMacro = the macro name, to track ‘Date = will provide current date ‘ Time = will provide current time ‘ we are using & to concatenate and | as separator
End Sub
–Code end
Below is the sub procedure code to create text file and keep appending lines, whenever we run the above macro.
–Code start
Sub Logtracker(LogMessage As String)
‘declare the path to save file and create log Const LogFileName As String = “C:\Users\xyz\Desktop\tracker_log.txt”
‘declare FileNum as integer, which will be used check Log File Dim FileNum As Integer
‘ assign next file number, on every run
FileNum = FreeFile
‘Below code creates the file if it doesn’t exist. If exist then opens the file to write the log Open LogFileName For Append As #FileNum
‘Below code will write our log message and append at the end in text file Print #FileNum, LogMessage
‘Below code will close the file Close #FileNum ‘ close the file
End Sub
–Code end
Now try to run the macro UserName() and test the working of above codes. There will be a text file created and saved on the path we have mentioned, in above example it is on desktop. Below is the screenshot of the text file tracker_log.txt, for reference.
prassann.daphal|UserNameMacro|8/22/2018 9:30:44 AM
prassann.daphal|UserNameMacro|8/22/2018 9:30:47 AM
prassann.daphal|UserNameMacro|8/22/2018 9:34:41 AM
prassann.daphal|UserNameMacro|8/22/2018 9:34:44 AM
prassann.daphal|UserNameMacro|8/22/2018 9:34:55 AM
prassann.daphal|UserNameMacro|8/22/2018 9:37:11 AM
prassann.daphal|UserNameMacro|8/22/2018 9:37:25 AM
Thank you for reading ! Happy Learning !
Please write feedback comments if you like my posts and also let me know if you want me to write any specific articles which want to learn or need help on.
This is for people who write the automation macros in VBA code to execute multiple tasks. We will talk here about two examples where you will need to deactivate the alerts.
Example 1: We have created a web crawler macro, which extracts or downloads data from website. Here every time you refresh or land on the website i will show some pop-up messages or alerts or shows different screens through which the macro is executing the tasks.
Example 2: we have a macro which is running on excel application. which opens different worksheets to process the data. During the process we can see multiple sheets activated and the programmed tasks running on screen.
Let’s say you don’t want to see all what is happening on screen or in case you are creating the macro for some end user / customer and that user doesn’t want to know what is happening at the background, he just needs output. In this case we can just use 2 lines of code, which will help use to disable the Alerts and any Screen updates, till the macro completes all it’s tasks.
I have written below sample code, which can be used to check the working of the disable alerts and disable screen updating functions. I am using the example to 2 from above (running macro in Excel Workbook) for ease of understanding. This code starts writing 1 to 2000 by activating each sheet in the workbook using the FOR LOOP code.
Below are the only two lines of code, which work to disable Display Alerts and Screen updating.
Run the code. You will observe the screen changing from Sheet 1 activation to Sheet 3 and the numbers written from 1 to 2000 on each screen
Now to test the display alerts and screen updating remove the below commented code and run again. code with comments ‘Application.DisplayAlerts = False ‘Application.ScreenUpdating = FalseCode without comments
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Now you will not see any movements on the screen. Instead you will just see the final message box saying Your macro is completed !
–Code start
Sub deactivateAlerts()
‘ remove the ‘ (comment) to activate below code
‘Application.DisplayAlerts = False ‘Application.ScreenUpdating = False
Sheets(“Sheet1”).Select
Range(“A1”).Select For i = 1 To 2000
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select Next i
Sheets(“Sheet2”).Select
Range(“A1”).Select For i = 1 To 2000
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select Next i
Sheets(“Sheet3”).Select
Range(“A1”).Select For i = 1 To 2000
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select Next i
‘Please add the below code before ending the macro. Else it will keep the alerts and screen updating disabled for all other codes too.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox “Your macro is completed !”
End Sub
–Code end
Thank you for reading. Happy learning !
Please write comments, if you like
Please write comments if you like my posts and also write if you want me to post solutions on any problems which you face on excel.
You must have come across this challenge, when you copy paste some data from websites, from emails or any documents in excel. e.g. if you copy paste the text www.pdexcel.com in excel file, it will be copied as hyperlink, as it is website address. Even if you type email id in excel cell and press enter, the text will turn into hyperlink.
In our example, we are using a sample data to check if our code will remove the hyperlinks.
Copy paste the above table in excel file, we can see that email id column has hyperlink, and whenever you will try to select any of the email id column cells, the click will open a new email compose item (if you are using any default connected email application for emails). Below is the screenshot from Microsoft outlook:
To avoid the above action, we will need to remove the hyperlinks and to do it manually, we will need to select all cells, right click each cell and select remove hyperlinks.
In our sample data there are only 4 records and we have structured data. But when we have large data and we need to search and remove hyperlinks, it will become difficult to remove all hyperlinks. Also, it will be a very time consuming task.
To avoid all this, we can just create a small macro, which can be used to removed all hyperlinks within the sheet or entire workbook with just one click of mouse :-).
Check out below vba codes and try them in your excel.
–Code start
Sub removeallhyperlinks()
‘the below code will remove all hyperlinks from the active excel sheet on screen
ActiveSheet.Hyperlinks.Delete
End Sub
–Code end
That’s it, a single line code to remove all hyperlinks in the worksheet :-).
This code can help you to achieve different tasks to copy and paste files using the automated process. Below are few examples where this code can be used:
Copy and paste files from one location to other as a backup
Copy and paste processed files from local desktop or drive to Customer location or FTP location
Schedule a copy paste macro, for trigger as per specific requirement of job
Copy paste file, in order to process the copied file and keep original as backup
…and you can write as many, as per your job requirements.
You can directly copy paste the below code in your VBA module and use it by changing few variables. Check out the comments in Green, wherever you would need to tweak the code to get the desired output.
—Code start
Sub Copy_File()
‘ Declare variables we are going to use. All variables are declared as string, as we are going to assign text values to them Dim filename As String Dim Source As String Dim Destination As String
‘Dim means dimension, which we are using to declare the variables
‘Next step will be to set values to our declared variables
filename = “Desert_backup”
Source = “C:\Test\Desert.jpg”
Destination = “C:\Test\Backup\” & filename & “.jpg”
‘We have now set values or assigned values to our variables. In the example above, we are trying to copy a file named Desert.jpg, which is saved in Test folder in my C drive to folder named Backup under Test folder in my C drive. Refer the above path in the code i.e. Source and Destination.
‘Also while doing so, we are renaming the back up file from Desert.jpg to Desert_backup.jpg.
‘Below is the final code, which will copy the file from source to destination, with new name
FileCopy Source, Destination
‘ We have inbuilt FileCopy VBA function to complete the copy paste process. The FileCopy function’s standard script is FileCopy(Source, Destination) which means, this function copies file from one folder or directory to other
‘ Remember if the file you are trying to copy is open, then the macro will give a run-time error. i.e. in order to copy the file, the file needs to be closed.
End Sub
—Code end
Just in case if you are planning to use the code to take the server back up daily or weekly once, so instead of renaming file to _backup, you can just add a timestamp at the end of file name, in order to save the file with the specific date and time stamp of the back up day.
Check below code in which we are using date time stamp to rename and save the file. The only different from above code is the tweak done in the Destination string.
—Code start
Sub Copy_File_dt ()
‘ Declare variables Dim filename As String Dim Source As String Dim Destination As String
‘Here we have used 2 inbuilt VBA functions Now() and Format. Now() function will get you the current value of date and time. While the Format function helps you to format the date time, in whatever format we need as output
‘Final code for file copy
FileCopy Source, Destination
End Sub
—Code end
In order to run the above code for multiple files copy paste, you will need to tweak the above code, to assign dynamic values to variables and add a Loop code to run till all the files are copied.
You can also get a list of files that you want to copy and also track how many files actually got copied, in order to verify or validate the output. But right now i want to keep this post simple, just to help you understand the simple code, on how to copy paste the file.
To understand how to use If else and case statements, i have create a small program code, which checks your personality based on your birth date numbers. So have fun while learning :-).
Code:
Sub checkpersonality()
‘Define all variables we are going to use in below code Dim bdate As Integer, bmonth As Integer, byear As Integer, total As Integer Dim calca As Integer, calcb As Integer, calcc As Integer, calcd As Integer, calctotal AsInteger Dim calce As Integer, calcf As Integer, yourbnum As Integer
‘On error, e.g. if incorrect dates are entered the below code will take you to the error message: in the code and exit the execution
On Error GoTo error_message
‘Use InputBox function to collect the day, month and year of the user
bdate = InputBox(“Enter your Day of birth (DD):”)
bmonth = InputBox(“Enter your Month of birth (MM):”)
byear = InputBox(“Enter your Year of birth (YYYY):”)
‘Calculate total from your birth date, learnt this from astrology website 🙂
total = bdate + bmonth + byear
‘below are few more calculations on the number from above total
calca = Left(total, 1)
calcb = Right(Left(total, 2), 1)
calcc = Right(Left(total, 3), 1)
calcd = Right(total, 1)
calctotal = calca + calcb + calcc + calcd
‘below is use of If Else statement to get a final number from all calculations
If calctotal >= 10 Then
calce = Left(calctotal, 1)
calcf = Right(calctotal, 1)
yourbnum = calce + calcf Else: yourbnum = calctotal End If
‘After all the calculations above, we will get a final number as output. This number will decide what is your personality trait and based on that a message box will pop-up from below 1 to 9 cases. Select Case yourbnum
Case 1
MsgBox “Your Birth number is ” & yourbnum & ” and You are The originator” & vbCrLf & vbCrLf & “1s are originals. Coming up with new ideas and executing them is natural. Having things their own way is another trait that gets them as being stubborn and arrogant. 1s are extremely honest and do well to learn some diplomacy skills. They like to take the initiative and are often leaders or bosses, as they like to be the best. Being self-employed is definitely helpful for them.” & vbCrLf & vbCrLf & “Lessons to learn: Others ideas might be just as good or better and to stay open minded.” & vbCrLf & vbCrLf & _
“Famous 1s: Tom Hanks, Robert Redford, Hulk Hogan, Carol Burnett, Wynona Judd, Nancy Reagan, Raquel Welch, Prassann Daphal”, vbInformation
Case 2
MsgBox “Your Birth number is ” & yourbnum & ” and You are The peacemaker” & vbCrLf & vbCrLf & “2s are the born diplomats. They are aware of others needs and moods and often think of others before themselves. Naturally analytical and very intuitive they don’t like to be alone. Friendship and companionship is very important and can lead them to be successful in life, but on the other hand they’d rather be alone than be in an uncomfortable relationship. Being naturally shy they should learn to boost their self-esteem and express themselves freely and seize the moment and not put things off.” & vbCrLf & vbCrLf & _
“Famous 2s: President Bill Clinton, Madonna, Whoopi Goldberg, Thomas Edison, Wolfgang Amadeus Mozart”, vbInformation
Case 3
MsgBox “Your Birth number is ” & yourbnum & ” and You are The life of the party” & vbCrLf & vbCrLf & “3s are idealists. They are very creative, social, charming, romantic, and easygoing. They start many things, but don’t always see them through. They like others to be happy and go to great lengths to achieve it. They are very popular and idealistic. They should learn to see the world from a more realistic point of view.” & vbCrLf & vbCrLf & _
“Famous 3s: Alan Alda, Ann Landers, Bill Cosby, Melanie Griffith, Salvador Dali, Jodi Foster”, vbInformation
Case 4
MsgBox “Your Birth number is ” & yourbnum & ” and You are The conservative” & vbCrLf & vbCrLf & “4s are sensible and traditional. They like order and routine. They only act when they fully understand what they are expected to do. They like getting their hands dirty and working hard. They are attracted to the outdoors and feel an affinity with nature. They are prepared to wait and can be stubborn and persistent. They should learn to be more flexible and to be nice to themselves.” & vbCrLf & vbCrLf & _
“Famous 4s: Neil Diamond, Margaret Thatcher, Arnold Schwarzenegger, Tina Turner, Paul Hogan, Oprah Winfrey”, vbInformation
Case 5
MsgBox “Your Birth number is ” & yourbnum & ” and You are The non-conformist” & vbCrLf & vbCrLf & “5s are the explorers. Their natural curiosity, risk taking, and enthusiasm often land them in hot water. They need diversity, and don’t like to be stuck in a rut. The whole world is their school and they see a learning possibility in every situation. The questions never stop. They are well advised to look before they take action and make sure they have all the facts before jumping to conclusions.” & vbCrLf & vbCrLf & _
“Famous 5s: Abraham Lincoln, Charlotte Bronte, Jessica Walter, Vincent Van Gogh, Bette Midler, Helen Keller, Mark Hamil.”, vbInformation
Case 6
MsgBox “Your Birth number is ” & yourbnum & ” and You are The romantic” & vbCrLf & vbCrLf & “6s are idealistic and need to feel useful to be happy. A strong family connection is important to them. Their actions influence their decisions. They have a strong urge to take care of others and to help. They are very loyal and make great teachers. They like art or music. They make loyal friends who take the friendship seriously. 6s should learn to differentiate between what they can change and what they cannot.” & vbCrLf & vbCrLf & _
“Famous 6s: Albert Einstein, Jane Seymour, John Denver, Meryl Streep, Christopher Columbus, Goldie Hawn”, vbInformation
Case 7
MsgBox “Your Birth number is ” & yourbnum & ” and You are The intellectual” & vbCrLf & vbCrLf & “7s are the searchers. Always probing for hidden information they find it difficult to accept things at face value. Emotions don’t sway their decisions. Questioning everything in life, they don’t like to be questioned themselves. They’re never off to a fast start, and their motto is slow and steady wins the race. They come across as philosophers and being very knowledgeable, and sometimes as loners. They are technically inclined and make great researchers uncovering information. They like secrets. They live in their own world and should learn what is acceptable and what not in the world at large.” & vbCrLf & vbCrLf & _
“Famous 7s: William Shakespeare, Lucille Ball, Michael Jackson, Joan Baez, Princess Diana”, vbInformation
Case 8
MsgBox “Your Birth number is ” & yourbnum & ” and You are The big shot” & vbCrLf & vbCrLf & “8s are the problem solvers. They are professional, blunt and to the point, have good judgment and are decisive. They have grand plans and like to live the good life. They take charge of people. They view people objectively. They let you know in no uncertain terms that they are the boss. They should learn to exude their decisions on their own needs rather than on what others want.” & vbCrLf & vbCrLf & _
“Famous 8s: Edgar Cayce, Barbra Streisand, George Harrison, Jane Fonda, Pablo Picasso, Aretha Franklin”, vbInformation
Case 9
MsgBox “Your Birth number is ” & yourbnum & ” and You are The performer” & vbCrLf & vbCrLf & “9s are natural entertainers. They are very caring and generous, giving away their last dollar to help. With their charm, they have no problem making friends and nobody is a stranger to them. They have so many different personalities that people around them have a hard time understanding them. They are like chameleons, ever changing and blending in. They have tremendous luck, but also can suffer from extremes in fortune and mood. To be successful, they need to build a loving foundation.” & vbCrLf & vbCrLf & _
“Famous 9s: Albert Schweitzer, Shirley McLaine, Harrison Ford, Jimmy Carter, Elvis Presley”, vbInformation
Case Else
MsgBox “I didn’t knew this, You are not HUMAN !”, vbCritical End Select Exit Sub
‘ you have an option to change the below message, anything that you want to display on error
error_message:
MsgBox “You entered something wrong !” & vbCrLf & vbCrLf & “You need to enter your Birth Date ?”, vbCritical
End Sub
In above code, you have seen vbCrLf used between text messages. Here Cr means carriage return, while Lf means line feed, usign vbCrLf between text, it will create a new line for next text.
You can also use vbNewLine instead of vbCrLf, more easy to remember :-).
Below are the screen shots of the above program in action.
Open new excel file
Copy paste above code from sub to End sub in new VBA module
Save the file as Personality Test
Press Alt + F8 to run the code
Select checkpersonality macro, if you have multiple codes in same sheet
Run
we will take example of my birth date. 23-01-1984. First input your date of birth in first input box in DD format. In my case it will be 23.
In second input box, enter your birth month in MM format. In my case it will be 01.
In third input box, enter your birth year in YYYY format. In my case it will be 1984.
Based on your birth date, out program will calculate the personality and show the below output.
If you don’t enter anything in the input box or click cancel on input box, then it will show the error message.
Here the term loop means, the line of written code will be repeated till the specific condition is met. In excel VBA there are different ways to run code in loops, in this article we will learn one of the method, which is For Next loop.
First we will write simple code, which will pop-up the message box for numbers 1 to 10.
2. Add the below lines of codes, in any of the vba module:
‘ ForLoop is the name of the macro which will appear when you press ALt + F8 to run macro Sub ForLoop()
‘Define n variable as integer Dim n As Integer
‘ Assign the values to variable n For n = 1 To 10
‘MsgBox will give a pop up message with value assigned to variable n
MsgBox n
‘Next will loop back to start and add 1 to value of n, e.g. during first run of code value of n will be 1 and on execution of the Next, the value of n will become n = n + 1 i.e. n=2 Next
‘once the n value will become 10, the code will exit the execution with End Sub End Sub
3. Run the code using Alt + F8 and choose ForLoop from the list, if you have multiple macros
4. Below is the screenshot of the third Next statement executed in the above code:
Using the loop we can further write a code, which will help us to provide a message box pop-up to identify odd or even numbers.
Copy paste the below code in the VBA module:
Sub EvenOdd() For n = 1 To 10 If n Mod 2 = 0 Then
MsgBox n & ” is Even Number” Else
MsgBox n & ” is Odd Number” End If Next n End Sub
When you will execute the above code, you will get message box pop-up saying n (value of n), if it is even number or odd number. Below is the screen shot references for both even and odd numbers.
These message box pop-ups will continue till the n variable is set to 10 using For Next loop.
In above code, we have used the combination of For Next and If Else statements. e.g. If n is divisible by 2, then the number will be even, else odd. In this way, the loops can be used in different combinations in VBA code to achieve results.
If you are a excel vba programmer, you would definitely want to know, that how you can use the Auto filter option using code in macro. As you might be required to provide user with custom filtered data as final output.
We will first have a look at code which will check if the auto filter is already applied in the sheet. If the auto filter already applied then the code will exit (i.e. not execute), else it will apply the filter to the selected or available data within range.
Sub pdCheckAutoFilter() If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range(“A1”).AutoFilter Else End If End Sub
When executed the above macro will check , if the filters are already applied if yes, then it will exit the code and if not applied then it will filter the available data in current sheet data range.
Now you know how to check and apply the AutoFilter using vba code. Next we will learn how to remove this applied VBA filter.
By just tweaking the above autofilter check and apply code, we can write a code to check and remove Auto Filter
Sub pdCheckRemoveAutoFilter() If Not ActiveSheet.AutoFilterMode Then Else
Worksheets(“Sheet1”).AutoFilterMode = False End If End Sub
If there is no Autofilter applied to the data, then the above macro will just stop execution. While in case the Autofilter is applied then, the filters will be removed in Sheet1 data.
The above two macros can be used as a sub tasks within a program in order to achieve different outputs as per the requirements.
