Visual Basic

Welcome to Dream.In.Code
	Getting VB Help is Easy! Join 132,302 VB Programmers for FREE! Get instant access to thousands of VB experts, tutorials, code snippets, and more! There are 1,028 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a VB Expert

Received code from friend, need help explaining it

Received code from friend, need help explaining it

Options

m1_girard	25 Feb, 2008 - 02:24 PM Post #1
New D.I.C Head Joined: 25 Feb, 2008 Posts: 6	I received the following code from one of my friends and I need to explain it to my group members for a project. This was done in the latest Microsoft Excel and was done with VBA. If someone could please explain the general overview of what the code does and give me a bit of a step by step of the code itself I would really appreciate it. CODE Sub Processor() Dim ConstantCells As Range Dim FormulaCells As Range Dim cell As Range ' Ignore Errors On Error Resume Next ' Process the constants Set ConstantCells = Selection.SpecialCells(xlConstants) For Each cell In ConstantCells If cell.Value > 0 Then cell.Interior.ColorIndex = 6 End If Next cell ' Process the formulas Set FormulaCells = Selection.SpecialCells(xlFormulas) For Each cell In FormulaCells If cell.Value > 0 Then cell.Interior.ColorIndex = 7 End If Next cell End Sub Cheers This post has been edited by m1_girard: 25 Feb, 2008 - 04:58 PM
	This Post Was Helpful! ^New!


Register for Free to Make These Ads Go Away!

Martyr2

25 Feb, 2008 - 04:35 PM

Post #2

Programming Theoretician

Joined: 18 Apr, 2007
Posts: 5,027

Windows

Thanked 173 times

Expert In: C/C++, Java, VB, VB.NET, C#, PHP, Web Development, HTML & CSS, Javascript

My Contributions


Sub Processor()
    ' Declare some variables capable of holding a range of cells in the spreadsheet
    ' A range being like cells A1 - A5

    Dim ConstantCells As Range
    Dim FormulaCells As Range
    Dim cell As Range

    ' If there were errors created, just move on to the next line after the line that caused an error
    '  Ignore Errors

    On Error Resume Next
    '  Process the constants

    ' Get that range of cells now (that are selected and are cells that have constant values in them) 
    ' and put them in our range variable.
    Set ConstantCells = Selection.SpecialCells(xlConstants)

    ' Loop through the cells one by one and if the value in that cell 
    ' is greater than 0 change its color to color index 6

    For Each cell In ConstantCells
        If cell.Value > 0 Then
            cell.Interior.ColorIndex = 6
        End If
    Next cell

    ' Now get the cells that represent our formula cells from the selection
    Set FormulaCells = Selection.SpecialCells(xlFormulas)

    ' Loop through each of these formula cells and again if the value is greater than 0
    ' Set the color of these cells to color index 7
    For Each cell In FormulaCells
        If cell.Value > 0 Then
            cell.Interior.ColorIndex = 7
        End If
    Next cell
End Sub

The end result is that you should have two sets of cells which are two different colors. One representing constants and the other set representing formula cells. Below is a nice screenshot. Notice that cell C1 is a formula cell (A1 + B1) and is a different color.

I hope this helps answer your question.

This post has been edited by Martyr2: 25 Feb, 2008 - 04:49 PM

This Post Was Helpful! ^New!

m1_girard	25 Feb, 2008 - 04:56 PM Post #3
New D.I.C Head Joined: 25 Feb, 2008 Posts: 6	Thank you very much for the help. I'm currently in an MBA program but I have to take a mandatory programming course for some odd reason (I'm a marketing/strategy major). I have some very short lines of code I also need clarified, would it be acceptable if I posted these here? I really appreciate your help in this matter. Cheers This post has been edited by m1_girard: 25 Feb, 2008 - 04:58 PM
	This Post Was Helpful! ^New!

Martyr2	25 Feb, 2008 - 04:58 PM Post #4
Programming Theoretician Joined: 18 Apr, 2007 Posts: 5,027 Thanked 173 times Expert In: C/C++, Java, VB, VB.NET, C#, PHP, Web Development, HTML & CSS, Javascript My Contributions	You are an MBA guy? Damn and here things were going so well. I am suppose to hate you guys. You are the ones that limit our budgets and cramp our cubicles! I say down with YOU! hahaha jk Please don't post any questions or code to people via PM. It is better to post them out there so everyone can see them. Plus you can get more people answering and thus get faster answers. Thanks!
	This Post Was Helpful! ^New!

m1_girard	25 Feb, 2008 - 05:14 PM Post #5
New D.I.C Head Joined: 25 Feb, 2008 Posts: 6	QUOTE(Martyr2 @ 25 Feb, 2008 - 05:58 PM) You are an MBA guy? Damn and here things were going so well. I am suppose to hate you guys. You are the ones that limit our budgets and cramp our cubicles! I say down with YOU! hahaha jk Please don't post any questions or code to people via PM. It is better to post them out there so everyone can see them. Plus you can get more people answering and thus get faster answers. Thanks! Thanks for the clarification Martyr! And I did an internship with coca cola's caffeinated beverages division, a division I'm sure many people here are very familiar with Here are the various pieces of code/general terms. I need to explain the role of each and any supporting notes. They are numbered from 1-6. CODE 1. Option Explicit 2. Dim Temp as Double 3. Application.ScreenUpdating = False 4. ActiveCell.Address 5. ActiveCell.CurrentRegion.Select 6. Dim myArray(6) 6b). What is the range of the index that can be used with this statement? Thanks again for all your help, I'll tell my friends to try and ease on the budgets in the future, but I can't say the same for cubicles haha Cheers
	This Post Was Helpful! ^New!

Martyr2	26 Feb, 2008 - 10:23 AM Post #6
Programming Theoretician Joined: 18 Apr, 2007 Posts: 5,027 Thanked 173 times Expert In: C/C++, Java, VB, VB.NET, C#, PHP, Web Development, HTML & CSS, Javascript My Contributions	1. Option Explicit - It means that everything like variables must be defined explicitly before they can be used. It is a mode in which the compiler will make sure that all your variables have been defined before use and is one of a few modes that can be used. There is another mode called Option Strict which makes the compiler harsh on the programmer by making them follow strict rules and coding conventions. 2. Dim Temp as Double - Defines a variable called "Temp" as a datatype "double". A double is a decimal value. So in this statement Temp can hold decimal values like 2.3 or -45.6665. Integers on the other hand can only hold whole numbers like -1 or 5. 3. Application.ScreenUpdating = False - This turns screenupdating off, telling excel that it doesn't have to keep repainting the screen during the execution of the macro. This is good because if excel doesn't have to process the repainting of the screen, it allows a macro to execute faster because all resources are going into the actual processing of the macro. 4. ActiveCell.Address - Returns the address of the active cell. If your active cell was on E4 then it would return the value of $E$4 as the address. 5. ActiveCell.CurrentRegion.Select - As the name suggests it selects the current region. You know how you can select multiple columns and rows and cells at once? This statement will return a range for the currently selected region. Refer to my previous post to your first question to familiarize yourself with the idea of a Range. 6. Dim myArray(6) - Declares an array (a structure holding similar values contiguously) which can be accessed using a subscript. Think of it as a single file line of shoeboxes which you can store something in each box and access each box by the order in the line. 6b). What is the range of the index that can be used with this statement? - The range is 0 through 6 or 7 elements in total. If you specify "Option base 1" then it will start at 1 and not zero. The range would then be 1 through 6 or 6 elements. I hope that helps you out. Enjoy! "At DIC we be VBA kick ass code ninjas!"
	This Post Was Helpful! ^New!

m1_girard	26 Feb, 2008 - 01:22 PM Post #7
New D.I.C Head Joined: 25 Feb, 2008 Posts: 6	QUOTE(Martyr2 @ 26 Feb, 2008 - 11:23 AM) 1. Option Explicit - It means that everything like variables must be defined explicitly before they can be used. It is a mode in which the compiler will make sure that all your variables have been defined before use and is one of a few modes that can be used. There is another mode called Option Strict which makes the compiler harsh on the programmer by making them follow strict rules and coding conventions. 2. Dim Temp as Double - Defines a variable called "Temp" as a datatype "double". A double is a decimal value. So in this statement Temp can hold decimal values like 2.3 or -45.6665. Integers on the other hand can only hold whole numbers like -1 or 5. 3. Application.ScreenUpdating = False - This turns screenupdating off, telling excel that it doesn't have to keep repainting the screen during the execution of the macro. This is good because if excel doesn't have to process the repainting of the screen, it allows a macro to execute faster because all resources are going into the actual processing of the macro. 4. ActiveCell.Address - Returns the address of the active cell. If your active cell was on E4 then it would return the value of $E$4 as the address. 5. ActiveCell.CurrentRegion.Select - As the name suggests it selects the current region. You know how you can select multiple columns and rows and cells at once? This statement will return a range for the currently selected region. Refer to my previous post to your first question to familiarize yourself with the idea of a Range. 6. Dim myArray(6) - Declares an array (a structure holding similar values contiguously) which can be accessed using a subscript. Think of it as a single file line of shoeboxes which you can store something in each box and access each box by the order in the line. 6b). What is the range of the index that can be used with this statement? - The range is 0 through 6 or 7 elements in total. If you specify "Option base 1" then it will start at 1 and not zero. The range would then be 1 through 6 or 6 elements. I hope that helps you out. Enjoy! "At DIC we be VBA kick ass code ninjas!" Thanks a lot for your help Martyr! I only have one problem left unsolved haha, I've been trying to adapt one of my previous solutions to it, but it isn't working out :S. I created a new thread for it if you have a chance.
	This Post Was Helpful! ^New!