VBA在Excel中的应用（一)：改变符合条件单元格的背景颜色
时间： 10:00:57
&&&& 阅读：20130
&&&& 评论：
&&&& 收藏：0
标签：&&&&&&&&&&&&&&&&&&　　在使用excel处理数据的时候，为了能更清晰的标示出满足特定条件的单元格，对单元格添加背景色是不错的选择。手工处理的方式简单快捷，但是当遇到大批量数据，就会特别的费时费力，而且不讨好（容易出错）。通过代码来处理是个不错的选择，excel可以通过VBA编程来处理内部数据，在打开excel页面后，可以通过&alt + F11&组合键来启动VBA编程界面，跟VB的编程界面和语法一样，需要注意的是如何调用excel的内容。VBA通过sheet, range和cells三个层次来调用excel中的制定区域，sheet指定要处理的表格页，ranges制定要处理的范围，可以是一个单元格，也可以是多个单元格，cells(i,j)通过制定行下标i和列下表j来定位制定的单元格，通过这三个层次就基本能定位excel中的任意制定位置。
& & &关于vba和excel编程，请详见：
& & & & & & & & & & & & & & & & & & & & & & &
& & &实例：在做验证性因子分析（Confirmed Factoe Analysis，CFA）的时候，我们通常因子载荷（factor loading）来判断构念（construct）之间的区别效度（discriminant validity），即同一构念下的item之间correlation尽可能高，而不同构念的item之间的相关性尽可能低。所以将correlation matrix中大于没特定阈值单元格用特定背景颜色加以区分，就可以清晰的看出item之间的相关关系。
　　假设我们有一个64*64的correlation matrix，那么在excel中是用vba对不同范围的correlation值的单元格添加背景颜色的代码如下：
1 Sub changeBgColor()
Dim i As Integer
Dim j As Integer
Dim r As Integer
Dim c As Integer
r = 67 ‘最后一行是第67行
c = 66 ‘最后一列是第66列
For i = 3 To r ‘迭代，从第3行开始，一直到最后一行
For j = 2 To c
‘迭代，从第2列开始，一直到最后一列
If Cells(i, j) & 0.5 And Cells(i, j) & 0.6 Then
‘如果单元格Cells(i,j)的值大于0.5下于0.6，那么背景颜色代码为42
Cells(i, j).Interior.ColorIndex = 42
If Cells(i, j) & 0.6 And Cells(i, j) & 0.7 Then
Cells(i, j).Interior.ColorIndex = 43
If Cells(i, j) & 0.7 And Cells(i, j) & 0.8 Then
Cells(i, j).Interior.ColorIndex = 6
If Cells(i, j) & 0.8 And Cells(i, j) & 1 Then
Cells(i, j).Interior.ColorIndex = 3
32 End Sub
　　结果如下所示：
& 附1：excel中的颜色代码（colorIndex）
&&标签：&&&&&&&&&&&&&&&&&&
&&国之画&&&& &&
版权所有 京ICP备号-2
迷上了代码！Excel VBA. FREE Excel VBA Code & Excel Macro Help
Excel VBA - Excel Macros
| &Excel VBA & Macro Code Tips, Tricks
Excel Formulas, Tip, Tricks and Feature Examples
Excel VBA INDEX Page 1 Of
All Excel downloads below here are free. List of the most popular asked questions in the
. Excel Formulas through to Excel VBA macro code. An ever growing list of popular questions and answers on both Excel, Excel macros and Excel VBA The Macros here show us multiple ways to have our VBA Macro Code apply to specific Worksheets. Copy code into any standard Module in the Excel VBE. Here are many examples that can be used to create Excel charts with a difference. The Excel charting pages listed here show many different charting techniques, examples and solutions. Collect data or ranges from you users This page is a must if you are wanting to work with dates and times in an Excel spreadsheet. It explains how Excel stores dates and times, give the magic numbers needed for dates and times and date and time conversions and functions. Here's how to create relatively easy Date Adder by using a UserForm, 3 TextBox Controls, 5 Label Controls, 1 ComboBox Control and 1 CommandButton Control. Macro code here transpose every x rows into columns. If you record Excel macros, as apposed to writing them, you will often wish to stop the screen flicker caused by your Excel macro selecting cells and Worksheets. A Variable is used to store temporary information that is used for execution within the Procedure, Module or Workbook.& Before we go into some detail of Variables, there are a few important rules that you must know about.
In Excel, when coding in VBA, we can use what are know as variables to store information. These variables (as the name suggests) can be varied and changed to store different data information. As soon as a variable loses scope it loses its stored value. Excel has the ability to automatically run/execute any Excel macro code of our choosing when opening an Excel Workbook. Excel has the ability to automatically run/execute any Excel macro code of our choosing when data in any cell on a Worksheet changes. We can do this via the Worksheet_Change event. We can even specify which cell/cells must be changed before running our code. Add 2 CommandButtons to your UserForm that will move your multi-column ListBox items up and down. Use Array Elements to fill range, chosen by user, complete with headings.Unlike Access, Excel does not allow for us to mask time and/or date entries. However, we can use the Worksheet_Change Event to achieve masked time entries
One of the best methods for multiple conditions is the Select Case Statement. It does away with the need for multiple If Statements which can make Excel Macros very hard to read and decipher. These Excel VBA conversion functions can be use to convert from one data type to another. Custom Excel Function (Excel Macros) that can be used to calculate tax based on a sliding scale, or bracket tax. are used in Excel VBA Macro code to tell Excel to repeat a block of code until a condition is TRUE, FALSE, a specified number of times, or to loop through a Collection or Array. I would like to show you 2 ways to restrict looping by using the COUNTIF Function with the Find Method. The 1st code uses a whole cell match, while the 2nd uses a part cell match. Excel VBA allows us to send emails from within Excel and even send the Workbook as an attachment. In Excel we can use Excel VBA code to determine the number of pages that will be printed from the active sheet. Excel VBA UserForms and their associated controls are a great way to present/collect data from users. There are often occasions however when we need to loop through all controls on a UserForm, or only certain specified controls. Shapes are those from the Drawing toolbar or the Forms toolbar. The code shows how to loop through them and access their Properties. The Excel VBA macro code here can be used in any Excel Workbook, or
for sorting Excel Sheets. In Excel 2000, Microsoft introduced the ability to color the Sheet name tabs. The Excel VBA macro code here can be used in any Excel Workbook that is at least Excel 2000, or higher. Adding worksheets to Excel is very simple The Excel VBA macro code here can be used in any Excel Workbook. When run, it will sort the 1 dimensional array of strings, MyArray When we write VBA code it is often desired to have the VBA Macro code not visible to end-users. This is to protect your intellectual property and/or stop users messing about with your code. With the aid of some Excel VBA code placed the private module of the Workbook Object (ThisWorkbook) we can password protect a Worksheet from viewing to all those that do not know the password. for Valid Dates Easy to follow steps on how to create a user friendly Excel Calendar using Excel VBA. The Excel macro code here will create a calendar on a new Worksheet. The current day will always be highlighted dynamically each day. Some examples on deleting rows in Excel that meet a criteria. Most of these methods are far more efficient and quicker that the conventional ways of using a loop in Excel VBA. One very common question is: &how can I delete rows from my Excel Worksheet based on a specified criteria, or condition?& The code here can be used to delete the module which houses the code. In other words, it deletes itself after running once The code here can be used to delete all Event code within ThisWorkbook The macro here can be used to delete Sheet Code based on the
supplied to the
within the Sheets Collection
. It's ideal to use when you need to copy Sheets from a Workbook but do not want the code to copy with the Sheets. If you need to have a list of options that decrease as users select from it, you can
and some Excel VBA Code With the aid of Excel VBA and the Worksheet Activate Event we can restrict the scrollable area on any Excel Worksheet. When working with Excel, many users end up with a lot of different Worksheets which then makes navigation cumbersome. This page shows you how to create a Index Sheet via Excel VBA. It is often asked how one can have a date, or date and time, entered into a corresponding cell after data in entered into other cells. Let's suppose you need the current date entered into Column &B&, on the same row as Column &A& data is entered. The date entered must be static so that means we cannot simply use the TODAY(), or NOW() functions. The Excel Find Method is an excellent tool to use when writing Excel VBA, or Excel macros.
The Find Method is an excellent method to use in Excel VBA macro code. It is many hundreds of times faster than a
, which is often erroroneously used to locate data. However, things become somewhat tricky when using the Find Method to Find Dates. Even recorded Excel Macros can fall over. With the aid of Excel VBA we can write a custom formula/function, or user defined function to extract out the nth word from a text string.Onto
Page 2Excel 2007 Macros From MicrosoftExcel Macros From Microsoft&&&& 50% Off! Free Choice of
on all purchases totaling over $64.00. ALL
totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to
31 days after purchase date. Instant Download and Money Back Guarantee on Most SoftwareTry out:
| (best value) |
Technical Analysis in Excel With $139.00 of FREE software!Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师