January 10,
am by Dick Kusleika
Use the FormulaArray property of the Range object to enter array formulas in a cell from VBA.
The array formula must be 255 characters or less or you’ll see the ever-helpful “Unable to set the FormulaArray property of the Range class” error.
Dave sent me a solution to this problem that’s shown below.
It replaces part of the formula with a place holder, then uses the Replace method of the Range object to add the rest.
Apparently Replace doesn’t have such limitations as FormulaArray does.
By the way, he uses
cool calendar formula (as seen in the
comments) as his example.
I love that formula, although that doesn’t really have anything to do with this post.
”’ If the long formula can be broken into parts, where the second part can
”’ be replaced by a dummy function, this approach can be used…
Public Sub LongArrayFormula()
& & Dim theFormulaPart1 As String
& & Dim theFormulaPart2 As String
& & theFormulaPart1 = “=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-“ & _
& & & & & & & & & & & & & “MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
& & & & & & & & & & & & & “(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
& & & & & & & & & & & & & &#;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),”“”“,” & _
& & & & & & & & & & & & & “X_X_X())”
& & & & & & & & & & & & &
& & theFormulaPart2 = “DATE(YEAR(NOW()),MONTH(NOW()),1)-“ & _
& & & & & & & & & & & “(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+” & _
& & & & & & & & & & & &#;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)”
& & With ActiveSheet.Range(&#”)
& & & & .FormulaArray = theFormulaPart1
& & & & .Replace “X_X_X())”, theFormulaPart2
& & & & .NumberFormat = “mmm dd”
& & End With
Nice work, Dave.
Thanks for the suggestion.
Category: Uncategorized
[cc_vb]Block of code goes here[/cc_vb][cci_vb]Inline code goes here[/cci_vb][cc]Formula goes here[/cc]
Leave a Reply
Advertisement
Recent Posts
Select Month
April 2016
February 2016
January 2016
December 2015
November 2015
October 2015
September 2015
August 2015
April 2015
March 2015
February 2015
January 2015
December 2014
November 2014
October 2014
September 2014
August 2014
April 2014
March 2014
February 2014
January 2014
December 2013
November 2013
October 2013
September 2013
August 2013
April 2013
March 2013
February 2013
January 2013
December 2012
November 2012
October 2012
September 2012
August 2012
April 2012
March 2012
February 2012
January 2012
December 2011
November 2011
October 2011
September 2011
August 2011
April 2011
March 2011
February 2011
January 2011
December 2010
November 2010
October 2010
September 2010
August 2010
April 2010
March 2010
February 2010
January 2010
December 2009
November 2009
October 2009
September 2009
August 2009
April 2009
March 2009
February 2009
January 2009
December 2008
November 2008
October 2008
September 2008
August 2008
April 2008
March 2008
February 2008
January 2008
December 2007
November 2007
October 2007
September 2007
August 2007
April 2007
March 2007
February 2007
January 2007
December 2006
November 2006
October 2006
September 2006
August 2006
April 2006
March 2006
February 2006
January 2006
December 2005
November 2005
October 2005
September 2005
August 2005
April 2005
March 2005
February 2005
January 2005
December 2004
November 2004
October 2004
September 2004
August 2004
April 2004
March 2004
January 2004
Categories
Categories
Select Category
Access&&(1)
ActiveX Controls&&(3)
Add-ins&&(10)
Application Object&&(1)
Array formulas&&(5)
Arrays&&(9)
Automation&&(12)
Books&&(5)
Bugs&&(16)
Charting&&(18)
Classes&&(21)
Collections&&(4)
Columns and Rows&&(1)
COM Addins&&(1)
CommandBars&&(1)
Controls&&(2)
Copy and Paste&&(1)
Counting and Summing Functions&&(2)
Data Analysis&&(6)
Data Validation&&(1)
Data Visualization&&(15)
Databases&&(1)
Date Functions&&(2)
Dictionaries&&(1)
Downloads&&(15)
Enumerations&&(1)
Events&&(5)
Excel 14&&(24)
Excel 15&&(9)
Excel 2013&&(6)
Excel Advanced&&(13)
Excel Basic&&(12)
Excel Intermediate&&(18)
Excel Mashup&&(3)
Excel Services&&(2)
Excel’s UI&&(3)
Excel12&&(15)
External Data&&(5)
File Operations&&(11)
Filtering&&(2)
Fluent UI&&(1)
For Normal People&&(5)
Formatting&&(8)
Formulas&&(11)
Games&&(4)
General&&(6)
Goal Seek&&(2)
Hyperlinks&&(1)
JavaScript&&(1)
Keyboard&&(29)
Links&&(5)
Lookups&&(2)
Math and Stats Functions&&(4)
Miscellany&&(18)
Modules&&(1)
Names&&(2)
Navigation&&(1)
Outlook&&(7)
Performance&&(3)
Pivot Tables&&(3)
PivotTables&&(7)
Polls&&(1)
Posts by Jeff&&(4)
PowerPivot&&(4)
Printing&&(1)
Products&&(9)
Range Object&&(5)
RegEx&&(4)
Slicers&&(2)
Sports&&(3)
Tables&&(3)
Templates&&(1)
Training&&(4)
Tutorials&&(2)
UI Design&&(3)
Uncategorized&&(301)
User Defined Functions&&(7)
User Interface&&(11)
Userforms and Controls&&(11)
Variables and Data Types&&(1)
VBA Advanced&&(8)
VBA Basics&&(8)
VBA Code Library&&(3)
VBA Functions and Keywords&&(2)
Visual Basic Editor&&(7)
Weblogs&&(1)
Windows API&&(6)
Worksheet Functions&&(9)上传时间：
和龙妹妹在一起的日子
专辑名称：
专辑创建者：
视频数：32
播放次数：25,971
56官方微信
扫一扫发现精彩ASCII HTML Codes - HTML Special Characters in the ASCII Set
Share this page on:
Your suggestion is on its way!
An email with a link to:
was emailed to:
Thanks for
with others!
Most Emailed Articles
HTML Codes - ASCII Special Characters
HTML codes to put ASCII special characters on your Web page
The following list includes the HTML codes for the ASCII symbols used on Web pages. The first section includes the first 255 character codes and their related HTML codes. Then, at the bottom you'll find some other symbols that don't have ASCII designations and the HTML codes to create them. Not all browsers support all the codes, so be sure to test your HTML codes before you use them.
Characters for Specific Languages and Uses:
Friendly Code
Numerical Code
Description
Horizontal Tab
Exclamation point
Double quote
Number sign
Dollar sign
Percent sign
Ampersand (and sign)
Single quote
Left parenthesis
Right parenthesis
Asterisk (star)
Minus (hyphen)
Forward slash
Semi-colon
Less-than sign
Equal sign
Greater-than sign
Question mark
Left square bracket
Back slash
Right square bracket
Underscore
Grave accent
Lowercase a
Lowercase b
Lowercase c
Lowercase d
Lowercase e
Lowercase f
Lowercase g
Lowercase h
Lowercase i
Lowercase j
Lowercase k
Lowercase l
Lowercase m
Lowercase n
Lowercase o
Lowercase p
Lowercase q
Lowercase r
Lowercase s
Lowercase t
Lowercase u
Lowercase v
Lowercase w
Lowercase x
Lowercase y
Lowercase z
Left curly brace
Vertical bar
Right curly brace
Not defined
Single low-quote
Function symbol (lowercase f with hook)
Double low-quote
Double dagger
Per million symbol
Capital esh
Left single angle quote
OE ligature
Capital ž
Left single-quote
Right single-quote
Left double-quote
Right double-quote
Small bullet
Lowercase esh
Right single angle quote
oe ligature
Lowercase ž
Uppercase y-umlaut
Non-breaking space
Inverted exclamation point
Pound currency sign
Currency sign
Yen currency sign
Broken vertical bar
Section symbol
Umlaut (Diaeresis)
Feminine ordinal indicator (superscript lowercase a)
Left angle quote
Soft hyphen
Registered sign
Degree sign
Plus/minus sign
Superscript 2
Superscript 3
Acute accent
Micro sign
Pilcrow sign (paragraph)
Middle dot
Superscript 1
Masculine ordinal indicator (superscript o)
Right angle quote
One quarter fraction
One half fraction
Three quarters fraction
Inverted question mark
A grave accent
A accute accent
A circumflex
AE ligature
E circumflex
I circumflex
N tilde (enye)
O circumflex
Multiplication sign
U circumflex
SZ ligature
a circumflex
ae ligature
e circumflex
i circumflex
o circumflex
Division symbol
u circumflex
Jennifer Kyrnin
Related Articles
Web Design/HTML
See More About
Explore Web Design/HTML
Must Reads
Most Popular
See More About:
By Category
ASCII HTML Codes - HTML Special Characters in the ASCII Set&. All rights reserved.> 问题详情
若有说明：int n=2，*p=&n，*q=p；，则以下非法的赋值语句是()。A．p=q；B．*p=*q；C．n=*q；D．p=n：
悬赏：0&答案豆
提问人：匿名网友
发布时间：
若有说明：int n=2，*p=&n，*q=p；，则以下非法的赋值语句是( )。A．p=q；B．*p=*q；C．n=*q；D．p=n：
网友回答（共0条）
#include main(){\tint n, *p=NULL;\t*p=&n;若有定义:int x,y;char a,b,c;并有以下输入数据(此处代表换行符，Ц代表空格
我有更好的答案
您可能感兴趣的试题
1设有数组定义：chararray[]="China"；则数组array所占的空间为(&)个字节。A．4B．5C．6D．72下面程序的输出结果是(&)。&&func(int X)&&{&static int a=2；&&return&(a+=X)；}&&main()&&{&int b=2，c=4，d；&&d=func(b)；&&d=func(c)；&&&&printf("%d\n"，d)；&&}A．4B．6C．8D．12
相关考试课程
请先输入下方的验证码查看最佳答案
图形验证：
验证码提交中……