VBA Range.Formula

[King]

Hello,

I have a problem with below VBA code to read the cells formula into VBA

Dim objSheet As Excel.Worksheet
Set objSheet = ThisWorkbook.Sheets("Master")
Dim varSheetData As Variant
varSheetData = objSheet.Range("A9:FB820").Formula

when above line is execute, a error 1004 Application-defined or
object-defined error was occur

but if the code change to read cells value
varSheetData = objSheet.Range("A9:FB820").Value
its ok and give me a array contain all the data...

is there anything I was missing or it just not supported??

Thanks, King.

 

[JLGWhiz]

Probably too complicated to program the return of the formulas, since the
precedents and dependents could also be involved. However, it will return a
single cell formula.

 

[Tim Williams]

Reading into a variant array doesn't work with .Formula

Tim

 

[King]

Thanks, Tim,

I will try change the code to read the cells with formula one by one...

King

 

[Tim Zych]

Hmm, it works for me.

Dim varSheetData As Variant
varSheetData = Range("A9:FB820").Formula

Just tested it.

In the immediate window:
?varSheetData(812,158)
=ADDRESS(ROW(),COLUMN(),4)

As an alternative try FormulaR1C1. Then convert back using:

application.ConvertFormula(varSheetData(812,158) ,xlR1C1,xlA1)

 

[King]

Ohh..........

I got the problem, its because the worksheet that I want to read the formula
is protected,
I can now read the formula by cancel the protection first and enable back
after read.

King

 

[Tim Williams]

I guess I should test before posting...

Tim

Hmm, it works for me.

Dim varSheetData As Variant
varSheetData = Range("A9:FB820").Formula

Just tested it.

In the immediate window:
?varSheetData(812,158)
=ADDRESS(ROW(),COLUMN(),4)

As an alternative try FormulaR1C1. Then convert back using:

application.ConvertFormula(varSheetData(812,158) ,xlR1C1,xlA1)

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility