Properties Of Equality Worksheet: Fill & Download for Free

This is a great question. Often as a programmer you will be asked to do some work with Excel and VBA. The Visual Basic language is straightforward to pick up for a programmer but the are some things I would have loved to have known when I stared using VBA.Visual Basic EditorTo enter the Visual Basic editor from Excel use Alt + F11. Using this in the editor will bring you back to Excel. This works in all Office applications(and Visio) from 2007 onwards.Immediate WindowWhen testing you code or showing examples you can write values to the Immediate Window. Use Debug.Print followed by variables, values ,expressions etc.Debug.Print "Value in cell A1 is: " & Range("A1") If this window is not visible select View->Immediate Window from the menu (or Ctrl + G). Debug.Print will print to the Immediate Windows even if the window is not visible.Where to Put Your Code - ModulesVBA has Modules which is where you write your code. There are two types:ModuleClass ModuleThe basic Module is used for global variables and procedures. The Class Module is similar to classes in C#, C++, Java etc. It is used to create an object.ProceduresThe are two types of procedures in VBA:SubsFunctionsThese are very similar but have some small differencesFunctions can return a value but Subs cannot.When you run your code the topmost procedure must be a Sub.Functions appear in the worksheet function list alongside the built in Excel functions.IMPORTANT: In VBA a Sub and a Macro are the same thing.The following code shows a simple example of a Function returning a value to a SubSub Test()   Dim s As String  ' Call function and get string  s = GetString  ' Print return value  Debug.Print s   End Sub  Function GetString() As String  ' Return the text   GetString = "Sample Text" End Function Workbooks, Worksheets and RangesAlmost everything you do in VBA will involve one or more of these three components. It's important to understand how they relate to each other.In Excel a Workbook contains a group of Worksheets and they in turn contain Cells. The hierarchy is the same in VBA. Let's look at these components individuallyThe WorkbookTo access a workbook you canopen a workbookaccess a workbook that is already openaccess the current workbook(the one with the VB code)Use the active workbook(bad idea!)The following code shows the different ways to access a workbook' Declare workbook variable  Dim wk As Workbook   ' Access a workbook that is already open  Set wk = Workbooks("Report.xlsx")  Debug.Print wk.Name   ' Open a workbook to access it  Set wk = Workbooks.Open("C:\Docs\Accounts.xlsx")  Debug.Print wk.Name    ' Access current workbook - the one that contains the code.  Debug.Print ThisWorkbook.Name For an in-depth article on Workbooks check out: The Complete Guide To Workbooks in Excel VBAThe WorksheetEvery worksheet is belong to a workbook. All workbooks have a Worksheets collection. The following code shows how to use itSub UseWorksheet()   ' Get the workbook called example.xlsx  Dim wk As Workbook  Set wk = Workbooks("example.xlsx")    ' Declare worksheet variable  Dim sh As Worksheet    ' Get worksheet called sheet 1 in Example.xlsx workbook  Set sh = wk.Worksheets("Sheet1")    ' Get worksheet called sheet1 in current workbook  Set sh = ThisWorkbook.Worksheets("Sheet1")  Debug.Print sh.name    ' Get left most sheet  Set sh = ThisWorkbook.Worksheets(1)  Debug.Print sh.name    ' Get right most sheet  Set sh = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)  Debug.Print sh.name  End Sub You can also access the worksheet using ActiveSheet. This is best avoided unless you have a specific need for the currently active sheet. If you use Worksheets() without specifying the workbook it uses the ActiveWorkbook by default.Sub UseActive()    Dim sh As Worksheet    ' Use the currently active sheet  Set sh = ActiveSheet  Debug.Print "Active sheet is: " & sh.name    ' Use the currently active workbook  Set sh = ActiveWorkbook.Worksheets(1)  Debug.Print "This sheet belongs to the workbook: " & sh.parent.name    ' no workbook mentioned is the same as using active workbook  Set sh = Worksheets(1)  Debug.Print "This sheet belongs to the workbook: " & sh.parent.name  End Sub You can access the worksheet in the current workbook(the one with the code) using the Code Name of the worksheet. This is very useful as if theuser changes the name of the worksheet your code will still work. For more on using the code name see Using the code name of the worksheetFor in-depth information about Worksheets check out: The Complete Guide To Worksheets in Excel VBACellsThe most common way to access cells is to use the Range property of the worksheet. Range refers a group of one or more cells. You can use Range to read from cells, write to cells, set the format of cells etc. The following code shows some examplesSub UseRange()    Dim sh As Worksheet  Set sh = ThisWorkbook.Worksheets("sheet1")    ' Write values to cells  sh.Range("A1:A3,B3") = 55  sh.Range("C4") = "12/12/2017"  sh.Range("F6") = "John Smith"    ' Read from cell - can only read from one cell  Dim s As String  s = sh.Range("A3")   End Sub You will often want to determine which cell to use at run time. For example, you may place a value in a cell based on the current day of the week. The cell you write to would then depend on the day of the week when the Macro runs.You can use a variable with Range to select the row at run time.Sub GetRow()    Dim sh As Worksheet  Set sh = ThisWorkbook.Worksheets("sheet1")    Dim lRow As Long  ' Ask user for row  lRow = InputBox("Please enter row number")    ' Write to the row the user entered  sh.Range("A" & lRow) = "Sample Text"   End Sub Using Cells PropertyIf you need to set the column at run time it can be tricky to convert the number to the column letter. In this case it is better to use the Cells property. Cells takes row and column number as arguments. The main difference with Range is that it returns a range of one cell.Sub UseCells()    Dim sh As Worksheet  Set sh = ThisWorkbook.Worksheets("sheet1")    ' Write to A1  sh.Cells(1, 1) = 5    ' Write to C6  Debug.Print sh.Cells(6, 3) = "Jane Smith"    ' Read from A5  Debug.Print sh.Cells(5, 1)   End Sub Get a Range of Cells Using Cells PropertyTo get more than one cell you can use the Cells property with the Range property. The following code shows how:Sub UseCells()    Dim sh As Worksheet  Set sh = ThisWorkbook.Worksheets("sheet1")    ' Write 5 to the range of cells A1:C5  sh.Range(sh.Cells(1, 1), sh.Cells(5, 3)) = 5   End Sub To find out more about Ranges and Cells check out The Complete Guide to Ranges and Cells in Excel VBAVariablesYou declare variables using Dim <name> As <type>. If you are creating an object you use New.' Declare basic variable types Dim lVal As Long Dim sName As String Dim curDate As Date Dim dDecimal As Double ' currency is a double with 4 decimal places Dim cPrice As Currency   ' Declare object Dim oShapes As New clsShapes   ' Declare Collection Dim coll As New Collection   ' Declare Static Array Dim arrStatic(0 To 10) As Long   ' Declare Dynamic Array Dim arrDynamic() As Long You assign variables like any programming language using equals. If the item is an object then you must use the key word Set.Note: When you use Set you are creating a reference to the item rather than creating a new copy.Dim lVal As Long lVal = 45   Dim wk As Workbook Set wk = Workbooks("example.xlsx") LoopsLoops are a confusing topic in VBA because the there are 4 types with one defunct and one can be used in 4 ways. I'll cover them briefly here. For more information on loops check out VBA For Loops and VBA While LoopThe four loops are :For Each NextFor NextDo LoopWhile Wend(avoid this)For Each NextThis loop is neater to write and quicker to run than the standard For loop. However it can only read in one way - the first item to thelast item. If you need a different order use the For Next Loop.Dim wk As Workbook ' Print the names of all the open workbooks For Each wk In Workbooks  Debug.Print wk.Name Next For NextThis loop is useful if you need to read through items in reverse or to do something like read every second item.The following code shows some examplesDim i As Long ' Print the names of all workbooks ' order is first opened to last For i = 1 To Workbooks.Count  Debug.Print Workbooks(i).Name Next   ' Print the names of all workbooks ' order is last opened to first For i = Workbooks.Count To 1 Step -1  Debug.Print Workbooks(i).Name Next Do LoopThis loop can be used in 4 ways. You can put the condition at the start or end of the loop and you can use While or Until which reverse conditions to each other. For example While x<10 is the same as Until x>=10.Do While <condition> Loop  Do Until <condition> Loop  Do Loop While <condition>  Do  Loop Until <condition> While WendThis was included in VBA for backward compatibility with older versions. Use the Do Loop instead.Other Useful BitsThis is a short list of some other useful items in VBAAssertions:These can be very part useful for identifying errors in your code. Use Debug.Assert followed by the condition you wish to evaluate. For more on this see How To Make Your VBA Code BulletProofDebug.Assert lMonth >= 1 And lMonth <= 12 Worksheet Functions:You can access any of the worksheet function like Count, Sum, VLookup in VBA. If you want to add a range of cells you can simply take advantage of the Sum worksheet function instead of using a loop to do it.Debug.Print WorksheetFunction.Sum(Range("A1:A5")) Compilation Arguments:You can use these to turn off certain items when developing or testing. Select Tools->VBAProject Properties and add something like "Debugging =1" to the "Conditional Compilation Arguments" textbox.Now you can use the Debugging compilation argument in your code#If Debugging = 1 Then  Debug.Assert lMonth >= 1 And lMonth <= 12 #End If Some Cool TricksRange To Array in One LineYou can fill an array from a range of cells and vice versa using just one line of code for each operation.Public Sub UseArrays()    ' Create array  Dim Marks() As Variant    ' Read 26 values into array from sheet1  Marks = Worksheets("Sheet1").Range("A1:Z1").Value    ' Write the 26 values to the third row of sheet2  Worksheets("Sheet2").Range("A3:Z3").Value = Marks   End Sub Get the Worksheet\Workbook that a Range belongs toIt's often useful when debugging to find the workbook or worksheet that a range belongs to. You can easily do this using the Parent propertySub GetParents()   Dim rg As Range  Set rg = ActiveSheet.Range("A1:A5")  ' Print the worksheet name  Debug.Print "Worksheet is: " & rg.Parent.Name  ' Print the workbook name  Debug.Print "Workbook is: " & rg.Parent.Parent.Name   End Sub Get the last Row\Column with textWhen you use VBA you will very often need to find the last row or column with text. This is one of the most commonly asked questions from VBA users. The way to do it is to find the last cell with text from the bottom(for Rows) or from the right(for Columns).Dim lLastRow As Long, lLastCol As Long   ' Get last row with text from column A lLastRow = Cells(Rows.Count, 1).End(xlUp).Row ' get last column with text from row 1  lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column   Debug.Print lLastRow, lLastCol Simple but UsefulThe following are examples of some simple but useful properties' Print the address of a range Dim rg As Range Set rg = Range(Cells(1, 1), Cells(56, 22)) Debug.Print rg.Address   ' Print current user Debug.Print Application.UserName   ' Print name, path and fullname of workbook Debug.Print ThisWorkbook.Name Debug.Print ThisWorkbook.Path Debug.Print ThisWorkbook.FullName I hope you found these tips helpful. They would have saved me a lot of time when I started using Excel VBA. If you want to see more free in-depth articles about VBA then check out my blog at Excel Macro Mastery

Personally, I like showing that VBA is equal to Python in conciseness. This example really exploits the properties of an Excel worksheet itself in sorting numbers between two columns. You can generate a lot of prime numbers quickly with 20 lines of code (actually 15 without a couple of niceties):Public Sub AnyNameYouLike()  Dim n As Double, nn As Double, c As Double  With Worksheets(1)  .Cells.ClearContents   nn = 100000   For n = 2 To nn - 1   c = n  If .Cells(n, 1) = 0 Then  .Cells(n, 2) = n  End If   Do Until c > nn  c = c + n  If c < nn Then  .Cells(c, 1) = c  End If  Loop  DoEvents  Next   End With  End Sub How it works: From a list of [math]n[/math]s eliminate every multiple of each [math]n[/math]. In my code, the outer loop runs through each [math]n[/math], and the inner loop adds [math]n[/math] to itself to make [math]c[/math]. If any subsequent [math]n[/math] is equal to [math]c[/math] - the same position in the list - eliminate it from the list of [math]n[/math]s that can be prime. The result is to divide [math]n[/math] into two lists, composite and prime, without applying a test to either one.

Microsoft classified a number of Excel functions in the “Engineering” category. These include functions for imaginary and complex numbers like IMCOS, octal and hexadecimal functions like HEX2DEC, Bessel functions like BESSELY, and a units conversion function CONVERT.You use these functions just like any other Excel function. If you use them often enough to know their parameters, type an equals sign, the name of the function, and a left parenthesis, then start entering the parameters. You may also click the fx icon to the left of the formula bar, and enter the parameters in the resulting function wizard dialog.I’m guessing that Microsoft designed these functions around the needs of electrical engineers, who frequently use complex numbers when predicting the performance of an analog circuit. Engineering functions are also useful to software engineers, who often need to deal with binary, octal and hexadecimal values.You’d think that the CONVERT function would be useful to all kinds of engineers, but I bet almost none of them use it. It has a large number of acceptable values for the input and output units, but you enter them as text rather choosing from a dropdown. If you don’t have a cheat sheet (or the help screen for that function) in front of you, you will never get the syntax right the first time.If you would like to use a function for dimensionless numbers or equations frequently used in your branch of engineering, you will need to add it as part of a purchased library or more likely as a user-defined function that you write yourself. You insert these in your worksheet formulas just like any other function, but I suggest using the fx function wizard.I wrote a number of user-defined functions in VBA that I use in various workbooks. These include functions to return physical and thermodynamic properties of water, steam, refrigerants and pure chemicals. They also include functions to support fluid dynamics and heat transfer calculations.Using these functions, I built worksheets to perform routine calculations to support engineering designs and for customer submittals. In some cases, I used the equation editor to put an image of the equation I am using onto the worksheet so a customer or other engineer reviewing my calculations can satisfy himself that I am doing it correctly.By using the user defined functions, I know that there is no mathematical error in the computations. The ease of making the calculations encourages me to tweak the inputs until I get optimum results. All in all, using these home-made engineering functions gives me a big boost in productivity, while also reducing the chance of errors.