您的位置：程序门 -> vb -> vba

excel vba中数组怎么赋值？

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

excel vba中数组怎么赋值？[已结贴,结贴人:winmancn]

发表于：2008-01-25 14:37:06 楼主

dim aa（) as double
aa = newdoublearray（1732.55,1834.45,1936.36,2038.27,2140.19,2240.1,2344.02,2445.95,2547.88,2649.82, _ 2751.76,2853.71,2955.66,3057.62,3159.58,3261.55,3363.53,3465.5,3567.48,3669.46, _ 3771.43, 3873.41,3975.39,4077.37,4179.35,4281.33,4383.31)

一运行就弹出 “不可以对数组赋值”

发表于：2008-01-25 14:52:291楼得分:0

回答者顺便告诉如何结贴

发表于：2008-01-25 15:34:102楼得分:0

发表于：2008-01-25 15:51:253楼得分:0

如何结贴可以看公告，或http://topic.csdn.net/u/20080110/19/7cb462f1-cac6-4c28-848e-0a879f4fd642.html

发表于：2008-01-28 08:46:094楼得分:0

dim aa as variant
aa = array（1732.55,1834.45,1936.36,2038.27,2140.19,2240.1,2344.02,2445.95,2547.88,2649.82, _
2751.76,2853.71,2955.66,3057.62,3159.58,3261.55,3363.53,3465.5,3567.48,3669.46, _
3771.43,3873.41,3975.39,4077.37,4179.35,4281.33,4383.31)
这样就可以给数组赋值。

我试过了，这个在一个sub 里的是可以的，我的问题或许是我没写清楚
我在public 里定义了变量，因为要在很多sub里用到。
然后在一个sub里赋值，再用你的方法就不行了。
是不是我赋值的方式有问题呢？？我正在研究。。。
这个初始化模块的内容如果移动到public 下面运行就会出现无效外部过程 array（

这个初始化赋值以前我是把它放在 excel 表格内以明码方式存取，现在想把它放到程序里让外面干净些，就遇到了这个麻烦。

发表于：2008-01-28 09:16:345楼得分:0

可以的，我试了

public aa as variant

private sub command1_click（)
aa = array（1732.55, 1834.45, 1936.36, 2038.27, 2140.19, 2240.1, 2344.02, 2445.95, 2547.88, 2649.82, _
2751.76, 2853.71, 2955.66, 3057.62, 3159.58, 3261.55, 3363.53, 3465.5, 3567.48, 3669.46, _
3771.43, 3873.41, 3975.39, 4077.37, 4179.35, 4281.33, 4383.31)
end sub

aa必须定义为variant，否则此方法不能用。

发表于：2008-01-28 09:42:406楼得分:0

晕，还是无效外部过程。

发表于：2008-01-28 09:43:337楼得分:0

把你的代码贴出来看看

发表于：2008-01-28 10:08:598楼得分:0

public i, j, k as integer
public day, month, year, p, q, startpoint, no, dd, w, dl, smn, smo, linenum, startline, linecount, poingnum（99) as integer
public g（100), v（100), time1（100), r（100), t4（100), t5（100), h（100), hg（100), jdg（100) as single
public a（30), b（30), c（30), d（30), e（30), f（30), a9（30), b9（30), ad（30), bd（30), c9（79), d9（79) as variant
public hh（100, 3), bug（100), nn（100), ip（100), lp（100), yp（100), y（100) as single
public longitude, latitude, aoa, bob, x, gt, ve, dg, dr, dt, h0, y0, x0, p1, pm as single
public gutichao（100), weidugaizheng（100), piaoyigaizheng（100), bugegaizheng（100), zhonglizhi（100), zhongliyi（100) as single

public wksh, myname, mypath, bgycname as string
public newsheet
a（) = array（1721.71, 1823.05, 1924.4, 2025.76, 2127.13, 2228.52, 2329.92, 2431.34, 2532.77, 2634.23, 2735.7, 2837.19, 2938.69, 3040.22, 3141.76, 3243.32, 3344.9, 3446.49, 3548.11, 3649.74, 3751.4, 3853.07, 3954.76, 4056.47, 4158.2, 4259.95, 4361.72)
b（) = array（1.01338, 1.01349, 1.01362, 1.01375, 1.01387, 1.01402, 1.01418, 1.01435, 1.01452, 1.0147, 1.01489, 1.01508, 1.01526, 1.01543, 1.01561, 1.01576, 1.01595, 1.01614, 1.01634, 1.01653, 1.01673, 1.01693, 1.01712, 1.01731, 1.01749, 1.01767, 1.01784)
c（) = array（1746.77, 1849.56, 1952.35, 2055.15, 2157.95, 2260.76, 2363.57, 2466.38, 2569.2, 2672.03, 2774.86, 2877.69, 2980.53, 3083.37, 3186.22, 3289.09, 3391.94, 3494.81, 3597.68, 3700.56, 3803.44, 3906.33, 4009.23, 4112.13, 4215.03, 4317.94, 4420.85)
d（) = array（1.02791, 1.02794, 1.02798, 1.02801, 1.02805, 1.0281, 1.02815, 1.0282, 1.02825, 1.02829, 1.02833, 1.02839, 1.02841, 1.02849, 1.02855, 1.02862, 1.02867, 1.02873, 1.0288, 1.02885, 1.0289, 1.02895, 1.02899, 1.02903, 1.02906, 1.02908, 1.02908)
e（) = array（1774.82, 1879.25, 1983.68, 2088.13, 2192.59, 2297.07, 2401.56, 2506.07, 2610.59, 2715.13, 2819.69, 2924.25, 3028.83, 3133.42, 3238.02, 3342.63, 3447.26, 3551.9, 3656.56, 3761.22, 3865.9, 3970.59, 4075.28, 4179.99, 4284.71, 4389.44, 4494.17)
f（) = array（1.04425, 1.04435, 1.0445, 1.0446, 1.04475, 1.04495, 1.0451, 1.04525, 1.0454, 1.04555, 1.04565, 1.04575, 1.0459, 1.046, 1.04615, 1.0463, 1.0464, 1.04655, 1.04665, 1.04675, 1.04685, 1.04695, 1.0471, 1.04715, 1.04725, 1.04735, 1.04745)
a9（) = array（1732.55, 1834.45, 1936.36, 2038.27, 2140.19, 2240.1, 2344.02, 2445.95, 2547.88, 2649.82, 2751.76, 2853.71, 2955.66, 3057.62, 3159.58, 3261.55, 3363.53, 3465.5, 3567.48, 3669.46, 3771.43, 3873.41, 3975.39, 4077.37, 4179.35, 4281.33, 4383.31)
b9（) = array（1.01906, 1.01908, 1.01911, 1.01914, 1.01917, 1.01922, 1.01927, 1.01931, 1.01936, 1.01941, 1.01947, 1.01953, 1.01959, 1.01963, 1.01969, 1.01973, 1.01975, 1.01977, 1.01978, 1.01979, 1.01979, 1.01979, 1.0198, 1.01979, 1.01978, 1.01976, 1.01975)
ad（) = array（0, 10.835, 21.663, 32.487, 43.308, 54.128, 64.948, 75.768, 86.588, 97.409, 108.23, 119.051, 129.872, 140.692, 151.511, 162.33, 173.147, 183.964, 194.781, 205.597, 216.41)
bd（) = array（1.08347, 1.08284, 1.08241, 1.0821, 1.08199, 1.08199, 1.08199, 1.08199, 1.0821, 1.0821, 1.0821, 1.0821, 1.08199, 1.08189, 1.08189, 1.08178, 1.08168, 1.08168, 1.08157, 1.08136)
c9（) = array（0, 102.018, 204.028, 306.028, 408.019, 510.006, 611.986, 713.962, 815.933, 917.899, 1019.861, 1121.819, 1223.776, 1325.733, 1427.69, 1529.648, 1631.609, 1733.572, 1835.538, _
1937.506, 2039.477, 2141.451, 2243.429, 2356.411, 2447.397, 2549.388, 2651.384, 2753.385, 2855.392, 2957.405, 3059.425, 3161.449, 3263.478, 3365.511, 3467.547, 3569.585, 3671.624, 3773.664, _
3875.704, 3977.745, 4079.787, 4181.828, 4283.868, 4385.907, 4487.944, 4589.978, 4692.009, 4794.036, 4896.058, 4998.076, 5100.088, 5202.094, 5304.092, 5406.083, 5508.064, 5610.033, 5711.99, 5813.935, _
5915.865, 6017.781, 6119.681, 6221.562, 6323.42, 6425.256, 6527.068, 6628.854, 6730.612, 6832.344, 6934.047, 7035.72)
d9（) = array（1.02018, 1.0201, 1.02, 1.01991, 1.01987, 1.0198, 1.01976, 1.01971, 1.01966, 1.01962, 1.01958, 1.01957, 1.01957, 1.01957, 1.01958, 1.01961, 1.01963, 1.01966, 1.01968, 1.01971, 1.01974, _
1.01977, 1.01982, 1.01987, 1.01991, 1.01996, 1.02001, 1.02007, 1.02013, 1.0202, 1.02024, 1.02029, 1.02033, 1.02036, 1.02038, 1.02039, 1.0204, 1.0204, 1.02041, 1.02042, 1.02041, 1.0204, 1.02039, 1.02037, _
1.02034, 1.02031, 1.02027, 1.02022, 1.02018, 1.02012, 1.02006, 1.01998, 1.01991, 1.01981, 1.01969, 1.01957, 1.01945, 1.0193, 1.01916, 1.019, 1.01881, 1.01858, 1.01836, 1.01812, 1.01785, 1.01759, 1.01732, _
1.01703, 1.01674, 1.01642)

发表于：2008-01-28 10:16:289楼得分:0

续前

sub readzlgzh（)

'dim i, j, day, month, year, startpoint, q as integer
'dim g（100), v（100), time1（100), r（100), t4（100), t5（100), h（100), hg（100) as single
'dim a（100), b（100), c（100), d（100), e（100), f（100), a9（100), b9（100), hh（100, 3) as single
'dim ad（100), bd（100), c9（99), d9（99), bug（100), nn（100), ip（100), lp（100) as single

dim c9（), d9（) as variant

'c9 = newdoublearray（0, 102.018, 204.028, 306.028, 408.019, 510.006, 611.986, 713.962, 815.933, 917.899, 1019.861, 1121.819, 1223.776, 1325.733, 1427.69, 1529.648, 1631.609, 1733.572, 1835.538, 1937.506, 2039.477, 2141.451, 2243.429, 2356.411, 2447.397, 2549.388, 2651.384, 2753.385, 2855.392, 2957.405, 3059.425, 3161.449, 3263.478, 3365.511, 3467.547, 3569.585, 3671.624, 3773.664, 3875.704, 3977.745, 4079.787, 4181.828, 4283.868, 4385.907, 4487.944, 4589.978, 4692.009, 4794.036, 4896.058, 4998.076, 5100.088, 5202.094, 5304.092, 5406.083, 5508.064, 5610.033, 5711.99, 5813.935, 5915.865, 6017.781, 6119.681, 6221.562, 6323.42, 6425.256, 6527.068, 6628.854, 6730.612, 6832.344, 6934.047, 7035.72)
'd9 = newdoublearray（1.02018, 1.0201, 1.02, 1.01991, 1.01987, 1.0198, 1.01976, 1.01971, 1.01966, 1.01962, 1.01958, 1.01957, 1.01957, 1.01957, 1.01958, 1.01961, 1.01963, 1.01966, 1.01968, 1.01971, 1.01974, 1.01977, 1.01982, 1.01987, 1.01991, 1.01996, 1.02001, 1.02007, 1.02013, 1.0202, 1.02024, 1.02029, 1.02033, 1.02036, 1.02038, 1.02039, 1.0204, 1.0204, 1.02041, 1.02042, 1.02041, 1.0204, 1.02039, 1.02037, 1.02034, 1.02031, 1.02027, 1.02022, 1.02018, 1.02012, 1.02006, 1.01998, 1.01991, 1.01981, 1.01969, 1.01957, 1.01945, 1.0193, 1.01916, 1.019, 1.01881, 1.01858, 1.01836, 1.01812, 1.01785, 1.01759, 1.01732, 1.01703, 1.01674, 1.01642)
'"c": reading number reduce to gravity value' 读数变化到重力值根重力仪有关的常数
'不同仪器的不同数据
'range（"b4", range（"b4").end（xltoright)).select
'range（"d4").end（xldown).select
'a = array（1721.71, 1823.05, 1924.4, 2025.76, 2127.13, 2228.52, 2329.92, 2431.34, 2532.77, 2634.23, 2735.7, 2837.19, 2938.69, 3040.22, 3141.76, 3243.32, 3344.9, 3446.49, 3548.11, 3649.74, 3751.4, 3853.07, 3954.76, 4056.47, 4158.2, 4259.95, 4361.72)
' b（) = array（1.01338, 1.01349, 1.01362, 1.01375, 1.01387, 1.01402, 1.01418, 1.01435, 1.01452, 1.0147, 1.01489, 1.01508, 1.01526, 1.01543, 1.01561, 1.01576, 1.01595, 1.01614, 1.01634, 1.01653, 1.01673, 1.01693, 1.01712, 1.01731, 1.01749, 1.01767, 1.01784)
' c（) = array（1746.77, 1849.56, 1952.35, 2055.15, 2157.95, 2260.76, 2363.57, 2466.38, 2569.2, 2672.03, 2774.86, 2877.69, 2980.53, 3083.37, 3186.22, 3289.09, 3391.94, 3494.81, 3597.68, 3700.56, 3803.44, 3906.33, 4009.23, 4112.13, 4215.03, 4317.94, 4420.85)
' d（) = array（1.02791, 1.02794, 1.02798, 1.02801, 1.02805, 1.0281, 1.02815, 1.0282, 1.02825, 1.02829, 1.02833, 1.02839, 1.02841, 1.02849, 1.02855, 1.02862, 1.02867, 1.02873, 1.0288, 1.02885, 1.0289, 1.02895, 1.02899, 1.02903, 1.02906, 1.02908, 1.02908)
' e（) = array（1774.82, 1879.25, 1983.68, 2088.13, 2192.59, 2297.07, 2401.56, 2506.07, 2610.59, 2715.13, 2819.69, 2924.25, 3028.83, 3133.42, 3238.02, 3342.63, 3447.26, 3551.9, 3656.56, 3761.22, 3865.9, 3970.59, 4075.28, 4179.99, 4284.71, 4389.44, 4494.17)
' f（) = array（1.04425, 1.04435, 1.0445, 1.0446, 1.04475, 1.04495, 1.0451, 1.04525, 1.0454, 1.04555, 1.04565, 1.04575, 1.0459, 1.046, 1.04615, 1.0463, 1.0464, 1.04655, 1.04665, 1.04675, 1.04685, 1.04695, 1.0471, 1.04715, 1.04725, 1.04735, 1.04745)
'a9（) = array（1732.55, 1834.45, 1936.36, 2038.27, 2140.19, 2240.1, 2344.02, 2445.95, 2547.88, 2649.82, 2751.76, 2853.71, 2955.66, 3057.62, 3159.58, 3261.55, 3363.53, 3465.5, 3567.48, 3669.46, 3771.43, 3873.41, 3975.39, 4077.37, 4179.35, 4281.33, 4383.31)
' b9（) = array（1.01906, 1.01908, 1.01911, 1.01914, 1.01917, 1.01922, 1.01927, 1.01931, 1.01936, 1.01941, 1.01947, 1.01953, 1.01959, 1.01963, 1.01969, 1.01973, 1.01975, 1.01977, 1.01978, 1.01979, 1.01979, 1.01979, 1.0198, 1.01979, 1.01978, 1.01976, 1.01975)
' ad（) = array（0, 10.835, 21.663, 32.487, 43.308, 54.128, 64.948, 75.768, 86.588, 97.409, 108.23, 119.051, 129.872, 140.692, 151.511, 162.33, 173.147, 183.964, 194.781, 205.597, 216.41)
' bd（) = array（1.08347, 1.08284, 1.08241, 1.0821, 1.08199, 1.08199, 1.08199, 1.08199, 1.0821, 1.0821, 1.0821, 1.0821, 1.08199, 1.08189, 1.08189, 1.08178, 1.08168, 1.08168, 1.08157, 1.08136)
c9（) = array（0, 102.018, 204.028, 306.028, 408.019, 510.006, 611.986, 713.962, 815.933, 917.899, 1019.861, 1121.819, 1223.776, 1325.733, 1427.69, 1529.648, 1631.609, 1733.572, 1835.538, _
1937.506, 2039.477, 2141.451, 2243.429, 2356.411, 2447.397, 2549.388, 2651.384, 2753.385, 2855.392, 2957.405, 3059.425, 3161.449, 3263.478, 3365.511, 3467.547, 3569.585, 3671.624, 3773.664, _
3875.704, 3977.745, 4079.787, 4181.828, 4283.868, 4385.907, 4487.944, 4589.978, 4692.009, 4794.036, 4896.058, 4998.076, 5100.088, 5202.094, 5304.092, 5406.083, 5508.064, 5610.033, 5711.99, 5813.935, _
5915.865, 6017.781, 6119.681, 6221.562, 6323.42, 6425.256, 6527.068, 6628.854, 6730.612, 6832.344, 6934.047, 7035.72)
d9（) = array（1.02018, 1.0201, 1.02, 1.01991, 1.01987, 1.0198, 1.01976, 1.01971, 1.01966, 1.01962, 1.01958, 1.01957, 1.01957, 1.01957, 1.01958, 1.01961, 1.01963, 1.01966, 1.01968, 1.01971, 1.01974, _
1.01977, 1.01982, 1.01987, 1.01991, 1.01996, 1.02001, 1.02007, 1.02013, 1.0202, 1.02024, 1.02029, 1.02033, 1.02036, 1.02038, 1.02039, 1.0204, 1.0204, 1.02041, 1.02042, 1.02041, 1.0204, 1.02039, 1.02037, _
1.02034, 1.02031, 1.02027, 1.02022, 1.02018, 1.02012, 1.02006, 1.01998, 1.01991, 1.01981, 1.01969, 1.01957, 1.01945, 1.0193, 1.01916, 1.019, 1.01881, 1.01858, 1.01836, 1.01812, 1.01785, 1.01759, 1.01732, _
1.01703, 1.01674, 1.01642)

end sub
'test 好像可以去掉这个sub了
sub readlll（)
set newsheet = sheets.add（type:=xlworksheet)
newsheet.cells（1, 1).value = "a（j)"
newsheet.cells（1, 2).value = "b（j)"
newsheet.cells（1, 3).value = "c（j)"
newsheet.cells（1, 4).value = "d（j)"
newsheet.cells（1, 5).value = "e（j)"
newsheet.cells（1, 6).value = "f（j)"
newsheet.cells（1, 7).value = "a9（j)"
newsheet.cells（1, 8).value = "b9（j)"
newsheet.cells（1, 9).value = "c9（j)"
newsheet.cells（1, 10).value = "d9（j)"
newsheet.cells（1, 11).value = "ad（j)"
newsheet.cells（1, 12).value = "bd（j)"
newsheet.cells（1, 13).value = "r（j)"
newsheet.cells（1, 14).value = "time1（j)"
newsheet.cells（1, 15).value = "hh（j, 1)"
newsheet.cells（1, 16).value = "hh（j, 2)"
newsheet.cells（1, 17).value = "hh（j, 3)"
for j = 1 to i
newsheet.cells（j + 1, 1).value = a（j)
newsheet.cells（j + 1, 2).value = b（j)
newsheet.cells（j + 1, 3).value = c（j)
newsheet.cells（j + 1, 4).value = d（j)
newsheet.cells（j + 1, 5).value = e（j)
newsheet.cells（j + 1, 6).value = f（j)
newsheet.cells（j + 1, 7).value = a9（j)
newsheet.cells（j + 1, 8).value = b9（j)
newsheet.cells（j + 1, 9).value = c9（j)
newsheet.cells（j + 1, 10).value = d9（j)
newsheet.cells（j + 1, 11).value = ad（j)
newsheet.cells（j + 1, 12).value = bd（j)
newsheet.cells（j + 1, 13).value = r（j)
newsheet.cells（j + 1, 14).value = time1（j)
newsheet.cells（j + 1, 15).value = hh（j, 1)
newsheet.cells（j + 1, 16).value = hh（j, 2)
newsheet.cells（j + 1, 17).value = hh（j, 3)
'newsheet.cells（j+startline, 18).value = bd（j)
next j
range（"n1", range（"n1").end（xldown)).select
selection.numberformatlocal = "h:mm;@"
end sub

发表于：2008-01-28 10:18:4710楼得分:10

明白了，
不能用public a（30),改为public a as variant
a不能固定大小，并在sub中给a赋值
例：
public a as variant

sub test（)
a=array（......)
end sub

明白了？

发表于：2008-01-28 10:37:5811楼得分:0

终于过了，谢谢阿 * z_wenqian

下一篇：自动评分
上一篇：excel如何实现计算一组数据中第二大的数据

其他资讯

您的位置：程序门 -> vb -> vba

excel vba中 数组怎么赋值？

excel vba中数组怎么赋值？