Check if there are charts on a given worksheet and if yes, delete them(VBA-Excel)

'This code checks if there are any charts on a specific sheet and, if yes, deletes them:

Sub know_if_there_are_charts()
Dim ws As Worksheet

If ws.ChartObjects.Count <> 0 Then
ws.ChartObjects.Delete

End If

End Sub

Divide the axis values by 1000 (VBA-Excel)

This code changes the unit of a given axis of a chart to thousand, i.e. divide the units by 1000

Sub Divide_axis_values_by_1000()
    ActiveChart.Axes(xlValue).DisplayUnit = xlThousands
End Sub

Installing Linux in Windows 8

As I told on this post: "The selected languages", one of my selected languages to learn how to program was Unix. However it took me a while to finally find a good Unix run-environment for my windows pc.

On the first glance, the best option would be the installation of Linux OS on a virtual box. That's why I have been trying and trying again to make the oracle virtual box work on my windows 8 but it has been in vain.
As my thirst for learning how to program in Unix is kind of out of control LOL LOL LOL, I found a better option: the Cygwin Terminal.

If you have the windows 8 and want to learn Unix I think that the installation of Cygwin is definitely a very good option. It is very fast and allows almost all the commands that Linux have. Try it!

Please check this video that I did to explain you step by step how to install it. The video has Portuguese audio but there are subtitles in English.

How to enable the content of a workbook?(Excel-VBA)

Each time we open with VBA an excel file, coming from another person, one of the most annoying things is the fact of having to select the "enable content" option.

However, did you know that you can get rid of that security warning directly in VBA? Just write this following code,defining as true the update of the links (the argument of Open method), so for example:

With the code above we open the file1.xls and we get rid of the update links security warning! ;) A very useful instruction in my point of view!

How to change the background color of a cell in VBA? (VBA-EXCEL)

The easiest way to change the background color of a cell in VBA is using the color index, so if we want to change the color of cell A1 to red we should write:

Sub change_color()

Activesheet.range("a1").interior.colorindex=3

End sub

I leave here to you the color palette with the respective numbers of the colors, in case you want to color a cell but not with red :)

How to change the chart type of just one of the series on a chart.-VBA(excel)

Sometimes we have a chart with different variables, if we want the different variables to appear on the same chart but with a different chart type we can do it on VBA:

Just select the chart, write this code and run it:

Sub Chart()

ActiveChart.ChartType =xlXYScatterSmooth
ActiveChart.SeriesCollection(1).ChartType = xlXYScatterLines

End Sub

In this example, we have as result a line to show the evolution of the variable 1, and just points to show the behaviour of the rest of the series. Easy, isn't it?

Set a Range (VBA-Excel)

One of the basic steps to solve a lot of problems on Excel is to set a range in order to use it and reuse it as many times as you like during a procedure (Macro).

Sub SetaRange()

Dim Rng as Range

Set Rng=Range("A1:C10")

End Sub

With the code above you set a range named Rng (you can choose a different name if you prefer), consisting of all the cells from A1 to C10 on the Active Sheet.

The Remainder of a division in Python

Did you know that there is an operator in Python to find the remainder of a division? How awesome is that?!? It is the greatest idea that someone have ever had!!!

So if you want to know the remainder of a division you just write the dividend and the divisor and use the symbol (%) between them et... voilá!!!!!!!! The result is the remainder of the division of those numbers :) :)

Exponentiation in Python

If you using Python as calculator, probably you have already tried to use "^" as exponentiation, because you are used to do it in VBA, and it did not work.

So, use "**" as exponentiation and you will get the right result.

Now you are wondering: "Isn't it two times the multiplication symbol?!?"
In order to answer you: " Yes, it is! But do not worry in several programming languages this two times multiplication symbol acts as exponentiation instead of the instinctive "^" ".

Copy and paste in Excel (VBA-Excel)

Copying and pasting are very easy tasks to execute on excel with VBA. For example if you want to copy the column D to paste on the column A, you can use the simple code below:



Sub copy_and_paste()
Range("D1").EntireColumn.Copy Range("A1").EntireColumn
End Sub




P.S. Once that you did not specify the worksheet, excel will assume it as the activesheet, if you want to perform the same task on a different sheet do not forget to identify it before the Range ;)

An Economist Programming

Each day I am happier with the decision I took about learning programming languages. Few days ago I realized that sometimes managers and economists around the world request programmers/developers to do some tasks and the result is not the expected one.

 Who is guilty?:

 "The managers because they do not know how the program works and keep asking impossible tasks"-says the programmer

 "The programmers because they do not understand what is the program for, they do not know what the client and the investors want to see from our company"-says the economist.

This battle is endless, and to say the truth... none of them is guilty! By one side, normally the economists do not understand programming languages, and are requesting the programmer to do something impossible or useless (sometimes people request programs to do exactly the same they can do with a click on GUY).By the other side, normally developers have an engineering/technology or science background which means that normally they do not understand the "business language" that economists speak.

As economist you are encouraged to follow the profit, you are taught by Adam Smith that exists and invisible hand called market which brings everyone to the equilibrium (position where all always wanted to be) if each one is just following its own profit. You are born to make the company profitable and happier.

 As programmer you are encouraged to follow the technological development, you are taught that spending 10 years of your life developing a program is worth once that it will enable the future generations to save much more than 10 years in repetitive tasks. You are born to make the life in the company easier and happier.

 The solution is to create an "hybrid" person:

The role of people like me (economist interested in Programming) is becoming more and more important inside the companies because they build a bridge between the Managers and the Programmers, reducing or even eliminating the communication problems.

P.S In spite of not having any guilty on this programmer VS manager battle, I must refer that we actually see more engineers taking extra courses in Business Administration than economists learn how to program. So we have to recognize that programmers try a little bit harder to bring a solution ;)

Convert cm in ppt (VBA-PP and Word)

To write my last code for powerpoint(see it here) I had to convert cm in pt, in order to define the position of each picture on the slide.

If you also need to convert these measurement units you might find the following link very useful:

http://www.asknumbers.com/CentimetersToPointsConversion.aspx

If you don't know what pt is, in simple words it stands for point and is the smallest typographic unit also used by microsoft office on powerpoint and on word.
If you want, for example, to define in VBA the distance you want your picture from the top edge or left edge you must measure it in pt instead of cm or inches ;)

How to insert several pictures, with different scales and positions, on a new slide? (VBA-PP)

Sub insertimages()

Dim path1, path2, path3, path4 As String

Dim scale1, scale2, scale3, scale4 As Single

Dim perwidth1, perheight1, perwidth2, perheight2, perwidth3, perheight3, perwidth4, perheight4 As Single

Dim pic As Shape

Dim a1, a2, a3, a4, b1, b2, b3, b4 As Long

'Pictures'

path1 = "C:\Users\CS\Desktop\foto1.jpg"

path2 = "C:\Users\CS\Desktop\foto2.jpg"

path3 = "C:\Users\CS\Desktop\foto3.jpg"

path4 = "C:\Users\CS\Desktop\foto4.jpg"

'scale of each picture in percentage'

scale1 = 0.1

scale2 = 0.1

scale3 = 0.1

scale4 = 0.1

'position of each picture on the slide'

perwidth1 = 0

perheight1 = 0

perwidth2 = 0.3

perheight2 = 0.3

perwidth3 = 0.5

perheight3 = 0.5

perwidth4 = 0.7

perheight4 = 0.7

''''''''''''''''''''''''''''''''''''

'convert it in ppt'

a1 = perwidth1 * 958.11023646

a2 = perwidth2 * 958.11023646

a3 = perwidth3 * 958.11023646

a4 = perwidth4 * 958.11023646

b1 = perheight1 * 541.41732297

b2 = perheight2 * 541.41732297

b3 = perheight3 * 541.41732297

b4 = perheight4 * 541.41732297

ActivePresentation.Slides.Add 1, ppLayoutBlank

Set pic = ActivePresentation.Slides(1).Shapes.AddPicture(path1, False, True, a1, b1, -1, -1)

ActivePresentation.Slides(1).Shapes(1).ScaleWidth scale1, msoTrue

ActivePresentation.Slides(1).Shapes(1).LockAspectRatio = msoTrue

Set pic = ActivePresentation.Slides(1).Shapes.AddPicture(path2, False, True, a2, b2, -1, -1)

ActivePresentation.Slides(1).Shapes(2).ScaleWidth scale2, msoTrue

ActivePresentation.Slides(1).Shapes(2).LockAspectRatio = msoTrue

Set pic = ActivePresentation.Slides(1).Shapes.AddPicture(path3, False, True, a3, b3, -1, -1)

ActivePresentation.Slides(1).Shapes(3).ScaleWidth scale3, msoTrue

ActivePresentation.Slides(1).Shapes(3).LockAspectRatio = msoTrue

Set pic = ActivePresentation.Slides(1).Shapes.AddPicture(path4, False, True, a4, b4, -1, -1)

ActivePresentation.Slides(1).Shapes(4).ScaleWidth scale4, msoTrue

ActivePresentation.Slides(1).Shapes(4).LockAspectRatio = msoTrue

ActivePresentation.Slides.Range(1).Cut

ActivePresentation.Slides.Paste -1

End Sub

How to move a slide to the end of the presentation?(VBA-PP)

It is easy!Set it as the first slide of the presentation and then cut and paste it one position before (What?!? which position is before the first one?... The last one!! VBA is amazing... I know!! :))

So just do it like this:

Sub...()
....
ActivePresentation.Slides.Range(1).Cut
ActivePresentation.Slides.Paste -1
....
End Sub

Creating a presentation with N blank slides (VBA-PP)

Sub InsertNslides()
Dim n As Integer
Dim i As Integer

n = InputBox("How many slides?")

i = 0

Do Until i = n
i = i + 1
ActivePresentation.Slides.Add 1, ppLayoutBlank
Loop

End Sub

What do you think? Is it good for a beginner??

Why would a female learn how to program?

More than wondering why would an economist learn how to program, probably you are wondering why would a FEMALE economist learn how to program....

Your guess was right...I married an engineer, naturally! Although, I will learn VBA, because he does not know it (and I have the chance to be better than him in something around computers :) )

The selected languages...

To start my programming career I picked VBA ( of course every economist would like to use excel more efficiently) and Unix, because I just read that if you know how to program you will hate windows and love Linux (once that one day I will know how to program I am getting ready for that big moment).

The Reason...

I just realize that I would like to learn programming languages, in spite of having economics background.

Here, I will be writing on the first person how difficult is programming for an economist and sharing with the world my programming achievements (hopefully i will have some) along the way :)

P.S: If you find spelling mistakes just let me know, because I am not an English native speaker.