Excel VBA using a few Named Ranges in a formula

Question

I am trying to use a few Named Ranges together in a formula in VBA, however it seems to take very long for excel to calculate. The Named Ranges have almost 70k of data each (All have the same number of rows). Below is an example of the code:

Dim i, erow, ecol As Long

erow = Sheets("Sheet1").Range("A11").End(xlDown).Row
ecol = Sheets("Sheet1").Range("A11").End(xlToRight).Column

Sheets("Sheet1").Range(Cells(12, ecol + 2), Cells(erow, ecol + 2)).Formula = "=(0.03 - Ith_LT) * (a_LI_LT * 0.03 ^ 2 + b_LI_LT * 0.03 + c_LI_LT)

Ith_LT, a_LI_LT, b_LI_LT, c_LI_LT are the named ranges.

I am trying to speed this up by first storing the named ranges in memory then calling it but to no avail. Below is my code.

Dim i, erow, ecol As Long
Dim Ith, a, b, c As Variant

erow = Sheets("Sheet1").Range("A11").End(xlDown).Row
ecol = Sheets("Sheet1").Range("A11").End(xlToRight).Column
Ith = Range("Ith_LT")
a = Range("a_LI_LT")
b = Range("b_LI_LT")
c = Range("c_LI_LT")
Sheets("Sheet1").Range(Cells(12, ecol + 2), Cells(erow, ecol + 2)).Value = (0.03 - Ith) * (a * 0.03 ^ 2 + b * 0.03 + c)

When i run this, a type mismatch error pops up. Is there any way to make this work? or are there anyway to make this code run faster? Thank you.

explicitly declare i and erow as variants if that is what you want. Check this is not causing problems. Though i think you want longs. — QHarr
– QHarr, Commented Oct 25, 2017 at 5:55

INOPIAE · Accepted Answer · 2017-10-25 05:54:57Z

1

In your variable declaration you only define the last varibale with the given type:

Dim i, erow, ecol As Long
Dim Ith, a, b, c As Variant

Instead of variant you should use the type range instead of variant for the second line

Dim Ith as range, a as range, b as range, c  as range

Afterwards you use this line to use the named ranges

Set Ith = Range("Ith_LT")

answered Oct 25, 2017 at 5:54

INOPIAE

2931 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Imran Malek · Accepted Answer · 2017-10-25 09:41:41Z

0

@Zabjaku Based on your comment I'm assuming all ranges are single column and the final output is to be populated as a result of formula then you can use this.

This will fill your output column and if the named range changes you don't have to change your formula.

Sheets("Sheet1").Range(Cells(12, ecol + 2), Cells(erow, ecol + 2)).Value = "=(0.03-" & Replace(Range("Ith_LT").Cells(1, 1).Address, "$", "") & ")*(" & Replace(Range("a_LI_LT").Cells(1, 1).Address, "$", "") & "*0.03^2+" & Replace(Range("b_LI_LT").Cells(1, 1).Address, "$", "") & "*0.03+" & Replace(Range("c_LI_LT").Cells(1, 1).Address, "$", "") & ")"

answered Oct 25, 2017 at 9:41

Imran Malek

1,7392 gold badges13 silver badges14 bronze badges

Collectives™ on Stack Overflow

Excel VBA using a few Named Ranges in a formula

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related