tag:blogger.com,1999:blog-67626292036265510952024-03-06T04:50:04.978+05:30Kannan's Tech Blog..VBA Programming - Tutorial & Discussion forum.Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.comBlogger253125tag:blogger.com,1999:blog-6762629203626551095.post-54711950506416721132012-10-08T11:44:00.000+05:302014-04-16T12:23:06.585+05:30VBA : Creating Custom Data typehere is a code to Create your own type of variable :<br />
<script type="syntaxhighlighter" class="brush: vb"><![CDATA[
'Creation of a variable type
Type guests
last_name As String
first_name As String
End Type
Sub variables()
'Declaration
Dim p1 As guests
'Assigning values to p1
p1.last_name = "Smith"
p1.first_name = "John"
'Example of use
MsgBox p1.last_name & " " & p1.first_name
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-91815600864847969852012-10-05T19:50:00.002+05:302012-10-05T19:50:19.239+05:30VBA Special cellsVBA special cells make your life easy, instead of writing multiple lines of code and looping the the entire range of cells, this special cell types select in less than a second.<br />
<br />
For more info visit msdn link below.<br />
<a href="http://msdn.microsoft.com/en-us/library/office/aa213567%28v=office.11%29.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/office/aa213567%28v=office.11%29.aspx</a><br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Sub SelectAllBlanks()
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.UsedRange.SpecialCells _
(xlCellTypeAllValidation).Select
ActiveSheet.UsedRange.SpecialCells _
(xlCellTypeFormulas).Select
ActiveSheet.UsedRange.SpecialCells _
(xlCellTypeVisible).Select
ActiveSheet.UsedRange.SpecialCells _
(xlCellTypeAllFormatConditions).Select
ActiveSheet.UsedRange.SpecialCells _
(xlCellTypeComments).Select
ActiveSheet.UsedRange.SpecialCells _
(xlCellTypeLastCell).Select
'xlCellTypeAllFormatConditions. Cells of any format
'xlCellTypeAllValidation. Cells having validation criteria
'xlCellTypeBlanks.Empty Cells
'xlCellTypeComments. Cells containing notes
'xlCellTypeConstants. Cells containing constants
'xlCellTypeFormulas. Cells containing formulas
'xlCellTypeLastCell. The last cell in the used range. Note this XlCellType will include empty cells that have had any of cells default format changed.
'xlCellTypeSameFormatConditions. Cells having the same format
'xlCellTypeSameValidation. Cells having the same validation criteria
'xlCellTypeVisible. All visible cells
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-88339971402574262702012-10-05T11:48:00.001+05:302012-10-05T12:08:51.455+05:304 VBA Course : Colors<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRksFz54d1N1ha0v4CVJrNG3oPQG2jSQOuKgWQv1rHqb9SV5WkK4weIIDE1BL6zfCZS8ZRa0D1iQvzvmWsFJIlSZmeOalbI5JCG3cmjxHg9dwvEZDuv-2fZ3IqPZpBpW0rul_VkWwBrUo/s1600/4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRksFz54d1N1ha0v4CVJrNG3oPQG2jSQOuKgWQv1rHqb9SV5WkK4weIIDE1BL6zfCZS8ZRa0D1iQvzvmWsFJIlSZmeOalbI5JCG3cmjxHg9dwvEZDuv-2fZ3IqPZpBpW0rul_VkWwBrUo/s1600/4.png" /></a></div>
<br />
Let's start by assigning a color to the text in A1.<br />
<br />
After adding <span class="b">Font.</span>, we get this result :<br />
<img alt="color - colors" class="relief" src="http://www.excel-pratique.com/en/vba/img_colors/color.png" title="" /><br />
<br />
There are two different ways that we can set the color : <span class="b">ColorIndex</span>, which has 56 colors, or <span class="b">Color</span> which makes it possible to use any color at all.<br />
<div class="p_top">
<br /></div>
<div class="p_top">
<u><b><span class="u">ColorIndex</span> :</b></u></div>
<br />
Here you can see the 56 colors that are available through <span class="b">ColorIndex</span> :<br />
<img alt="colorindex - colors" class="relief" src="http://www.excel-pratique.com/en/vba/img_colors/colorindex.png" title="" />
<br />
To set the color of our text to one of these 56, we should write :<br />
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;"> </span></code></div>
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;">Sub</span> example()<br />
<span style="color: green;">'Text color for A1 : green (Color num. 10)<br />
</span> Range(<span style="color: maroon;">"A1"</span>).Font.ColorIndex = 10<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div>
<br />
This code will give us the following result :<br />
<img alt="test2 - colors" class="relief" src="http://www.excel-pratique.com/en/vba/img_colors/test2.png" title="" />
<div class="mini i">
<br /></div>
<div class="mini i">
For versions of Excel lower than 2007 : using ColorIndex is preferable to using Color.</div>
<div class="p_top">
<br /></div>
<div class="p_top">
<span class="u">Color</span> :</div>
Here is a similar example in which we use <span class="b">Color</span> :<br />
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;"> </span></code></div>
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;">Sub</span> example()<br />
<span style="color: green;">'Text color for A1 : RGB(50, 200, 100)<br />
</span> Range(<span style="color: maroon;">"A1"</span>).Font.Color = RGB(50, 200, 100)<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div>
<div id="clic">
<br /></div>
<div id="clic">
<b>In this case, the color is : <span class="b">RGB(50, 200, 100)</span>.
</b><br />RGB stands for Red-Green-Blue, and the numerical values go from 0 to 255 for each color.</div>
A few examples of colors so that you can understand this better :<br />
<ul>
<li>RGB(0, 0, 0) : <span class="b">black</span></li>
<li>RGB(255, 255, 255) : <span class="b">white</span></li>
<li>RGB(255, 0, 0) : <span class="b">red</span></li>
<li>RGB(0, 255, 0) : <span class="b">green</span></li>
<li>RGB(0, 0, 255) : <span class="b">blue</span></li>
</ul>
Luckily, there are lots of easy ways to find the RGB values for colors. Here's one (click on the image) :<br />
<br />
<a href="http://www.excel-pratique.com/en/vba/colors.php#clic" title="Click here"><img alt="colors" src="http://www.excel-pratique.com/en/vba/img_colors/colors.png" style="margin-bottom: 0px;" /></a>
<br />
Choose the color that you want from this utility and just copy the three values into the
<span class="b">RGB(<span style="color: #fa232a;">red_value</span>, <span style="color: #0ed536;">green_value</span>, <span style="color: #2064fc;">blue_value</span>)</span>.<br />
So to change our text color to the violet above, we should use the following code :<br />
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;"> </span></code></div>
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;">Sub</span> example()<br />
<span style="color: green;">'Text color for A1 : RGB(192, 24, 255)<br />
</span> Range(<span style="color: maroon;">"A1"</span>).Font.Color = RGB(192, 24, 255)<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div>
<br />
This code will produce the following result :<br />
<img alt="test - colors" class="relief" src="http://www.excel-pratique.com/en/vba/img_colors/test.png" title="" />
<div class="mini i">
<br /></div>
<div class="mini i">
For versions of Excel lower than 2007 : the number of
colors is limited (the closest available color to the RGB values will be
used).</div>
<div style="margin-bottom: 5px; margin-top: 5px;">
<div id="div-gpt-ad-1338510188641-0" style="height: 90px; width: 728px;">
<div id="div-gpt-ad-1338510188641-0_ad_container">
<ins style="border: 0px none; display: inline-table; height: 90px; position: relative; width: 728px;"><ins style="border: 0px none; display: block; height: 90px; position: relative; width: 728px;"></ins></ins></div>
</div>
</div>
<div class="p_top">
<b><span class="u">Add colored borders</span> :</b></div>
We will now create a macro that adds a border to the active cell using <span class="b">ActiveCell</span>.<br />
<br />
The border will be heavy and red :<br />
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;"> </span></code></div>
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;">Sub</span> example()<br />
<span style="color: green;">'Border weight<br />
</span> ActiveCell.Borders.Weight = 4<br />
<span style="color: green;">'Border color : red<br />
</span> ActiveCell.Borders.Color = RGB(255, 0, 0)<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div>
<br />
Result :<br />
<img alt="border - colors" class="relief" src="http://www.excel-pratique.com/en/vba/img_colors/border.png" title="" />
<br />
To apply this effect to many cells at once, we can use the <span class="b">Selection</span> command:
<br />
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;"> </span></code></div>
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;">Sub</span> example()<br />
<span style="color: green;">'Border weight<br />
</span> Selection.Borders.Weight = 4<br />
<span style="color: green;">'Border color : red<br />
</span> Selection.Borders.Color = RGB(255, 0, 0)<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div>
<div class="p_top">
<br /></div>
<div class="p_top">
<span class="u">Add background color to the selected cells</span> :</div>
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;"> </span></code></div>
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;">Sub</span> example()<br />
<span style="color: green;">'Add background color to the selected cells<br />
</span> Selection.Interior.Color = RGB(174, 240, 194)<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div>
<br />
Result :<br />
<img alt="background - colors" class="relief" src="http://www.excel-pratique.com/en/vba/img_colors/background.png" title="" />
<div class="p_top">
<br /></div>
<div class="p_top">
<span class="u">Add color to the tab for a worksheet</span> :</div>
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;"> </span></code></div>
<div class="code_vba">
<code><span style="color: navy; font-weight: bold;">Sub</span> example()<br />
<span style="color: green;">'Add color to the tab for "Sheet1"<br />
</span> Sheets(<span style="color: maroon;">"Sheet1"</span>).Tab.Color = RGB(255, 0, 0)<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div>
<br />
Result :<br />
<img alt="tab - colors" class="relief" src="http://www.excel-pratique.com/en/vba/img_colors/tab.png" title="" />Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-67923748437044733102012-10-03T13:19:00.002+05:302012-10-05T11:49:43.721+05:303 VBA Course : Properties<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwog48RCeobDkSW4IlhpWyLLn5hAKUz9w8IH_KG29IVSPkAaa4z74ERvDJD-4Rt6e6CW7Gxl8M-SSpG4OVWB678GwJLvKnTnyg8kmdMJTILeMGac4X-rce2dlbz_OUugqxQSGH_-0LLEs/s1600/3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwog48RCeobDkSW4IlhpWyLLn5hAKUz9w8IH_KG29IVSPkAaa4z74ERvDJD-4Rt6e6CW7Gxl8M-SSpG4OVWB678GwJLvKnTnyg8kmdMJTILeMGac4X-rce2dlbz_OUugqxQSGH_-0LLEs/s1600/3.png" /></a></div><br />
In this exercise, we will write VBA code that modifies the content and appearance of cells and worksheets.<br />
<br />
First open the editor, add a module, copy the macro below into it, and link it to a formula button (Previous lesson)<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Incomplete Macro<br />
</span> Range (<span style="color: maroon;">"A8"</span>)<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div>We want to modify cell A8 at the beginning of this macro.<br />
<br />
To display the list of possible things that can be associated with the Range object, add a period after <span class="b"> </span><br />
<span class="b">Range ("A8")</span> :<br />
<br />
<img alt="list - properties" class="relief" src="http://www.excel-pratique.com/en/vba/img_properties/list.png" title="" /> <br />
The editor will now display the various possibilities ...<br />
<br />
In this first example, click on "Value" and then on the Tab key to validate your choice.<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Incomplete Macro<br />
</span> Range(<span style="color: maroon;">"A8"</span>).Value<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
In this case, the property, Value, represents the contents of the cell.<br />
<br />
Next, we will assign the value 48 to cell A8 :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'A8 = 48<br />
</span> Range(<span style="color: maroon;">"A8"</span>).Value = 48<br />
<span style="color: green;">'Translation :<br />
</span> <span style="color: green;">'The value of cell A8 is equal to 48<br />
</span><span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
Then we will assign the value <span class="b">Sample text</span> to cell A8 (important : the text must be within " ") :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'A8 = Sample text<br />
</span> Range(<span style="color: maroon;">"A8"</span>).Value = <span style="color: maroon;">"Sample text"</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
In this case, we're going to modify cell A8 on the worksheet, the cell from which the procedure is actually launched (using a formula button). If you create a second button like this on worksheet 2, it will modify cell A8 on that sheet (sheet 2).<br />
<br />
To make it modify cell A8 on sheet 2 when you click the button on sheet 1, you have to add the following before Range : <span class="b">Sheets("Name_of_the_sheet")</span> or <span class="b">Sheets(Number_of_the_sheet)</span>.<br />
<br />
<div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'A8 on sheet 2 = Sample text<br />
</span> Sheets(<span style="color: maroon;">"Sheet2"</span>).Range(<span style="color: maroon;">"A8"</span>).Value = <span style="color: maroon;">"Sample text"</span><br />
<span style="color: green;">'Or :<br />
</span> <span style="color: green;">'Sheets(2).Range("A8").Value = "Sample text"<br />
</span><span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div>Just the same, if we wanted to modify cell A8 on sheet 2 of another open workbook, we have to add the following before Sheets and Range : <span class="b">Workbooks("Name_of_the_file")</span>.<br />
<br />
<div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'A8 on sheet 2 of workbork 2 = Sample text<br />
</span> Workbooks(<span style="color: maroon;">"Book2.xlsx"</span>).Sheets(<span style="color: maroon;">"Sheet2"</span>).Range(<span style="color: maroon;">"A8"</span>).Value = <span style="color: maroon;">"Sample text"</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div>Although we used Value in these examples, you don't really need to use it, because if nothing else is specified, it will be the value of the cell that is modified.<br />
<br />
For example, these two lines would have the same effect :<br />
<br />
<div class="code_vba"><code>Range(<span style="color: maroon;">"A8"</span>).Value = 48<br />
Range(<span style="color: maroon;">"A8"</span>) = 48</code></div><div class="code_vba"></div><div class="p_top"><span class="u">Erase cell contents</span> :</div><img alt="list2 - properties" class="relief" src="http://www.excel-pratique.com/en/vba/img_properties/list2.png" title="" /><br />
<br />
<div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Erase the contents of column A<br />
</span> Range(<span style="color: maroon;">"A:A"</span>).ClearContents<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div><div class="code_vba"></div><div class="p_top"><u><b><span class="u">Text Formatting</span> :</b></u></div>When you open <span class="b">Font.</span>, the list of properties that can be applied to text formatting will appear :<br />
<br />
<img alt="list3 - properties" class="relief" src="http://www.excel-pratique.com/en/vba/img_properties/list3.png" title="" /> <br />
<div class="mini i"><br />
</div><div class="mini i">We'll explain in detail how to edit the colors on the next page ...</div><div class="p_top"><br />
</div><div class="p_top"><u><b><span class="u">Formatting : change text size </span> :</b></u></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Edit the size of text in cells A1 through A8<br />
</span> Range(<span style="color: maroon;">"A1:A8"</span>).Font.Size = 18<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="p_top"><br />
</div><div class="p_top"><u><b><span class="u">Formatting : make text bold</span> :</b></u></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Make cells A1 through A8 bold<br />
</span> Range(<span style="color: maroon;">"A1:A8"</span>).Font.Bold = <span style="color: navy; font-weight: bold;">True</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div><span class="b">Bold = True</span> means <span class="b">Characters will appear in bold = Yes</span>.<br />
<br />
To remove the "bold" formatting from text, all you have to do is replace "Yes" with "No", or in other words, "True" with "False" :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Remove "bold" formatting from cells A1 through A8<br />
</span> Range(<span style="color: maroon;">"A1:A8"</span>).Font.Bold = <span style="color: navy; font-weight: bold;">False</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="p_top"><br />
</div><div class="p_top"><u><b><span class="u">Formatting : italicize text</span> :</b></u></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Italicize cells A1 through A8<br />
</span> Range(<span style="color: maroon;">"A1:A8"</span>).Font.Italic = <span style="color: navy; font-weight: bold;">True</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div><div class="p_top"><b><u><span class="u">Formatting : underline text</span> :</u></b></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Underline cells A1 through A8<br />
</span> Range(<span style="color: maroon;">"A1:A8"</span>).Font.Underline = <span style="color: navy; font-weight: bold;">True</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div><div class="p_top"><u><b><span class="u">Formatting : Set font</span> :</b></u></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Edit font in cells A1 through A8<br />
</span> Range(<span style="color: maroon;">"A1:A8"</span>).Font.Name = <span style="color: maroon;">"Arial"</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div style="margin-bottom: 5px; margin-top: 5px;"><div id="div-gpt-ad-1338510188641-0" style="height: 90px; width: 728px;"></div></div><div class="p_top"><u><b><span class="u">Add borders</span> :</b></u></div><img alt="list4 - properties" class="relief" src="http://www.excel-pratique.com/en/vba/img_properties/list4.png" title="" /> <br />
<div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Add a border to cells A1 to A8<br />
</span> Range(<span style="color: maroon;">"A1:A8"</span>).Borders.Value = 1<br />
<span style="color: green;">'Value = 0 => no border<br />
</span><span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div><div class="p_top"><span class="u">Change the formatting of currently selected cells</span> :</div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Add a border to selected cells<br />
</span> Selection.Borders.Value = 1<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div><div class="p_top"><u><b><span class="u">Change a worksheet's properties</span> :</b></u></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Hide a worksheet<br />
</span> Sheets(<span style="color: maroon;">"Sheet3"</span>).Visible = 0<br />
<span style="color: green;">'Visible = -1 => cancels the effect<br />
</span><span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div><div class="code_rem">Don't forget that we've only introduced a tiny minority of the possible customizations that can be done with VBA. If the property that you are looking for isn't described in detail here, don't be afraid to look for it in the list of properties in Excel or in Excel's own help files. </div><div class="code_rem"><br />
The macro recorder can also save you a lot of time if you don't know the name of a property. If you record the action that you need, it will be easy to find the name of the property so that you can then use it in your own macro.</div><div class="p_top"><br />
</div><div class="p_top"><span class="u">Change the value of a cell based on another cell</span> :</div><br />
In this case, we want A7 to take its value from A1 :<br />
<img alt="value - properties" class="relief" src="http://www.excel-pratique.com/en/vba/img_properties/value.png" title="" /> <br />
So we will tell A7 to take its value from A1, which would look like this :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'A7 = A1<br />
</span> Range(<span style="color: maroon;">"A7"</span>) = Range(<span style="color: maroon;">"A1"</span>)<br />
<span style="color: green;">'Or :<br />
</span> <span style="color: green;">'Range("A7").Value = Range("A1").Value<br />
</span><span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
If we only wanted to copy the text size from the other cell, the code would look like this :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
Range(<span style="color: maroon;">"A7"</span>).Font.Size = Range(<span style="color: maroon;">"A1"</span>).Font.Size<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_rem"></div><div class="code_rem">Anything on the left side of the = takes on the value of what is on the right side of the =.</div><div class="p_top"><span class="u">Change the value of a cell based on its own value</span> :</div><br />
Now we're going to create a click counter.<br />
Each time we click, the value of A1 will be incremented by 1:<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Click counter in A1<br />
</span> Range(<span style="color: maroon;">"A1"</span>) = Range(<span style="color: maroon;">"A1"</span>) + 1<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
Excel executes the code line by line, so these commentaries should help you understand the code itself :<br />
<div class="code_vba"><code><span style="color: green;">'For example : before the code is executed, A1 has the value 0<br />
</span><br />
<span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<br />
<span style="color: green;">'The button has been clicked, so the procedure is starting<br />
</span> <span style="color: green;">'For the moment, A1 still has the value 0<br />
</span> <br />
<span style="color: green;">'DURING the execution of the line immediately below, A1 still has the value 0<br />
</span> Range(<span style="color: maroon;">"A1"</span>) = Range(<span style="color: maroon;">"A1"</span>) + 1 <span style="color: green;">'And now the calculation is : New_value_of_A1 = 0 + 1<br />
</span> <span style="color: green;">'A1 has the value 1 only AFTER the execution of the line of code<br />
</span> <br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="p_top"><br />
</div><div class="p_top"><span class="u">With</span> :</div>This code makes it possible to set different properties of the active cell :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
ActiveCell.Borders.Weight = 3<br />
ActiveCell.Font.Bold = <span style="color: navy; font-weight: bold;">True</span><br />
ActiveCell.Font.Size = 18<br />
ActiveCell.Font.Italic = <span style="color: navy; font-weight: bold;">True</span><br />
ActiveCell.Font.Name = <span style="color: maroon;">"Arial"</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
In this case, we can use <span class="b">With</span> to avoid having to repeat <span class="b">ActiveCell</span>.<br />
Now you will see how With works:<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: green;">'Beginning of instructions using command: WITH<br />
</span> <span style="color: navy; font-weight: bold;">With</span> ActiveCell<br />
.Borders.Weight = 3<br />
.Font.Bold = <span style="color: navy; font-weight: bold;">True</span><br />
.Font.Size = 18<br />
.Font.Italic = <span style="color: navy; font-weight: bold;">True</span><br />
.Font.Name = <span style="color: maroon;">"Arial"</span><br />
<span style="color: green;">'End of instructions using command: END WITH<br />
</span> <span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">With</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
This way we don't have to repeat <span class="b">ActiveCell</span>.<br />
Although it isn't really necessary in this case, we could avoid repeating <span class="b">.Font</span>, too, which would look like this :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> properties()<br />
<span style="color: navy; font-weight: bold;">With</span> ActiveCell<br />
.Borders.Weight = 3<br />
<span style="color: navy; font-weight: bold;">With</span> .Font<br />
.Bold = <span style="color: navy; font-weight: bold;">True</span><br />
.Size = 18<br />
.Italic = <span style="color: navy; font-weight: bold;">True</span><br />
.Name = <span style="color: maroon;">"Arial"</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">With</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">With</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div>Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-77877768500296553882012-10-03T11:44:00.000+05:302012-10-05T11:49:58.828+05:302 VBA Course : Selections<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiOL8LjQLBqwVZ2h1z1JpOFZsVWt5ozH1KJm5K_MSkpYPclqjZeTbz1QK4dHXEI2MKn4G6SFmZCyckxcaGPvzHQUGqNeI1ZPBgTylFCKVc64ShCwxieS6ysFxCc1iKJKZVrPPJDk1D_Mo/s1600/stroke_number_two_gestureworks.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiOL8LjQLBqwVZ2h1z1JpOFZsVWt5ozH1KJm5K_MSkpYPclqjZeTbz1QK4dHXEI2MKn4G6SFmZCyckxcaGPvzHQUGqNeI1ZPBgTylFCKVc64ShCwxieS6ysFxCc1iKJKZVrPPJDk1D_Mo/s1600/stroke_number_two_gestureworks.png" /></a></div> We'll begin by creating a macro that selects the cell that we specifiy.<br />
<br />
First open the editor and add a module :<br />
<br />
<img alt="module - selections" class="relief" src="http://www.excel-pratique.com/en/vba/img_selections/module.png" title="" /> <br />
In the module, type "sub selection" and press Enter.<br />
<br />
You will notice that Excel has automatically filled in the end of this new procedure :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
Now create a formula button to which you will associate this macro (it is empty for now) :<br />
<br />
<img alt="macro - selections" class="relief" src="http://www.excel-pratique.com/en/vba/img_selections/macro.png" title="" /> <br />
<br />
Complete your macro with this code :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Select cell A8<br />
</span> Range(<span style="color: maroon;">"A8"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
You can test this macro by clicking on your formula button, and you will see that cell A8 is now selected.<br />
<br />
We will now edit the macro so that it selects cell A8 on the second worksheet :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Activating of Sheet 2<br />
</span> Sheets(<span style="color: maroon;">"Sheet2"</span>).Activate<br />
<span style="color: green;">'Selecting of Cell A8<br />
</span> Range(<span style="color: maroon;">"A8"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
Excel will now activate Sheet 2 and then select cell A8.<br />
<div class="code_rem"></div><div class="code_rem"><span class="u">Note</span> : the comments (text in green) will help you understand the macros in this course correctly.</div><div class="p_top"><span class="u">Selecting different cells</span> :</div><div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Selecting A8 and C5<br />
</span> Range(<span style="color: maroon;">"A8, C5"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="p_top"><br />
</div><div class="p_top"><br />
</div><div class="p_top"><u><b><span class="u">Selecting a range of cells</span> :</b></u></div><div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Selecting cells A1 to A8<br />
</span> Range(<span style="color: maroon;">"A1:A8"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="p_top"><br />
</div><div class="p_top"><br />
</div><div class="p_top"><u><b><span class="u">Selecting a range of cells that has been renamed</span> :</b></u></div><div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Selecting cells from the "my_range" range<br />
</span> Range(<span style="color: maroon;">"my_range"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div><img alt="my range - selections" class="relief" src="http://www.excel-pratique.com/en/vba/img_selections/my_range.png" title="" /> <br />
<div style="margin-bottom: 5px; margin-top: 5px;"><div id="div-gpt-ad-1338510188641-0" style="height: 90px; width: 728px;"></div></div><div class="p_top"><u><b><span class="u">Selecting a cell by row and column number</span> :</b></u></div><div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Selecting the cell in row 8 and column 1<br />
</span> Cells(8, 1).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
This method of selecting cells allows for more dynamic selections. It will be quite useful further along.<br />
<br />
Here is a little example :<br />
<div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Random selection of a cell from row 1 to 10 and column 1<br />
</span> Cells(Int(Rnd * 10) + 1, 1).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: green;">'Translation :<br />
</span> <span style="color: green;">'Cells([random_number_between_1_and_10], 1).Select<br />
</span><span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
In this case, the row number is : <span class="b">Int(Rnd * 10) + 1</span>, or in other words : <span class="b">a number between 1 and 10</span> (there's no reason you should learn this code at this point).<br />
<div class="p_top"><br />
</div><div class="p_top"><u><b><span class="u">Moving a selection</span> :</b></u></div><div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Selecting a cell (described in relation to the cell that is currently active)<br />
</span> ActiveCell.Offset(2, 1).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
Moving the selection box two rows down and one column to the right :<br />
<br />
<img alt="offset - selections" class="relief" src="http://www.excel-pratique.com/en/vba/img_selections/offset.png" title="" /> <br />
<div class="p_top"><br />
</div><div class="p_top"><u><b><span class="u">Selecting rows</span> :</b></u></div><br />
It is possible to select entire rows using the <span class="b">Range</span> or <span class="b">Rows</span> commands (the Rows command is of course specific to rows).<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Selecting rows 2 to 6<br />
</span> Range(<span style="color: maroon;">"2:6"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Selecting rows 2 to 6<br />
</span> Rows(<span style="color: maroon;">"2:6"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="p_top"><br />
</div><div class="p_top"><u><b><span class="u"><br />
</span></b></u></div><div class="p_top"><u><b><span class="u">Selecting columns</span> :</b></u></div><br />
As with rows, it is possible to select entire columns using the <span class="b">Range</span> or <span class="b">Columns</span> commands (the Columns command is of course specific to columns).<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Selecting columns B to G<br />
</span> Range(<span style="color: maroon;">"B:G"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> selection()<br />
<span style="color: green;">'Selecting columns B to G<br />
</span> Columns(<span style="color: maroon;">"B:G"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div style="margin-bottom: 5px; margin-top: 5px;"><div id="div-gpt-ad-1338510188641-1" style="height: 90px; width: 728px;"></div></div>Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-86377733090535629782012-10-03T11:38:00.000+05:302012-10-05T11:50:11.640+05:301 VBA Course : First Macro<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfJkmOxRNIQ9F40inPeKPdGIZ5JDEIT6OrAyNmZWhKhbQp2le_L0kXqHxP2ZkhFipmPqvekO07FxvsCrdR-1oN8RL_3OnwKD4eRQXONJ6_5pwwvnAG-xFDNoVysOWq2ypqM9DRnpXsjsg/s1600/stroke_symbol_arrow_up_gestureworks.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfJkmOxRNIQ9F40inPeKPdGIZ5JDEIT6OrAyNmZWhKhbQp2le_L0kXqHxP2ZkhFipmPqvekO07FxvsCrdR-1oN8RL_3OnwKD4eRQXONJ6_5pwwvnAG-xFDNoVysOWq2ypqM9DRnpXsjsg/s1600/stroke_symbol_arrow_up_gestureworks.png" /></a></div><br />
The macro recorder makes it very easy to automate certain tasks.<br />
<br />
To give just one example, we will automate the following actions :<br />
<ul><li>delete the contents of columns A and C</li>
<li>move the contents of column B to column A</li>
<li>move the contents of column D to column C</li>
</ul><img alt="macro1 - first macro" class="relief" src="http://www.excel-pratique.com/en/vba/img_first_macro/macro1.png" title="" /> <br />
To do this, click on "Record Macro" and then "Ok", carry out the actions described above without interruption (because everything you do will be recorded) and then click on "Stop Recording".<br />
<div class="mini i"><br />
</div><div class="mini i">For versions of Excel lower than 2007 : Tools > Macros > Record New Macro.</div><div class="mini i"><br />
</div><img alt="macro2 - first macro" class="relief" src="http://www.excel-pratique.com/en/vba/img_first_macro/macro2.png" title="" /> <br />
Excel has recorded your actions and translated them into VBA code.<br />
<br />
To view your macro, open the editor (Alt F11) and click on "Module1" :<br />
<br />
<img alt="macro3 - first macro" class="relief" src="http://www.excel-pratique.com/en/vba/img_first_macro/macro3.png" title="" /> <br />
This code represents the recorded actions.<br />
<br />
Let's take a moment to look at the code that Excel has generated :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> Macro1()<br />
<span style="color: green;">'<br />
</span><span style="color: green;">' Macro1 Macro<br />
</span><span style="color: green;">'<br />
</span><br />
<span style="color: green;">'<br />
</span> Columns(<span style="color: maroon;">"A:A"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
Selection.ClearContents<br />
Columns(<span style="color: maroon;">"C:C"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
Selection.ClearContents<br />
Columns(<span style="color: maroon;">"B:B"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
Selection.Cut Destination:=Columns(<span style="color: maroon;">"A:A"</span>)<br />
Columns(<span style="color: maroon;">"D:D"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
Selection.Cut Destination:=Columns(<span style="color: maroon;">"C:C"</span>)<br />
Columns(<span style="color: maroon;">"C:C"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><div style="margin-bottom: 5px; margin-top: 5px;"><div id="div-gpt-ad-1338510188641-0" style="height: 90px; width: 728px;"></div></div><span class="b">Sub</span> and <span class="b">End Sub</span> mark the beginning and end of the macro, and "Macro1" is the name of this macro :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> Macro1()<br />
<br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
Let's edit the name of the macro to make it more descriptive, changing "Macro1" to "column_handling" (the name of the macro cannot contain any spaces) :<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> column_handling()</code></div><br />
The text in green (text preceeded by an apostrophe) is commentary, and will be ignored when the code is executed :<br />
<div class="code_vba"><code><span style="color: green;">'<br />
</span><span style="color: green;">' Macro1 Macro<br />
</span><span style="color: green;">'<br />
</span><br />
<span style="color: green;">'<br />
</span></code></div><br />
This kind of commentary can be very useful for finding things when there is a lot of code, or when you want to prevent the execution of certain lines of code without deleting them.<br />
<div class="code_vba"></div><div class="code_vba"><code><span style="color: navy; font-weight: bold;">Sub</span> column_handling()<br />
<span style="color: green;">'<br />
</span><span style="color: green;">'My first commentary !<br />
</span><span style="color: green;">'<br />
</span> Columns(<span style="color: maroon;">"A:A"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
Selection.ClearContents<br />
Columns(<span style="color: maroon;">"C:C"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
Selection.ClearContents<br />
Columns(<span style="color: maroon;">"B:B"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
Selection.Cut Destination:=Columns(<span style="color: maroon;">"A:A"</span>)<br />
Columns(<span style="color: maroon;">"D:D"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
Selection.Cut Destination:=Columns(<span style="color: maroon;">"C:C"</span>)<br />
Columns(<span style="color: maroon;">"C:C"</span>).<span style="color: navy; font-weight: bold;">Select</span><br />
<span style="color: navy; font-weight: bold;">End</span> <span style="color: navy; font-weight: bold;">Sub</span></code></div><br />
Now we want this macro to be executed at the click of a button.<br />
<br />
Click on Insert > Button (Form controls) :<br />
<div class="mini i"><br />
</div><div class="mini i">For versions of Excel lower than 2007 : "Button" from the "Formulas" toolbar.</div><div class="mini i"><br />
</div><img alt="macro4 - first macro" class="relief" src="http://www.excel-pratique.com/en/vba/img_first_macro/macro4.png" title="" /> Insert your button and then just select the macro that you created :<br />
<img alt="macro5 - first macro" class="relief" src="http://www.excel-pratique.com/en/vba/img_first_macro/macro5.png" title="" /> <br />
When you click on the button, your macro will be executed :<br />
<br />
<img alt="macro6 - first macro" class="relief" src="http://www.excel-pratique.com/en/vba/img_first_macro/macro6.png" title="" /> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-50640676638560014302012-10-03T11:32:00.002+05:302012-10-05T11:50:25.804+05:30VBA Course : Introduction<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIVS-lnS7dzNaOyHqCsF_1DG4-NxmYukaEUvSyrKxXSSXD68V-UwQxY1A6-FZQjzswjqEzBHEIH31VcTSd-vjojMMEsFqbRUoOmYz4j_nRLg90JccI3fz9cMrD99WcBC4CWdM0RSide_g/s1600/stroke_number_zero_gestureworks.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjIVS-lnS7dzNaOyHqCsF_1DG4-NxmYukaEUvSyrKxXSSXD68V-UwQxY1A6-FZQjzswjqEzBHEIH31VcTSd-vjojMMEsFqbRUoOmYz4j_nRLg90JccI3fz9cMrD99WcBC4CWdM0RSide_g/s1600/stroke_number_zero_gestureworks.png" /></a></div> VBA (Visual Basic for Applications) is a language related to Visual Basic that can only run through a host application (Excel, in our case).<br />
<br />
Using VBA, we can do almost anything imaginable with Excel ...<br />
<br />
But before we get started, let's begin by making sure that the tools we need are visible.<br />
<br />
If you are using Excel version 2007 (or a higher version), click on<span style="color: #0b5394;"> File > Options > Customize the Ribbon and then check "Developer".</span><br />
<br />
<img alt="ribbon - introduction" class="relief" src="http://www.excel-pratique.com/en/vba/img_introduction/ribbon.png" title="" /> <br />
A new tab will be added :<br />
<img alt="dev - introduction" class="relief" src="http://www.excel-pratique.com/en/vba/img_introduction/dev.png" title="" /> <br />
<div class="mini i"><br />
</div><div class="mini i">If the version of Excel that you are using is lower than 2007, add the "Control Toolbox" and "Formulas" toolbars.</div><div class="mini i"><br />
</div>To work with VBA code, we'll need an editor, which is installed by default. You can open it by pressing the shortcut key combination "<span style="color: #0b5394;">Alt F11</span>" :<br />
<br />
<img alt="vbe - introduction" class="relief" src="http://www.excel-pratique.com/en/vba/img_introduction/vbe.png" title="" /> <br />
<div class="code_rem"></div><div class="code_rem">We'll come back to this. What's important for now is just to remember the "Alt F11" shortcut ...</div><div style="margin-bottom: 5px; margin-top: 5px;"><div id="div-gpt-ad-1338510188641-0" style="height: 90px; width: 728px;"></div></div>Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-61753861531860830432012-09-28T13:15:00.003+05:302012-09-28T13:15:46.338+05:30VBA: Application Open FileName List all Excel File typesWhen you are showing a open dialog box to the user to select an excel file, by default the filter would be *.* which will show all files including excel files. and .xls and .xlsx will show only 97-2003 and 2007 excel files and failed to show .xlsb and .xlsm files. The below code (.xls*) will show all types of excel files.<br />
<br />
<script type="syntaxhighlighter" class="brush: vb"><![CDATA[
Sub Get_FileName()
Dim FileToOpen As String
'application.GetOpenFilename([FileFilter],[FilterIndex],[Title],[ButtonText],[MultiSelect])
FileToOpen = Application _
.GetOpenFileName("Excel Workbooks (*.xls*),*.xls*", 1, "Title of dialog Box", False)
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-62101375957193826452012-09-26T20:13:00.001+05:302012-09-26T20:13:59.646+05:30VBA: Array - Return common values from two arraysThe below function will return the common values that are exist in two different arrays. This can be achieved by nested-looping concept, but this is an effective way :).<a href="http://ckannan.blogspot.in/2012/09/vba-isinarray-find-value.html"><u>IsInArray</u></a> is an another function. Dont forget to include it.<br />
aa<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Function Array_UIntersect(array1 As Variant, _
array2 As Variant) As Variant
Dim tempArray As Variant
Dim i As Long
' start with a single element
ReDim tempArray(0)
' if element in first array exists in second array, keep it
For i = LBound(array1) To UBound(array1)
If IsInArray(array2, array1(i)) Then ' found!
ReDim Preserve tempArray(UBound(tempArray) + 1)
tempArray(UBound(tempArray)) = array1(i)
End If
Next i
' first element is Empty, so shift all elements one position up
For i = LBound(tempArray) To UBound(tempArray) - 1
tempArray(i) = tempArray(i + 1)
Next i
' remove last element
If UBound(tempArray) <> 0 Then
ReDim Preserve tempArray(LBound(tempArray) _
To UBound(tempArray) - 1)
End If
Array_UIntersect = tempArray
End Function
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com1tag:blogger.com,1999:blog-6762629203626551095.post-15336883227453770312012-09-26T20:08:00.001+05:302012-09-26T20:09:37.211+05:30VBA: Distinct Array items - Remove duplicatesIf you have an array with repeated items and want distinct items, then copy the below source code. <a href="http://ckannan.blogspot.in/2012/09/vba-isinarray-find-value.html"><u>IsInArray</u></a> is an another function. Dont forget to include it.<br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Function Array_Unique(arr As Variant) As Variant
Dim tempArray As Variant
Dim i As Long
' start the temp array with one element and
' populate with first value
ReDim tempArray(0)
tempArray(0) = arr(LBound(arr))
For i = LBound(arr) To UBound(arr)
If Not IsInArray(tempArray, arr(i)) Then ' not in destination array
ReDim Preserve tempArray(UBound(tempArray) + 1)
tempArray(UBound(tempArray)) = arr(i)
End If
Next i
Array_Unique = tempArray
End Function
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com1tag:blogger.com,1999:blog-6762629203626551095.post-48660240073718731852012-09-26T19:48:00.001+05:302012-09-26T19:55:40.845+05:30VBA: IsInArray - Find a value in array itemsThe below script will be used to get to know whether a particular item exists in a array or not. Instead of looping this would be faster.<br />
<script type="syntaxhighlighter" class="brush: vb"><![CDATA[
Function IsInArray(arr As Variant, valueToCheck As String, _
Optional exactMatch As Boolean = True) As Boolean
Dim wordList As String
Dim startPosition As Long
Dim nextCommaPosition As Long
Dim matchedTerm As String
If UBound(Filter(arr, valueToCheck)) > -1 Then
wordList = Join(arr, ",")
' start from the allegedly matched term ....
startPosition = InStr(wordList, valueToCheck)
' get position of the comma after the allegedly matched term ...
nextCommaPosition = InStr(startPosition + 1, wordList, ",")
' the alleged "match" is in between
matchedTerm = Mid$(wordList, startPosition, _
nextCommaPosition - startPosition)
If exactMatch Then
IsInArray = (StrComp(valueToCheck, matchedTerm) = 0)
Else
IsInArray = (StrComp(valueToCheck, matchedTerm) <> 0)
End If
End If
End Function
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com1tag:blogger.com,1999:blog-6762629203626551095.post-47329717975773708352012-09-26T19:22:00.000+05:302012-09-26T19:22:50.007+05:30VBA: Cell Data Validation list to an arrayThe below function will be used to get list of Cell data validation and store into an array variable. This code is needed when checking the cell value as whether it is picked from the validation drop-down or the validation rule overridden by copy-paste the cell value. (Excel has this flaw, whatever the data validation you define, simply that can be overriden by clipboard data Ctrl+C Ctrl+P)<br />
<br />
<script type="syntaxhighlighter" class="brush: vb"><![CDATA[
Function GetValidationRange(rng As Excel.Range) As Variant
Dim currentValidation As Excel.Validation
Dim targetRange As Excel.Range
Dim validationType As Excel.XlDVType
Dim tempValues() As Variant
Set currentValidation = rng.Validation
validationType = currentValidation.Type
If (validationType = xlValidateList Or validationType = xlValidateCustom) Then
Set targetRange = Excel.Range(currentValidation.Formula1)
If Not targetRange Is Nothing And targetRange.Count = 1 Then
GetValidationRange = targetRange
IsOnlyOneValue = 1
Exit Function
ElseIf Not targetRange Is Nothing Then
tempValues = WorksheetFunction.Transpose(targetRange.Value)
Else
tempValues = Split(currentValidation.Formula1, ",")
End If
GetValidationRange = tempValues
End If
For i = LBound(tempValues) To UBound(tempValues) - 1
Debug.Print tempValues(i)
Next i
End Function
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-78102332777756087132012-09-24T15:17:00.000+05:302012-09-24T15:17:43.870+05:30VBA Excel : Sheet Named range into ArrayThe below code will be used to access the list of named-range values and pass to an array variable.<br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Sub SingleColumnNamedRangeToArray()
Dim vArray()
Dim lLoop As Long
ReDim vArray(Range("DistinctSubsidiary").Rows.Count - 1)
vArray = WorksheetFunction.Transpose(Range("DistinctSubsidiary"))
For lLoop = LBound(vArray) To UBound(vArray)
MsgBox vArray(lLoop) & " resides in " & Range("DistinctSubsidiary").Cells(lLoop, 1).Address
Next lLoop
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-53416608670423053932012-09-17T16:00:00.000+05:302012-09-17T16:00:05.790+05:30VBA - Extract notes section from PPT<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEy8CKUjqZfFiOJu8ZyIWGQDSe9YTAkJxTZpZCF3qBJdeQmPHAdukUAPOnlXPK7iZknEvCwXywz83IcMP0oHZj-usVaHeSN1FY99uw6HXkoMuhE0HHgY-9opOjMRtIm6Kv1SVPvLiAMJg/s1600/1347877504_Microsoft+PowerPoint.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEy8CKUjqZfFiOJu8ZyIWGQDSe9YTAkJxTZpZCF3qBJdeQmPHAdukUAPOnlXPK7iZknEvCwXywz83IcMP0oHZj-usVaHeSN1FY99uw6HXkoMuhE0HHgY-9opOjMRtIm6Kv1SVPvLiAMJg/s1600/1347877504_Microsoft+PowerPoint.png" /></a></div>The below code will be used to extract the notes section from Powerpoint slides to excel sheets range. We have seen a similar example of copy-pasting data from excel to powerpoint. This is a reverse operation of extracting powerpoint notes to excel.<br />
<br />
Note: Please include Reference Libraries <i>Microsoft PowerPoint XX.0 Object Library</i> & <i>Microsoft Scripting Runtime</i><br />
<br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Sub ExtractSlideNoteMultiParagraphs()
Application.ScreenUpdating = False
Const FILE_TYPE As String = ".ppt"
Dim oFileDialog As FileDialog
Dim oFileSystem As FileSystemObject
Dim oLoopFolder As Folder
Dim oFilePath As File
Dim strExtension As String
Dim oPowerPoint As PowerPoint.Application
Dim oPresentation As PowerPoint.Presentation
Dim oTextRange As PowerPoint.TextRange
Dim RowN As Long
Dim PCount As Long
Dim i As Long
On Error GoTo ERROR_HANDLER
' Open a dialog window to select the desired folder.
Set oFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
With oFileDialog
If .Show Then ' Check if OK button is pressed
Set oFileSystem = CreateObject("Scripting.FileSystemObject")
Set oLoopFolder = oFileSystem.GetFolder(.SelectedItems(1))
If oLoopFolder.Files.Count = 0 Then GoTo EXIT_SUB
ActiveSheet.Cells.Clear ' Clear previous contents and formats
' Change the following part if you want to add some formats to your headers.
ActiveSheet.Range("A1").Value = "File Name"
ActiveSheet.Range("B1").Value = "Date and Time"
ActiveSheet.Range("C1").Value = "Notes" ' Add additional headers if you need
RowN = 2
Set oPowerPoint = CreateObject("PowerPoint.Application")
With oPowerPoint
.WindowState = ppWindowMinimized
.Visible = msoTrue
For Each oFilePath In oLoopFolder.Files
strExtension = Right(oFilePath, 5)
If InStr(strExtension, FILE_TYPE) > 0 Then
Set oPresentation = oPowerPoint.Presentations.Open( _
Filename:=oFilePath, _
WithWindow:=msoFalse)
With oPresentation
ActiveSheet.Cells(RowN, 1).Value = .Name
ActiveSheet.Cells(RowN, 2).Value = FormatDateTime(Now, vbGeneralDate)
Set oTextRange = _
.Slides(1).NotesPage.Shapes.Placeholders(2).TextFrame.TextRange
PCount = oTextRange.Paragraphs.Count
For i = 1 To PCount
ActiveSheet.Cells(RowN, i + 2).Value = oTextRange.Paragraphs(i)
Next i
RowN = RowN + 1
.Close
End With
End If
Next oFilePath
.Quit
End With
' This is an example.
With ActiveSheet.Range("C2", Range("C2").End(xlDown)).Font
.Color = 100
.TintAndShade = 0
.Italic = False
.Bold = False
.Underline = xlUnderlineStyleSingle
End With
End If
End With
EXIT_SUB:
Set oFilePath = Nothing
Set oPowerPoint = Nothing
Set oLoopFolder = Nothing
Set oFileSystem = Nothing
Set oFileDialog = Nothing
Application.ScreenUpdating = True
Exit Sub
ERROR_HANDLER:
' Some code for error handling
Err.Clear
GoTo EXIT_SUB
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-90957598521038519592012-09-17T14:50:00.001+05:302012-09-17T14:57:58.277+05:30VBA - Unzip files using VBA<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvaSVl8JrHBrPiMccYDa148pxNUyYXDxcseuayYBG-f1icq8ONvlI04UqZnne65dWgnik962-00ksLbgWRK88_M1P8sfoDREtEocc0XXCqExqT-yYb54DQag6_Dduklc94xWFU9F7ZsXQ/s1600/1347873289_Archive.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvaSVl8JrHBrPiMccYDa148pxNUyYXDxcseuayYBG-f1icq8ONvlI04UqZnne65dWgnik962-00ksLbgWRK88_M1P8sfoDREtEocc0XXCqExqT-yYb54DQag6_Dduklc94xWFU9F7ZsXQ/s1600/1347873289_Archive.png" /></a></div>
<br />
The below code will Unzip a *.zip file to a specific folder. We have already seen couple of examples like 'Accessing system special folders' and 'Creating shortcut of an system application', This code also in the same series of Unzipping the files. Quite useful if you are designing a complete protect and want to install in client machine.<br />
<br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Sub Test()
Call UnZip("E:\Extract", "C:\Users\Kannan\Desktop\exapandable-panel.zip")
End Sub
Function UnZip(strTargetPath As String, Fname As Variant)
Dim oApp As Object, FSOobj As Object
Dim FileNameFolder As Variant
If Right(strTargetPath, 1) <> Application.PathSeparator Then
strTargetPath = strTargetPath & Application.PathSeparator
End If
FileNameFolder = strTargetPath
'create destination folder if it does not exist
Set FSOobj = CreateObject("Scripting.FilesystemObject")
If FSOobj.FolderExists(FileNameFolder) = False Then
FSOobj.CreateFolder FileNameFolder
End If
Set oApp = CreateObject("Shell.Application")
oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items
Set oApp = Nothing
Set FSOobj = Nothing
Set FileNameFolder = Nothing
End Function
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com2tag:blogger.com,1999:blog-6762629203626551095.post-77570166632283688192012-09-17T14:33:00.003+05:302012-09-17T14:33:54.274+05:30VBA - Protect Sheet from User / Allow macro<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgh5GJyc_CrFMC6JPtp039j1x0u-mS7yRL0TP2HAuBAAVxl9_L4_tyrbScs9ehs_3qoNT9ze9mtCyw0cLMHP1hVHh9b3x4KTtFBW85v56ZHUKaWa27WnCuXRXGz9hvHqk0O2vERQEzX1P4/s1600/1347872509_Login+Manager.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgh5GJyc_CrFMC6JPtp039j1x0u-mS7yRL0TP2HAuBAAVxl9_L4_tyrbScs9ehs_3qoNT9ze9mtCyw0cLMHP1hVHh9b3x4KTtFBW85v56ZHUKaWa27WnCuXRXGz9hvHqk0O2vERQEzX1P4/s1600/1347872509_Login+Manager.png" /></a></div>
<br />
Generally, if you want your vba code to run on a protected sheet, you wrap your code in unprotect/protect statements in every procedure or function. You also have to remember to protect the sheet in case it is unprotected. Here is the attached file that clearly explains how you can avoid this.<br />
<br />
The following code in the Workbook_Open procedure/event protects all the sheets from the user but allows the macro code to work on the sheets.<br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Option Explicit
Private Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
wks.Protect Password:="abc", UserInterfaceOnly:=True
Next wks
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-20887542900770721182012-09-17T13:12:00.001+05:302012-09-17T16:02:26.673+05:30VBA - Export to Powerpoint<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig9wZo1VK8-_IRerpE5g8WXyTYjnEG9umBglWTqzg_AyBTrx-QiV9oibx1ibVpeXZ3Y9fmKDbNUGTUyhjh7XmJ5c8bnsgA1sAoU2hVt0uOrxmmoUkKuALVYwbWNZSPF4gjda8BBTa1YUs/s1600/pie-chart-icon.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRKrhqItTHcV2ZWc0mEM18yifH5TJR_ep231HMmOVUdz9wMRlaJMQ2wrRVVEH_dq87g6u3frq05sQOSnpj5OZYDuP8N1B8AJCMZIuPfwBNWAKd2M1myFxfNrzpdzGRWJpEGdRFUUx446A/s1600/1347877504_Microsoft+PowerPoint.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="150" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRKrhqItTHcV2ZWc0mEM18yifH5TJR_ep231HMmOVUdz9wMRlaJMQ2wrRVVEH_dq87g6u3frq05sQOSnpj5OZYDuP8N1B8AJCMZIuPfwBNWAKd2M1myFxfNrzpdzGRWJpEGdRFUUx446A/s200/1347877504_Microsoft+PowerPoint.png" width="150" /></a></div>
The below code will be used to copy paste a excel range (named range) to a powerpoint slide, this code has an ability to add new slides (at the End/Beginning) if you run a loop for 'N' number of slides. <br />
<br />
Customization Options:<br />
> Add new slide at the end / beginning<br />
> Copy paste the excel chart as Image / Html<br />
> Enable / Disable Chart link to the excel<br />
> Zoom / Align the Powerpoint slides from Excel<br />
<br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Sub Copy_Paste_to_PowerPoint()
'Requires a reference to the Microsoft PowerPoint Library via the Tools - Reference menu in the VBE
Dim ppApp As PowerPoint.Application
Dim ppSlide As PowerPoint.Slide
Dim SheetName As String
Dim TestRange As Range
Dim TestSheet As Worksheet
Dim TestChart As ChartObject
Dim PasteChart As Boolean
Dim PasteChartLink As Boolean
Dim ChartNumber As Long
Dim PasteRange As Boolean
Dim RangePasteType As String
Dim RangeName As String
Dim AddSlidesToEnd As Boolean
'Parameters
'SheetName - name of sheet in Excel that contains the range or chart to copy
'PasteChart -If True then routine will copy and paste a chart
'PasteChartLink -If True then Routine will paste chart with Link; if = False then paste chart no link
'ChartNumber -Chart Object Number
'
'PasteRange - If True then Routine will copy and Paste a range
'RangePasteType - Paste as Picture linked or unlinked, "HTML" or "Picture". See routine below for exact values
'RangeName - Address or name of range to copy; "B3:G9" "MyRange"
'AddSlidesToEnd - If True then appednd slides to end of presentation and paste. If False then paste on current slide.
'use active sheet. This can be a direct sheet name
SheetName = ActiveSheet.Name
'Setting PasteRange to True means that Chart Option will not be used
PasteRange = True
RangeName = "MyRange"
RangePasteType = "Picture"
RangeLink = True
PasteChart = True
PasteChartLink = True
ChartNumber = 1
AddSlidesToEnd = True
'Error testing
On Error Resume Next
Set TestSheet = Sheets(SheetName)
Set TestRange = Sheets(SheetName).Range(RangeName)
Set TestChart = Sheets(SheetName).ChartObjects(ChartNumber)
On Error GoTo 0
If TestSheet Is Nothing Then
MsgBox "Sheet " & SheetName & " does not exist. Macro will exit", vbCritical
Exit Sub
End If
If PasteRange And TestRange Is Nothing Then
MsgBox "Range " & RangeName & " does not exist. Macro will exit", vbCritical
Exit Sub
End If
If PasteRange = False And PasteChart And TestChart Is Nothing Then
MsgBox "Chart " & ChartNumber & " does not exist. Macro will exit", vbCritical
Exit Sub
End If
'Look for existing instance
On Error Resume Next
Set ppApp = GetObject(, "PowerPoint.Application")
On Error GoTo 0
'Create new instance if no instance exists
If ppApp Is Nothing Then Set ppApp = New PowerPoint.Application
'Add a presentation if none exists
If ppApp.Presentations.Count = 0 Then ppApp.Presentations.Add
'Make the instance visible
ppApp.Visible = True
'Check that a slide exits, if it doesn't add 1 slide. Else use the last slide for the paste operation
If ppApp.ActivePresentation.Slides.Count = 0 Then
Set ppSlide = ppApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
Else
If AddSlidesToEnd Then
'Appends slides to end of presentation and makes last slide active
ppApp.ActivePresentation.Slides.Add ppApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
ppApp.ActiveWindow.View.GotoSlide ppApp.ActivePresentation.Slides.Count
Set ppSlide = ppApp.ActivePresentation.Slides(ppApp.ActivePresentation.Slides.Count)
Else
'Sets current slide to active slide
Set ppSlide = ppApp.ActiveWindow.View.Slide
End If
End If
'Options for Copy & Paste Ranges and Charts
If PasteRange = True Then
'Options for Copy & Paste Ranges
If RangePasteType = "Picture" Then
'Paste Range as Picture
Worksheets(SheetName).Range(RangeName).Copy
ppSlide.Shapes.PasteSpecial(ppPasteDefault, link:=RangeLink).Select
Else
'Paste Range as HTML
Worksheets(SheetName).Range(RangeName).Copy
ppSlide.Shapes.PasteSpecial(ppPasteHTML, link:=RangeLink).Select
End If
Else
'Options for Copy and Paste Charts
Worksheets(SheetName).Activate
ActiveSheet.ChartObjects(ChartNumber).Select
If PasteChartLink = True Then
'Copy & Paste Chart Linked
ActiveChart.ChartArea.Copy
ppSlide.Shapes.PasteSpecial(link:=True).Select
Else
'Copy & Paste Chart Not Linked
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
ppSlide.Shapes.Paste.Select
End If
End If
'Center pasted object in the slide
ppApp.ActiveWindow.Selection.ShapeRange.Height = 445
ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
Application.CutCopyMode = False
On Error Resume Next
AppActivate ("Microsoft PowerPoint")
Set ppSlide = Nothing
Set ppApp = Nothing
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-575399075779460312012-09-17T12:33:00.002+05:302012-09-17T14:56:50.964+05:30VBA - Download files from sharepoint<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9IiyiH1J41kF_NOy6cLwf492xX5H88J9ThaPgoVYaToRd0dSCTBUmiVyIK1n__1D_HZXYytJG5dtPQP__0sgUwSVjwGkJxVZosyXpTqA95ChyFDekNFegE3ipLfM0EgjGjOqTnQJfqn4/s1600/SharePoint-2010-Logo.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="120" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9IiyiH1J41kF_NOy6cLwf492xX5H88J9ThaPgoVYaToRd0dSCTBUmiVyIK1n__1D_HZXYytJG5dtPQP__0sgUwSVjwGkJxVZosyXpTqA95ChyFDekNFegE3ipLfM0EgjGjOqTnQJfqn4/s200/SharePoint-2010-Logo.png" width="130" /></a></div>
The below VBA code will be used to download files from a sharepoint folder at one-click. I have got this code through surfing, this is quite usefull for the people who involves their day to day job with sharepoint files.<br />
<br />
You can now do the same kind to upload files in sharepoint, will try my best to post a code for Uploading files to sharepoint.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuI-dhExfZTBXF9Z1FEmvem-ZwmwE_bm_PWrCb2Y8oEUWcDlhE3D8U_uv0EG74PoGgsRskqe8-HMb6VfptvSuN5mBsdWhL7ndprHTvJTpQCx_XtO5JCMCsWWANoGBKmWdzucRDBbkfFUs/s1600/Capture.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="161" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuI-dhExfZTBXF9Z1FEmvem-ZwmwE_bm_PWrCb2Y8oEUWcDlhE3D8U_uv0EG74PoGgsRskqe8-HMb6VfptvSuN5mBsdWhL7ndprHTvJTpQCx_XtO5JCMCsWWANoGBKmWdzucRDBbkfFUs/s640/Capture.JPG" width="640" /></a></div>
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Option Explicit
Sub btnSharePointFolder()
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets("SharePoint Download")
If sht.Range("SharePointPath") = "" Then
MsgBox "Please enter a sharepoint path first", vbCritical
Exit Sub
End If
If Right(sht.Range("SharePointPath"), 1) <> "/" Then
'SharePointPath: http://testdrive.sharepoint.ckannan.blogspot.com/teams/YourTeam/
sht.Range("SharePointPath") = sht.Range("SharePointPath") & "/"
End If
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = sht.Range("SharePointPath")
.Title = "Please select a location of input files"
.Show
If Not .SelectedItems.Count = 0 Then
sht.Range("SharepointFolder") = .SelectedItems(1)
Else
Exit Sub
End If
End With
' 'To Remove Drive
' Shell "net use Q: /delete"
If Dir("Q:\", vbDirectory) = "" Then
Shell "net use Q: " & sht.Range("SharePointPath").Value '/user:MyDomain\MyUserName MyPassword
End If
End Sub
'_________________________________________________________________________________
Sub MapNetworkDrive()
If Dir("Q:\", vbDirectory) = "" Then
'SharePointPath: http://testdrive.sharepoint.ckannan.blogspot.com/teams/YourTeam/
Shell "net use Q: " & ThisWorkbook.Sheets("SharePoint Download").Range("SharePointPath").Value '/user:MyDomain\MyUserName MyPassword
MsgBox "The sharepoint path is mapped as network drive.", vbInformation
Else
MsgBox "The mapped network drive already exists.", vbInformation
End If
End Sub
'_________________________________________________________________________________
Sub DownloadFiles()
Dim Directory As String
Dim file As String
Dim i As Long
Dim fso As FileSystemObject
Application.ScreenUpdating = False
If Dir("Q:\", vbDirectory) = "" Then
MsgBox "There is no mapped network drive", vbCritical
Exit Sub
End If
'DownloadFolder: http://testdrive.sharepoint.ckannan.blogspot.com/teams/YourTeam/Shared Documents/PDW Status
Directory = "Q:\" & ThisWorkbook.Sheets("SharePoint Download").Range("DownloadFolder").Value & "\"
Set fso = CreateObject("Scripting.FileSystemObject")
' Get first file
file = Dir(Directory, vbReadOnly + vbHidden + vbSystem)
If file = "" Then
MsgBox "No files found in the sharepoint folder.", vbCritical
Exit Sub
End If
Do While file <> ""
fso.CopyFile Directory & file, "C:\", True
file = Dir()
Loop
Application.StatusBar = False
MsgBox "Downloaded all files to the local folder.", vbInformation
End Sub
'_________________________________________________________________________________
Sub btnLocalFolder_Click()
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "C:\"
.Title = "Please select a location to download files"
.Show
If Not .SelectedItems.Count = 0 Then
ThisWorkbook.Sheets("SharePoint Download").Range("LocalFolder") = .SelectedItems(1)
End If
End With
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com24tag:blogger.com,1999:blog-6762629203626551095.post-6963575037548595192012-09-17T12:03:00.000+05:302012-09-17T12:04:31.518+05:30Append string to a existing notepad (.txt) file in sharepoint The below code will be used to append string to an existing text file in a sharepoint folder, you can do the same function with an existing folder in your computer/ shared folder.<br />
<br />
e.g. When you want to create an user's log to a workbook in a separate file, you can use this code. <br />
<br />
<script type="syntaxhighlighter" class="brush: vb"><![CDATA[
Sub LogIn()
Call AppendTxt("\\ent51.sharepoint.hp.com\teams\ckannan\SiteAssets\Personal.txt", Now & " " & Application.UserName & vbNewLine)
End Sub
Function AppendTxt(sFile As String, sText As String)
On Error GoTo Err_Handler
Dim FileNumber As Integer
FileNumber = FreeFile ' Get unused file number
Open sFile For Append As #FileNumber ' Connect to the file
Print #FileNumber, sText ' Append our string
Close #FileNumber ' Close the file
Exit_Err_Handler:
Exit Function
Err_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: AppendTxt" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
GoTo Exit_Err_Handler
End Function
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com1tag:blogger.com,1999:blog-6762629203626551095.post-71905678896125180702012-09-05T12:32:00.002+05:302012-09-05T12:32:52.485+05:30VBA : List Special FoldersThe following code displays path of all special folders in Windows on the worksheet. Copy the following code in a general module and run(F5).<br />
<br />
e.g.<br />
<br />
<table border="0" cellpadding="0" cellspacing="0" style="width: 689px;"><colgroup><col style="mso-width-alt: 4534; mso-width-source: userset; width: 93pt;" width="124"></col> <col style="mso-width-alt: 20662; mso-width-source: userset; width: 424pt;" width="565"></col> </colgroup><tbody>
<tr height="20" style="height: 15.0pt;"> <td class="xl63" height="20" style="height: 15.0pt; width: 93pt;" width="124">Special Folder</td> <td class="xl63" style="width: 424pt;" width="565">Path</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td height="20" style="height: 15.0pt;">Desktop</td> <td>C:\Users\Public\Desktop</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td height="20" style="height: 15.0pt;">Start Menu</td> <td>C:\ProgramData\Microsoft\Windows\Start Menu</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td height="20" style="height: 15.0pt;">Programs</td> <td>C:\ProgramData\Microsoft\Windows\Start Menu\Programs</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td height="20" style="height: 15.0pt;">Startup</td> <td>C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Startup</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td height="20" style="height: 15.0pt;">Desktop</td> <td>C:\Users\Kannan\Desktop</td> </tr>
<tr height="20" style="height: 15.0pt;"> <td height="20" style="height: 15.0pt;">Roaming</td> <td>C:\Users\Kannan\AppData\Roaming</td> </tr>
</tbody></table><br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Sub GetSpecialFolderPaths()
Dim WSHShell As Object
Dim strPath As String
Dim strFolderName As String
Dim intLoop As Integer
Set WSHShell = CreateObject("Wscript.Shell")
For intLoop = 0 To WSHShell.SpecialFolders.Count - 1
strPath = WSHShell.SpecialFolders(intLoop)
strFolderName = Mid(strPath, InStrRev(strPath, Application.PathSeparator) + 1, 9999)
ActiveSheet.Cells(intLoop + 1, 1) = strFolderName
ActiveSheet.Cells(intLoop + 1, 2) = strPath
Next intLoop
Set WSHShell = Nothing
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-11398964268203633272012-09-04T19:21:00.000+05:302012-09-05T12:28:14.972+05:30VBA Excel: Create shortcut of an ApplicationThe below example create an <u>Shortcut</u> of Notepad.exe in the desktop. This will be useful when you are doing a end-to-end product package, create shortcut of your application in the desktop.<br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Sub CreateNotepadShortcut()
Dim objWSH As Object
Dim objShortCut As Object
Dim strPath As String
'----If Early Binding set a referece to Windows Script Host Object Module--
'Dim objWSH As New IWshRuntimeLibrary.IWshShell_Class
'Dim objShortCut As IWshRuntimeLibrary.IWshShortcut_Class
'==========================================================================
'----Late Binding----------------------------------------------------------
Set objWSH = CreateObject("WScript.Shell")
'==========================================================================
strPath = objWSH.SpecialFolders("Desktop") & "\" & "Notepad.lnk"
Set objShortCut = objWSH.CreateShortcut(strPath)
With objShortCut
.TargetPath = Environ("WINDIR") & "\" & "notepad.exe"
.Description = "Shortcut to Notepad"
.IconLocation = "C:\Windows\System\Shell32.dll,5"
.RelativePath = "C:\Temp"
.WorkingDirectory = "C:\"
.Hotkey = "Ctrl+Alt+C"
.Save
End With
Set objWSH = Nothing
Set objShortCut = Nothing
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-62887968851888843662012-09-04T18:40:00.000+05:302012-09-05T12:28:14.998+05:30VBA Excel: Table Intelligent SortThis is really an interesting post, will be quit useful in dashboards, scorecards and wherever high UI is needed. Like many statistical applications, data of multicolumn, if user want to sort by many categories the below code will be the best suit. <br />
<br />
Tips: If you link the data range with the chart data, you can see real magic. Sooner i will post some free codes under 'CHART' category for chart animations and many :)<br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
Dim bAscending As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then Exit Sub
'Validation for the Target cell (header of your table)
'Here we have considered as the titles are in the first row..
If Target.Rows.Count > 1 Then Exit Sub
If Target.Columns.Count > 1 Then Exit Sub
If Target.Row > 1 Then Exit Sub
If bAscending Then
mySortOrder = xlAscending
bAscending = False
Else
mySortOrder = xlDescending
bAscending = True
End If
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range(Target.Offset(1, 0), Cells(65000, Target.Column).End(xlUp)), _
SortOn:=xlSortOnValues, Order:=mySortOrder, DataOption:=xlSortNormal
.SetRange Target.CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
]]></script> <br />
<br />
<u>Worksheet Snapshot:</u><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBpgULY0RYhCCMImDboPAnPGgdZPbSaPvda8T3e_1oQ8xUsxqOPck8YTjxVhk1F4kktErdqW7mq1TVnzv4xkF-lxyY52EWi2lajNKQW3t2S0r0zfgkrFSwAF98fqtoCHJcPSv9GSJOepU/s1600/Capture.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="248" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBpgULY0RYhCCMImDboPAnPGgdZPbSaPvda8T3e_1oQ8xUsxqOPck8YTjxVhk1F4kktErdqW7mq1TVnzv4xkF-lxyY52EWi2lajNKQW3t2S0r0zfgkrFSwAF98fqtoCHJcPSv9GSJOepU/s400/Capture.PNG" width="400" /></a></div>Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-75179417454121551042012-09-04T18:15:00.000+05:302012-09-05T12:28:14.999+05:30VBA Excel: Track Cell ChangeWe have seen <u> <a href="http://ckannan.blogspot.in/2012/08/vba-excel-cell-change-tracking.html" target="_blank">Cell Change Tracking</a></u> example to track when the change has been done and who did the change, the below example is bit advanced. It will show which cell has been modified by whom and what was the previous value before the modification.<br />
<br />
<script class="brush: vb" type="syntaxhighlighter"><![CDATA[
'COPY THIS CODE INTO SHEET CHANGE EVENT
'public variable
Dim PreviousValue
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value <> PreviousValue Then
Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
Application.UserName & " changed cell " & Target.Address _
& " from " & PreviousValue & " to " & Target.Value
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PreviousValue = Target.Value
End Sub
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-28458037190223090762012-09-04T15:55:00.001+05:302012-09-05T12:28:14.983+05:30VBA Excel: Userform > Set ParentThe below code will be used to set the UserForm UF as a child of (1) the Application, (2) the Excel ActiveWindow, or (3) no parent. Returns TRUE if successful<br />
<br />
<script type="syntaxhighlighter" class="brush: vb"><![CDATA[
Function SetFormParent(UF As MSForms.UserForm, _
Parent As FORM_PARENT_WINDOW_TYPE) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SetFormParent
' Set the UserForm UF as a child of (1) the Application, (2) the
' Excel ActiveWindow, or (3) no parent. Returns TRUE if successful
' or FALSE if unsuccessful.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim UFHWnd As Long
Dim WindHWnd As Long
Dim R As Long
UFHWnd = HWndOfUserForm(UF)
If UFHWnd = 0 Then
SetFormParent = False
Exit Function
End If
Select Case Parent
Case FORM_PARENT_APPLICATION
R = SetParent(UFHWnd, Application.hwnd)
Case FORM_PARENT_NONE
R = SetParent(UFHWnd, 0&)
Case FORM_PARENT_WINDOW
If Application.ActiveWindow Is Nothing Then
SetFormParent = False
Exit Function
End If
WindHWnd = WindowHWnd(Application.ActiveWindow)
If WindHWnd = 0 Then
SetFormParent = False
Exit Function
End If
R = SetParent(UFHWnd, WindHWnd)
Case Else
SetFormParent = False
Exit Function
End Select
SetFormParent = (R <> 0)
End Function
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0tag:blogger.com,1999:blog-6762629203626551095.post-34818051941073664912012-09-04T15:51:00.000+05:302012-09-05T12:28:14.987+05:30VBA Excel: Userform > Opaque / TransparentThe below code will be used to set the <u>Opaque / Transparency Level</u> of the userform. Usually the VBA Userform doesn't have this feature, but through API functions we can do this.<br />
<br />
See <u><a href="http://ckannan.blogspot.in/2012/09/vba-excel-userform-customization.html" target="_blank">Declaration Library</a></u> post to understand and get the API function declarations.<br />
<br />
<script type="syntaxhighlighter" class="brush: vb"><![CDATA[
Function SetFormOpacity(UF As MSForms.UserForm, Opacity As Byte) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SetFormOpacity
' This function sets the opacity of the UserForm referenced by the
' UF parameter. Opacity specifies the opacity of the form, from
' 0 = fully transparent (invisible) to 255 = fully opaque. The function
' returns True if successful or False if an error occurred. This
' requires Windows 2000 or later -- it will not work in Windows
' 95, 98, or ME.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim UFHWnd As Long
Dim WinL As Long
Dim Res As Long
SetFormOpacity = False
UFHWnd = HWndOfUserForm(UF)
If UFHWnd = 0 Then
Exit Function
End If
WinL = GetWindowLong(UFHWnd, GWL_EXSTYLE)
If WinL = 0 Then
Exit Function
End If
Res = SetWindowLong(UFHWnd, GWL_EXSTYLE, WinL Or WS_EX_LAYERED)
If Res = 0 Then
Exit Function
End If
Res = SetLayeredWindowAttributes(UFHWnd, 0, Opacity, LWA_ALPHA)
If Res = 0 Then
Exit Function
End If
SetFormOpacity = True
End Function
'___________________________________________________________________
Function HWndOfUserForm(UF As MSForms.UserForm) As Long
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' HWndOfUserForm
' This returns the window handle (HWnd) of the userform referenced
' by UF. It first looks for a top-level window, then a child
' of the Application window, then a child of the ActiveWindow.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim AppHWnd As Long
Dim DeskHWnd As Long
Dim WinHWnd As Long
Dim UFHWnd As Long
Dim Cap As String
Dim WindowCap As String
Cap = UF.Caption
' First, look in top level windows
UFHWnd = FindWindow(C_USERFORM_CLASSNAME, Cap)
If UFHWnd <> 0 Then
HWndOfUserForm = UFHWnd
Exit Function
End If
' Not a top level window. Search for child of application.
AppHWnd = Application.hwnd
UFHWnd = FindWindowEx(AppHWnd, 0&, C_USERFORM_CLASSNAME, Cap)
If UFHWnd <> 0 Then
HWndOfUserForm = UFHWnd
Exit Function
End If
' Not a child of the application.
' Search for child of ActiveWindow (Excel's ActiveWindow, not
' Window's ActiveWindow).
If Application.ActiveWindow Is Nothing Then
HWndOfUserForm = 0
Exit Function
End If
WinHWnd = WindowHWnd(Application.ActiveWindow)
UFHWnd = FindWindowEx(WinHWnd, 0&, C_USERFORM_CLASSNAME, Cap)
HWndOfUserForm = UFHWnd
End Function
]]></script> Kannan Chandrasekaranhttp://www.blogger.com/profile/07497848865304258746noreply@blogger.com0