一、For循环
基本用法
for (语句 1; 语句 2; 语句 3) {
要执行的代码块
}
function 读取(){
for(let RowNum=2;RowNum<=5;RowNum=RowNum+1){
console.log(Range("a"+RowNum).Value());
console.log(Range("b"+RowNum).Value());
console.log('-----------')
}
}
function 求和(){
for(let IntNum=2;IntNum<=5;IntNum++){
Range("d"+IntNum).Value2=Range("b"+IntNum).Value()+Range("c"+IntNum).Value()
}
}
function 读取工作表名称(){
for(let ShsC=1;ShsC<=Sheets.Count;ShsC++){
console.log(Worksheets(ShsC).Name)
}
}
function 九九乘法表(){
for(let a =1;a<=9;a++){
for(let b=1;b<=a;b++){
Cells(a,b).Value2=a+"X"+b+"="+a*b
}
}
}
for in循环
for/in语句可以读取数组中的下标(索引号),或者对象的属性。
function test1(){
var arr=[12,13,456,4564,45];//相当于[0:12,1:13,2:456,3:4564,4:45]
for(let intIndex in arr){
console.log(intIndex+"-"+arr[intIndex]);
}
}
function test2(){
var obj={a:100,b:200,c:"rong"};//数组
for(let keys in obj){
console.log(keys+"-"+obj[keys]);
}
}
function 求和(){
var Arr1=Range("b2:b6").Value();
var Sums=0
var arr2=""
for(let keys in Arr1){
arr2 += Arr1[keys];//直接提取Arr1[keys]仍然是数组
Sums += Arr1[keys][0]
}
console.log(arr2);
console.log(Sums)
}
for of循环
使用 for of 循环可以轻松的遍历数组或者其它可遍历的对象,例如字符串、集合等。
function test1(){
let arr=[33,56,2,243,654];
for (let intValue of arr){
console.log(intValue);
}
for (let strValue of "who am i?"){
console.log(strValue);
}
}
function 求和(){
let totals = 0;
for(let Rng of ["b2:b6","c2:c6","d2:d6"]){
for(let RngCells of Range(Rng)){
totals +=RngCells.Value();
}
Console.log(totals);
totals=0;
}
}
二、while循环
function test(){
let intNum=0;
while (intNum<=5){
console.log("OK"+intNum);
intNum++;
}
}
三、IF分支语句
if (条件) {
// 如果条件为真,执行这里的代码块
} else {
// 如果条件为假,执行这里的代码块
}
function test1(){
for (let intNum=2;intNum<=11;intNum++){
let intScore=Cells(intNum,"B").Value();
if (intScore>=80){
Cells(intNum,"C").Value2="✔"
}else{
Cells(intNum,"C").Value2="✘"
}
}
}
多分支
function 判断(){
for(let Rngcell of Range("b2:b11")){
let Rngvalue = Rngcell.Value();
if(Rngvalue >= 90){
Rngcell(1,2).Value2 = "优"
}else if(Rngvalue >= 80){
Rngcell(1,2).Value2 = "中"
}else if(Rngvalue >= 60){
Rngcell(1,2).Value2 = "良"
}else{
Rngcell(1,2).Value2= "差"
}
}
}
多条件
function 判断(){
for(let RngV=2;RngV<=11;RngV++){
if(
Range("b"+RngV).Value2>=120 &&
Range("c"+RngV).Value2>=120 &&
Range("d"+RngV).Value2>=120
){
Range("e"+RngV).Value2 = "合格"
}
}
}
function test2(){
for (let intNum=2;intNum<=11;intNum++){
if(Cells(intNum,2).Value()>=100){
if (Cells(intNum,3).Value()>=120){
if (Cells(intNum,4).Value()>=120){
Cells(intNum,6).Value2="YES";
}
}
}
}
}
四、Switch分支语句
function test(){
let strContent=InputBox("输入数字");
switch (Number(strContent)){
case 1:
alert("A");break;
case 2:
alert("B");break;
case 3:
alert("C");break;
default:
alert("Other");break;
}
}
function 判断(){
for(let rngcell of Range("b2:b12")){
let rngvalue = rngcell.Value();
switch(true){
case rngvalue>=90:
rngcell(1,2).Value2 = "优";break;
case rngvalue>=80:
rngcell(1,2).Value2 = "良";break;
case rngvalue>=60:
rngcell(1,2).Value2 = "中";break;
default:
rngcell(1,2).Value2 = "差";
}
}
}
五、Break语句
break语句在单独使用时,会导致包含它的循环或switch语句立即退出。
function test(){
let intTotal=0;
for (let intRow=3;intRow<=9;intRow++){
for (let intCol=2;intCol<=13;intCol++){
let intAmount=Cells(intRow,intCol).Value();
intTotal +=intAmount;
if (intTotal>=1000){
Cells(intRow,"N").Value2=`${intTotal}-${intCol-1}月`;
break;
}
}
intTotal=0;
}
}
六、Continue语句
continue语句只能在循环体内使用,它不会退出循环,只是不再执行continue后面的语句,而是继续执行下一次循环。
function test2(){
for (let rngCell of Range("b2:b10")){
if (rngCell.Value()<80){continue}
rngCell.Interior.Color = 65535;
rngCell.Font.Color = 255;
}
}
七、try catch语句
function test1(){
try{
console.Log("123")
}
catch(err){
alert(err);
alert(err.message);
}
}
function test2(){
let intNum=1;
while (true){
try{
alert(Sheets(intNum).Name);
}
catch{
alert("没表了");
break;
}
intNum++;
}
}