[ Pobierz całość w formacie PDF ]
.You can accomplish the same effect by using Excel s SUBSTITUTE function,which is much more efficient than using a custom VBA function.The follow-ing formula, for example, removes all space characters from the text in cellA1.=SUBSTITUTE(A1, , ) 4800-x Ch24.F 8/27/01 11:59 AM Page 622622 Part VI: Developing Custom Worksheet FunctionsUsing Comments in Your CodeA comment is descriptive text embedded within your code.VBA completely ignoresthe text of a comment.It s a good idea to use comments liberally to describe whatyou do (because the purpose of a particular VBA instruction is not always obvious).You can use a complete line for your comment, or you can insert a commentafter an instruction on the same line.A comment is indicated by an apostrophe.VBA ignores any text that follows an apostrophe up until the end of the line.Anexception occurs when an apostrophe is contained within quotation marks.Forexample, the following statement does not contain a comment, even though it hasan apostrophe:Result = Can t calculate The following example shows a VBA Function procedure with three comments:Function MYFUNC() This function does nothing of valuex = 0 x represents nothingness Return the resultMYFUNC = xEnd FunctionWhen developing a function, you may want to test it without including a partic-ular instruction or group of instructions.Instead of deleting the instruction, simplyconvert it to a comment by inserting an apostrophe at the beginning.VBA thenignores the instruction(s) when the routine is executed.To convert the commentback to an instruction, delete the apostrophe.The VB Editor s Edit toolbar contains two very useful buttons.Select a groupof instructions and then use the Comment Block button to convert theinstructions to comments.The Uncomment Block button converts a groupof comments back to instructions.Using Variables, Data Types,and ConstantsA variable is a named storage location in your computer s memory.Variables canaccommodate a wide variety of data types from simple Boolean values (TRUE orFALSE) to large, double-precision values (see the following section).You assign avalue to a variable by using the assignment operator, which is an equal sign. 4800-x Ch24.F 8/27/01 11:59 AM Page 623Chapter 24: VBA Programming Concepts 623The following are some examples of assignment statements that use varioustypes of variables.The variable names are to the left of the equal sign.Each state-ment assigns the value to the right of the equal sign to the variable on the left.x = 1InterestRate = 0.075LoanPayoffAmount = 243089DataEntered = Falsex = x + 1MyNum = YourNum * 1.25HallOfFamer = Tony Gwynn DateStarted = #3/14/2001#VBA has many reserved words, which are words that you can t use for variableor procedure names.If you attempt to use one of these words, you get an error mes-sage.For example, although the reserved word Next may make a very descriptivevariable name, the following instruction generates a syntax error:Next = 132Unfortunately, sometimes syntax error messages aren t descriptive.The preced-ing instruction generates a syntax error in Excel 2002 (earlier versions of Excelmay produce a different error).So if an assignment statement produces an errorthat does not seem to make sense, check the online help to make sure that yourvariable name does not have a special use in VBA.Defining Data TypesVBA makes life easy for programmers because it can automatically handle all of thedetails involved in dealing with data.Data type refers to how data is stored inmemory as integers, real numbers, strings, and so on.Although VBA can take care of data typing automatically, it does so at a cost slower execution and less efficient use of memory.If you want optimal speed foryour functions, you need to be familiar with data types.Generally, it s best to usethe data type that uses the smallest number of bytes, yet still be able to handle allof the data that will be assigned to it.When VBA works with data, execution speedis a function of the number of bytes that VBA has at its disposal [ Pobierz całość w formacie PDF ]