Info Gdu
Creating User-Defined Functions 75 Case Study Custom Functions Example and Explanation 76 Useful Custom Excel Functions 77 Next Steps 102 j UDFs can be entered only into standard modules.Sheet and ThisWorkbook modules are a special type of module and if you enter the function there, Excel doesn't recognize that you are creating a UDF.
W Uxv
warnings. See alerts watches, 48-50 WCL_FTP, 347 Web. See also Web sites Protocol , 347 publishing data to, 341-342 content management systems, 344-347 custom Web pages, 343 Save as Web Page command, 341 scheduled macros canceling, 338-339 CaptureData function, 337 340-341 OnTime function, 337 Ready mode, 337-338 running every two minutes, 339-340 running x minutes in the function, 337 windows of time for updates, 338 streaming data, 336-337 Web queries, 331 creating manually, 332 creating with...
RunTime Error Method Rangeof Object Global Failed
You have code that imports a text file each day. You expect the text file to end with a total row. After importing the text, you want to convert all the detail rows to italics. The following code works fine for months TotalRow Range A65536 .End xlUp .Row FinalRow TotalRow - 1 Range A1 A amp FinalRow .Font.Italic True End Sub Then, one day the client calls with the error message shown in Figure 23.9. The Runtime Error 1004 can be caused by a myriad of things. The Runtime Error 1004 can be caused...
Using the Areas Collection to Return a Noncontiguous Range
The Areas Collection is a collection on non-contiguous ranges within a selection. It consists of individual Range objects representing contiguous ranges of cells within the selection. If the selection contains only one area, the Areas collection contains a single Range object corresponding to that selection. Refer to Figure 3.12. You need to copy only the total numbers the gray cells and paste them to a new section. You might be tempted to loop through the sheet, copy a row, and paste it to...
Custom Delete Event
Submitted by Masaru Kaji. Excel doesn't provide an event for capturing the deletion of a row or column. The following code is an Event Hack By replacing the built-in deletion choices, it provides the programmer control over what the user can delete. In the sample provided, a message box appears, informing the user of what has been deleted AssignMacro JudgeRng End Sub Private Sub AssignMacro ByVal strProc As String Dim lngld As Long Dim CtrlCbc As CommandBarControl Dim CtrlCbcRet As...
Illegal Window Closing
The userforms created in the VBE are not that different than normal windows They also include the X close button in the upper right-hand corner. Though using the button is not wrong, it can cause problems, depending on the objective of the userform. In cases like this, you may want to control what happens if the user presses the button. Use the QueryClose event of the userform to find out what method is used to close the form and code an appropriate action Private Sub UserForm_QueryClose Cancel...
Info Khj
Sometimes the VBA window seems to not respond to hovering. Because some expressions are not supposed to show a value, it is difficult to tell whether VBA is not displaying the value on purpose or whether you are in the buggy not responding mode. Try hovering over something that you know should respond such as a variable . If you get no response, then hover, actually click into the variable, and continue to hover. This tends to wake Excel up from the stupor and hovering will work again. Are you...
Info Oss
In the RowSource property of the ListBox, enter the range from which the control should draw its data. Use a dynamic named range to keep the list updated if employees are added Private Sub btn_EmpOK_Click Dim EmpFound As Range With Range Empl_ist Set EmpFound .Find lb_EmpName.Value If EmpFound Is Nothing Then MsgBox Employee not found lb_EmpName.Value Exit Sub tb_EmpPosition .Offset 0, 1 tb_HireDate .Offset 0, 2 End With End If End With End Sub Using the Multi-Select Property of a ListBox...
Info Wik 1
' ToolBar Change CommandBars ToolBar List .Enabled True EnableControl 295, False ' cells EnableControl 296, False ' Rows EnableControl 297, False ' Cols ' amp Delete EnableControl 478, False ' Edit gt Delete EnableControl 292, False ' amp Delete EnableControl 293, False ' Row EnableControl 294, False ' Column EnableControl 847, False ' RightClick Tab ' Clear EnableControl 21, False ' cut EnableControl 19, False ' copy EnableControl 22, False ' paste EnableControl 755, False ' pastespecial '...
Info Eab
Another use of a unique list of values is to quickly populate a list box or a combo box on a userform. Say that you have a macro that can run a report for any one specific customer. To allow your clients to choose which customers to report, create a simple userform. Add a list box to the userform and set the list box's MultiSelect property to 1-fmMultiSelectMulti. I named my form frmReport. In addition to the list box, I have four command buttons OK, Cancel, Mark All, Clear All. The code to run...
API Examples
The following sections provide more examples of useful API declarations you can use in your Excel programs. Each example starts with a short description of what the example can do, followed by the actual declaration s , and an example of its use. This API function returns the computer name. This is the name of the computer found under MyComputer, Network Identification Private Declare Function GetComputerName Lib kernel32 Alias _ GetComputerNameA ByVal lpBuffer As String, ByRef nSize As Long As...
Disable Cut Copy and Paste 1
This program disables enables all methods of cutting, copying, and pasting data in Excel Option Private Module Sub EnableAllClear ' Insert EnableControl 295, EnableControl 296, EnableControl 297, ' amp Delete EnableControl 478, EnableControl 292, EnableControl 293, EnableControl 294, EnableControl 847, ' Clear EnableControl 3125, True EnableControl 1964, True EnableControl 872, EnableControl 873, EnableControl 874, ' CutCopyPaste EnableControl 21, True EnableControl 19, True EnableControl 22,...
Using a Scrollbar as a Slider to Select Values
Chapter 9 discussed using a spin button control to allow someone to choose a date. The spin button is good, but it allows clients to adjust up or down by only one unit at a time. An alternative method is to draw a horizontal scrollbar in the middle of the userform and use it as a slider. Clients can use arrows on the ends of the scrollbar like the spin button arrows, but they can also grab the scrollbar and instantly drag it to a certain value. The userform shown in Figure 21.12 includes a...
Info Plw
.ColumnGrand False .RowGrand False .NullString 0 End With 1 Calc the pivot table PT.ManualUpdate False PT.ManualUpdate True 1 PT.TableRange2 contains the results. Move these to J10 1 as just values and not a real pivot table. PT.TableRange2.Offset 1, 0 .Copy WSD.Range J10 .PasteSpecial xlPasteValues 1 At this point, the worksheet looks like Figure 12.8 1 Delete the original Pivot Table amp the Pivot Cache PT.TableRange2.Clear Set PTCache Nothing End Sub 1 Delete the original Pivot Table amp the...
Info Roa
'Import data to the first table and in the first column of a ten-row table. For Each wdCell In wdDoc.Tables 1 .Columns 1 .Cells i i 1 wdCell.Range.Text vaData i, 1 Next wdCell 'Save and close the document. With wdDoc .Save .Close End With 'Close the hidden instance of Microsoft Word. wdApp.Quit 'Release the external variables from the memory Set wdDoc Nothing Set wdApp Nothing MsgBox The data has been transfered to Test.doc., vblnformation End Sub
How To Draw In Vba Excel
XY points were plotted in a scatter chart to draw this shape. XY points were plotted in a scatter chart to draw this shape. The concept of using XY charts for drawing has been perfected by Mala Singh of XLSoft Consulting in India. Mala has been able to use XY charts to draw just about anything. This blueprint is actually an XY chart in Excel see Figure 10.21 . This blueprint is actually an XY Chart in Excel. It takes about 25 seconds to draw the chart. This blueprint is actually an XY Chart in...
Adding Graphics to a UserForm
ggj A listing on a form can be even more helpful if a corresponding graphic is added to the form, as shown in Figure 9.12. The following code displays the photograph corresponding to the selected employee from the ListBox Private Sub lb_EmpName_Change Dim EmpFound As Range With Range EmpList Set EmpFound .Find lb_EmpName.Value If EmpFound Is Nothing Then MsgBox Employee not found lb_EmpName.Value tb_EmpPosition .Offset 0, 1 tb_HireDate .Offset 0, 2 On Error Resume Next Img_Employee.Picture...
Convert Week Number into Date
Ever receive a spreadsheet report and all the headers showed the week number I don't know about you, but I don't know what Week 15 actually is. I'd have to get out my calendar and count the weeks. And what if you need to look at a past year What we need is a nice little function that will convert Week Year into the date of the Monday for that week, as shown in Figure 4.12. Str The Week information to be converted. The result must be formatted as a date. Function ConvertWeekDay Str As String As...
Cell Progress Indicator
This example builds a progress indicator in Column C based on entries in Columns A and B see Figure 13.12 Private Sub Worksheet_Change ByVal Target As Range If Target.Column gt 2 Or Target.Cells.Count gt 1 Then Exit Sub If Application.IsNumber Target.Value False Then Application.EnableEvents False Application.Undo Application.EnableEvents True MsgBox Numbers only please. Exit Sub End If If Target.Value gt Target.Offset 0, 1 .Value Then Application.EnableEvents False Application.Undo...
Info Psk
At this point, you can more or less see how just about every option that you do in Excel while the macro recorder is running corresponds to a bit of code in the recorded macro. Here is another example. The next line of code in the macro is Selection.End xlDown .Select You can click and get help for three topics in this line of code Selection, End, and Select. Assuming that Selection and Select are somewhat self-explanatory, click in the word End and press F1 for help. A Context Help dialog box...
A Ani
A1 references, 117 A1Style function, 121 above-average records, 224 absolute references, 25-27, 122-123 ADO ActiveX Data Objects , 402-404 case study, 403-404 connecting to, 404 DAO Data Access 412-413 inserting, 413-414 MDB Multidimensional Database files, 401 reading data from, 404 records adding, 404 deleting, 404, 410 inserting, 404-406 retrieving, 406-407 summarizing, 410-411 updating, 408-409 AccountingReport function, 261-262 ActiveX controls, attaching macros to, 494 Add function, 32,...
Creating a Vanilla Pivot Table in Excel Interface
Although they are the most powerful feature in Excel, Microsoft estimates that pivot tables are used by only 7 of Excel users overall. Based on surveys at MrExcel.com, about 42 of advanced Excel users have used pivot tables. Because a significant portion of you have never used pivot tables, I will walk through the steps of building a pivot table in the user interface. If you are already a pivot table pro, jump ahead to the next section. Let's say you have 50,000 rows of data, as shown in Figure...
Info Cge
MyMsg Do you want to Continue Response MsgBox myMsg, vbExclamation vbYesNoCancel, myTitle Select Case Response Case Is vbYes ActiveWorkbook.Close SaveChanges False Case Is vbNo ActiveWorkbook.Close SaveChanges True Case Is vbCancel MyMsg Do you want to Continue Response MsgBox myMsg, vbExclamation vbYesNoCancel, myTitle Select Case Response Case Is vbYes ActiveWorkbook.Close SaveChanges False Case Is vbNo ActiveWorkbook.Close SaveChanges True Case Is vbCancel A MsgBox can be used to display...
Separate Delimited String
You need to paste a column of delimited data. You could use Excel's Text to Columns, but you need only an element or two from each cell. Text to Columns parses the entire thing. What you need is a function that lets you specify the number of the element in a string that you need, as shown in Figure 4.13. StringElement str,chr,ind str The string to be parsed. chr The delimiter. ind The position of the element to be returned. Function StringElement str As String, chr As String, ind As Integer...
Info Qws
The complete syntax is Cht.Location Where, Name The possible values of the Where argument are xlLocationAsNewSheet and xlLocationAsObject, or xlLocationAutomatic. The name of the chart sheet to convert the embedded chart to when Where is xlLocationAsNewSheet The name of the sheet that will hold the embedded chart sheet when Where is xlLocationAsObject The macro recorder often records unnecessary code. This is one such example. Notice that when we used the line Charts.Add, we already specified...
Using AutoSort to Control the Sort Order
The Excel user interface offers an AutoSort option that enables you to show customers in descending order based on revenue. It is possible to create pivot tables for years without ever finding this setting. In the Excel user interface, double-click the Customer button in the pivot report. This brings up the PivotTable Field dialog, as shown in Figure 12.12. This dialog is the gateway to the AutoSort feature. This dialog is the gateway to the AutoSort feature. From the PivotTable Field dialog,...
Adding a Table on the Fly
This code uses a pass-through query to tell Access to run a Create Table command ' This creates tblReplenish 1 There are five fields 1 Style 1 B Auto replenishment level for B stores ' C Auto replenishment level for C stores ' Define the connection MyConn J transfers.mdb .Provider Microsoft.Jet.OLEDB.4.0 .Open MyConn End With Set cmd New ADODB.Command Set cmd.ActiveConnection cnn 'create table cmd.CommandText CREATE TABLE tblReplenish Style Char 10 Primary Key, A int, B int, C Int, RecActive...
Info Tbk 1
After clicking Advanced, you get to the powerful PivotTable Field Advanced Options dialog. Here, you can specify that customers should be sorted based on Descending revenue. Retrieve external data for all page field items raster performance Query external data source as you select each page field Item requires less memory f Disable pivoting of this field recommended AutoSort options O Manual you can drag items to rearrange them O Ascendrig Descending
Retrieve Numbers from Mixed Text
This function extracts and returns numbers from text that is a mix of numbers and letters, as shown in Figure 4.11. RetrieveNumbers Rng The argument is myString The text containing the numbers to be extracted. Function RetrieveNumbers myString As String Dim i As Integer, j As Integer Dim OnlyNums As String 'starting at the END of the string and moving backwards Step -1 For i Len myString To 1 Step -1 'IsNumeric is a VBA function that returns True if a variable is a number 'When a number is...
Summarizing Records via ADO
One of Access's strengths is running summary queries that group by a particular field. If you build a summary query in Access and examine the SQL view, you will see that complex queries can be written. Similar SQL can be built in Excel VBA and passed to Access via ADO. The following code uses a fairly complex query to get a net total by store ' This builds a table of net open 1 on Styles AI1 Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Basic Logic Get all open Incoming Transfers by...
Static Random
The function RAND can be very useful for creating random numbers, but it constantly recalculates. What if you need random numbers, but don't want them to change constantly The following function places a random number, but the number changes only if you force the cell to recalculate, as shown in Figure 4.19. StaticRAND There are no arguments for this function. Function StaticRAND As Double Randomize STATICRAND Rnd End Function Produce random numbers not quite so volatile.
Reverse the Contents of a Cell
This function is mostly fun, but you may find it useful it reverses the contents of a cell. ReverseContents myCell, IsText IsText Optional, should the cell value be treated as Text Default or a number. Function example Function ReverseContents myCell As Range, Optional IsText As Boolean True Dim i As Integer Dim OrigString As String, NewString As String OrigString Trim myCell 'remove leading and trailing spaces For i 1 To Len OrigString 'by adding the variable NewString to the character,...
Info Bcp
Building a content management system with these tools is simple. To the membership directory Excel file, I added two worksheets. In the worksheet called Top, I copied the HTML needed to generate the navigation bar of the Web site. To the worksheet called Bottom, I copied the HTML needed to generate the end of the HTML page. The simple Bottom worksheet is shown in Figure 14.14. Two worksheets are added to the membership directory workbook. One lists the HTML code necessary to draw the navigation...
The Alphabet Soup of XML
Note that in the previous example, we had only an XML file and Excel was able to accurately read the data, allow editing, and write the data back out for use by another application. Two additional file types schemas and transforms enhance XML files. Although the XML file contains the data and field names, an XML Schema file defines data relationships and data validation requirements. For example, a zip code field could require five numeric digits. XML Schemas are typically stored in XSD files....
Nesting One Loop Inside Another Loop
It is okay to run a loop inside another loop. Perhaps the first loop is running through all the rows in your recordset. The second loop then might run through all the columns in your recordset. Consider the dataset in Figure 5.10 By nesting one loop inside the other,VBA can loop through each row and then each column. I 1 c E5 t f c h i i 1 Wen_ ,1-ut'j hrtiuarir MiiCh O h r NmBrntbn U' 2 hSShweTSI l 572,63 i LhJj i .3 5 98 i,i7i ffl i 6 4,396 1,538336 i wj ,1'J,' 156 rPtms ,533 7sa -j rflwHi...
Info Cdh
.OnKey c ' , .OnKey v ', .OnKey DEL ', .OnKey INSERT .CellDragAndDrop .OnDoubleClick End With ' ToolBar Change CommandBars ToolBar List Sub DisAbleAllCLear ' Insert EnableControl 295 EnableControl 296 EnableControl 297 ' amp Delete EnableControl 478 EnableControl 292 EnableControl 293 EnableControl 294 EnableControl 847 ' Clear EnableControl 21, EnableControl 19, EnableControl 22, EnableControl 755, ' CutCopyPaste EnableControl 3125 EnableControl 1964 EnableControl 872, EnableControl 873,...
WorksheetCalculate
Worksheet_Calculate occurs after a sheet is recalculated. The following sample compares a month's profits between the previous and the current year. If profit has fallen, a red down arrow appears below the month if profit has risen, a green up arrow appears see Figure 8.5 Private Sub Worksheet_Calculate Select Case Range C3 .Value Case Is gt Range C4 .Value SetArrow 10, msoShapeDownArrow Case Is lt Range C4 .Value SetArrow 3, msoShapeUpArrow End Select End Sub Private Sub SetArrow ByVal...
The Default Chart Type
You will notice that code does not specify the chart type being created, yet a column chart was produced. On my machine, the default chart type for Excel is a column chart. This is an important feature of Excel VBA. Defaults that are saved as Excel settings need not be explicitly specified in VBA code. This can work for you or against you. On the positive side, if you need to create many charts of one particular type, you can change the default chart type on your machine and produce code to...
Adding an Image on the Fly
There is some unpredictability in adding images. Any given image might be shaped either landscape or portrait. The image might be small or huge. The strategy you might want to employ is to let the image load full size by setting the .AutoSize parameter to True before loading the image TC Image amp PicCount Me.Controls.Add bstrProgId forms.image.1' Me.Controls TC .Top LastTop Me.Controls TC .Left LastLeft Me.Controls TC .AutoSize True On Error Resume Next Me.Controls TC .Picture LoadPicture...
Adding Controls at Runtime
It is possible to add controls to a userform at runtime. This is very convenient if you are not sure how many items you will be adding to the form. Figure 21.8 shows a very plain form. It has only one button. This plain form is used to display any number of pictures from a product catalog. The pictures and accompanying labels appear at runtime, as the form is being displayed. Flexible forms can be created if you add most controls at runtime. Figure 21.8 shows a very plain form. It has only one...
Using ApplicationOnTime to Periodically Analyze Data
VBA offers the .OnTime method for running any VBA procedure at a specific time of day or after a specific amount of time has passed. You could write a macro that would capture data every hour throughout the day. This macro would have times hard-coded. The following code will theoretically capture data from a Web site every hour throughout the day Application.OnTime EarliestTime TimeValue 8 00 AM , _ Procedure CaptureData Application.OnTime EarliestTime TimeValue 9 00 AM , _ Procedure...
Info Ocr 1
setting. In this case, you must specify that the BaseField is ShipDate and that the Baseltem is something called previous ' Set up change from prior month With PT.PivotFields Revenue .Orientation xlDataField .Function xlSum .Caption Change .Calculation xlPercentDifferenceFrom .BaseField ShipDate .Baseltem previous .Position 3 .NumberFormat 0.0 End With Note that with positional calculations, you cannot use the AutoShow or AutoSort methods. This is too bad it would be interesting to sort the...
Exporting Charts as Images
It is very easy to export a chart as a GIF image file. The following code saves a picture of a chart to your hard drive as a GIF file Set Cht Cht.Export FileName C MyChart.gif, FilterName GIF End Sub This can come in useful in three possible scenarios Displaying Charts in a Web Page You might have a Web page that displays charts. It is easy to set up the Web page to always display lt Img src MyChart.gif gt . You can use Excel to import data, produce the chart, and save it as MyChart.gif. Saving...
Basic Error Handling with the On Error GoTo Syntax 1
3. Write the code to handle the error. If you want to return control of the macro to the line after the one that caused the error, use the statement Resume Next. In your macro, just before the line that may likely cause the error, add a line reading On Error GoTo MyErrorHandler. Note that in this line, you do not include the colon after the label name. Immediately after the line of code that you suspect will cause the error, add code to turn off the special error handler. This is very...
Controls and Collections
In Chapter 20, Creating Classes, Records, and Collections, several labels on a sheet were grouped together into a collection. With a little more code, these labels were turned into help screens for the users. Userform controls can be grouped into collections, too, and take advantage of class modules. The following example checks or unchecks all the check boxes on the userform, depending on which label the user chooses. Place the following code in the class module, clsFormEvents. It consists of...
Case Study Custom FunctionsExample and Explanation
Let's build a custom function used to add two values. After we've created it,we'll use it on a worksheet. Insert a new module in the VBE.Type the following function into the module. It is a function called Add that will total two numbers in different cells.The function has two arguments Number1 is the first number to add Number2 is the second number to add Function Add Number1, Number2 As Integer Add Number1 Number2 End Function Arguments are placed in parentheses after the name of the...
Errors Caused by Different Versions
Microsoft does improve VBA in every version of Excel. Pivot table creation was drastically improved between Excel 97 and Excel 2000. Certain chart features were improved between Excel 97 and Excel 2000. The TrailingMinusNumbers parameter is new in Excel 2002. If you write code in Excel 2002 or Excel 2003 and then send the code to a client with Excel 2000, that user gets a compile error as soon as she tries to run any code in the same module as the offending code. Consider this application with...
Info Xdi
The following macro specifies a secondary value Y axis for the chart and plots the Xdata series on the secondary Y axis. It results in the chart shown in Figure 10.13 Sub SecondaryAxisDemo Dim Cht As Chart Set Cht Charts Chart1 2 End Sub When one series has numbers of a vastly different scale,add a secondary axis to the chart. Sub SecondaryAxisDemo Dim Cht As Chart Set Cht Charts Chart1 2 End Sub When one series has numbers of a vastly different scale,add a secondary axis to the chart. Grid...
The Data SeriesVBA Name Series
Each series in a chart is a member of the SeriesCollection collection. The sample chart has two data series Xdata and Ydata . The VBA syntax for specifying a given series is Index can be a number ranging from 1 to the number of series in the chart or the name of the series. When you click any data point column belonging to Xdata on the chart, you see the following formula in the formula bar Sheet1 A 1 represents the name of the data series Xdata . By default, the second argument is null because...
Info Kaa 1
This BeforeDoubleClick event prevents the normal result of a double-click from taking place Private Sub xlChart_BeforeDoubleClick ByVal ElementID As Long, _ ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean Cancel True End Sub This BeforeRightClick event prevents the normal result of a right-click from taking place Private Sub xlChart_BeforeRightClick Cancel As Boolean Cancel True End Sub Now that the normal actions of the double-click and right-click have been controlled,...