# Will VBA in place of a function reduce this workbook size?

#### Mr. Walnuts

##### Board Regular
I know that VBA doesn't always slim down workbook size and calculations are calculations whether in VBA or Formula format; but in this instance, do you think VBA would be a better choice? This workbook is currently enormous in size and slow, and I think I can slim it down if I used the right VBA procedures. However, Im not certain what those procedures would be.

On two seperate worksheets in a workbook, I have a column that contains a formula that references a third sheet.

Sheet 1 = "Order"
Sheet 2 = "Turn-in"
Sheet 3 = "Bank"

Ok, here's the scenario: On the "Order" sheet, I have a list of orders from my company; on the "turn-In" sheet, a list of turn-ins. The "Bank" sheet lists ALL transactions (ORDER AND TURN-IN).

On both the turn-in and Order sheets, the formula is pretty much the same; it references a different column on the bank sheet, but does it the exact same way. Ok... The Order sheet lists all order from the company by order number (column A); the formula uses that order number and finds the transaction on the "Bank" sheet and returns the order cost as the result. however, the order numbers on the bank sheet have a 5 digit prefix on them (the prefix is the same on all orders, its used by the bank but only orders with my 5 digit prefix are on the list sent to me and pasted to this sheet. So all orders on bank sheet have the same prefix)

The below formula is in Cell C10 on "Order" Sheet; Column C lists the Cost reported by the bank. The formula below references the Bank sheet database, looks for an order number that matches the order number in A10 (Adding the prefix of course), and returns a cooresponding cost for that transaction.

=IF(ISBLANK(A10),"",IF(COUNTIF(BANK!\$A\$4:\$W\$10001,("ECGGT"&A10))>1,"",SUMIF(BANK!\$A\$4:\$W\$10001,"ECGGT"&\$A10,BANK!\$F\$4)))

I know this seems long for what it is, but it has to add the prefix, and then match the order number, and reference the cooresponding cost.

The kicker, this forumla is in more than 10,000 cells; 6,000 on the order sheet, and 5,000 on the turn-in sheet. Of course the references are a little different, but the principle is the same for all of them.

Is there a way to do this in VBA to save space and resources? This formula is really dragging.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Mr. Walnuts

##### Board Regular
Actually, is there a way to set up a VBA procedure that changes the formula to a value once the cell has returned a dollar amount?

At least that way, as the year progresses, the orders that have already returned cooresponding costs from the Bank sheet will no longer have the formula in the cell. That should reduce the resource demand.

#### shyrath

##### Active Member
Maybe I misunderstood, but you can run a macro once a week ( or time it , or run on close etc ) that looks through your column, if value > 0 ( i think that´s what you mean ) and then strip the formula by copy, then paste values.

This is what i use in my ordersheet. ( i use a dynamic range though ).

Code:
``````Sub test()

For Each c In Range("A1:A100")

If c.Value > 0 Then

c.Copy
c.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End If

Next c

End Sub``````

##### Well-known Member
Hi Walnuts,

A VBA would almost certainly reduce the size, but it may be at the expence of speed, not that we are talking a significant difference.

If no one comes up with anything simple, I could help, but I would need an example of your workbook.

Only a few rows for each sheet. You may want to change any sensitive names first.

If you do wish me to help let me know, and I'll send you a private message with my email so you can send the example.

Regards

#### FastExcel

##### Active Member
Ok... The Order sheet lists all order from the company by order number (column A); the formula uses that order number and finds the transaction on the "Bank" sheet and returns the order cost as the result.
The below formula is in Cell C10 on "Order" Sheet; Column C lists the Cost reported by the bank. The formula below references the Bank sheet database, looks for an order number that matches the order number in A10 (Adding the prefix of course), and returns a cooresponding cost for that transaction.

=IF(ISBLANK(A10),"",IF(COUNTIF(BANK!\$A\$4:\$W\$10001,("ECGGT"&A10))>1,"",SUMIF(BANK!\$A\$4:\$W\$10001,"ECGGT"&\$A10,BANK!\$F\$4)))

This formula is really dragging.

It sounds like you only have one bank transaction per Order.
If this is the case you can change the formula so that it will calculate like greased lightning.
- Sort the Bank data by Order (presumably Bank column A) then use this formula to return the cost from Column F on Bank
- =IF(ISBLANK(A10),"",if("ECGGT"&\$A10=VLOOKUP("ECGGT"&\$A10,BANK!\$A\$4:\$A\$10001,1),
VLOOKUP("ECGGT"&\$A10,BANK!\$A\$4:\$W\$10001,5),""))

This is using the approximate match option of VLOOKUP which is extremely fast on sorted data. The first VLOOKUP checks to see if the order number exists in column A on BANK and if it does exist the second VLOOKUP gets the cost from column 5 which is F.

For more information on Lookups and how to speed them up see
http://www.decisionmodels.com/optspeede.htm

If you have multiple bank transactions per order then it would be worth looking at a Pivot Table solution.

From your description I am sure that it would also be possible to create a fast-processing VBA solution, but it might well be more complex and inflexible.

#### Mr. Walnuts

##### Board Regular
Wow... I'll check that one out Charles, That may be exactly what I'm looking for.
They are always in chronilogical order, but not all the transactions from the Order and Turn-in sheets are on the Bank sheet immediately. They hit somewhat sporadically, as bank transactions tend to do. But regardless, when they DO hit, they are squeezed into their respective row.

We paste the Bank orders to the bank sheet daily so each day a few more are on the bank sheet. I think that will work perfectly.

Shy, THAT will definitely help me out too; I can add that for even more streamlining. However, I'll have to add a macro with a button that will re-enter the formula into the first cell on each sheet and autofill it down at the end of the year. That way it will slim down as the year passes, but the macro will restore it to original when the year rolls over.

Thanks a bunch everyone. I'll post the results.

#### shyrath

##### Active Member
Hey

I just learned from a previous post that you can shorten the code heaps by using

Code:
``````With Range("yourRange")
.Value = .Value
End With``````

Code:
``````For Each c In Range("A1:A100")

If c.Value > 0 Then

c.Copy
c.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End If

Next c``````

#### Mr. Walnuts

##### Board Regular
That doesn't replace the whole code does it?

where would it go in?

#### shyrath

##### Active Member
You could do

Code:
``````For Each c In Range("A1:A100")

If c.Value > 0 Then

c.value = c.value
End if

Next c``````

#### Fazza

##### MrExcel MVP
Hi,

Maybe a non-formula approach might be OK? Such as using SQL UPDATE statements. Then there are never any formulas. Code below assumes Excel 2003 or earlier. For Excel 2007, a different connection string would be required. I have assumed worksheet names like you wrote, headers in the first rows, data under, header names exactly like PO_Number and Cost on 'Order' and 'Turn-In' and AccountCode and Value on 'Bank'. Also I have late bound for the ADO object - to avoid setting references; if you change to early bound it will be faster.

HTH. Regards, Fazza

Code:
``````Sub maybe()

Dim strConn As String, strSQL As String
Dim objConn As Object

Application.ScreenUpdating = False

'this connection string for Excel 2003
strConn = Join\$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
ActiveWorkbook.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

strSQL = Join\$(Array( _
"UPDATE [Order\$]", _
"INNER JOIN [Bank\$] ON 'ECGGT' & [Order\$].PO_Number = [Bank\$].AccountCode", _
"SET [Order\$].Cost = [Bank\$].Value"), vbCr)

With objConn
.Open strConn
.Execute strSQL
.Execute Replace\$(strSQL, "Order", "Turn-In"), strConn
.Close
End With
Set objConn = Nothing
End Sub``````

Replies
1
Views
557
Replies
5
Views
252
Replies
2
Views
351
Replies
6
Views
284
Replies
0
Views
797

### Forum statistics

1,195,589
Messages
6,010,609
Members
441,558
Latest member
lambierules ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back