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

[Mr. Walnuts]

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.

 

[Mr. Walnuts]

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]

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 ).

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

 

[ColinKJ]

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

ColinKJ

 

[FastExcel]

ADVERTISEMENT

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]

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]

ADVERTISEMENT

Hey

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

With Range("yourRange")  
    .Value = .Value  
End With

instead of

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]

That doesn't replace the whole code does it?

where would it go in?

 

[shyrath]

You could do

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

If c.Value > 0 Then

c.value = c.value
End if

Next c

 

[Fazza]

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

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)
 
  Set objConn = CreateObject("ADODB.Connection") 
  With objConn
    .Open strConn
    .Execute strSQL
    .Execute Replace$(strSQL, "Order", "Turn-In"), strConn
    .Close
  End With
  Set objConn = Nothing
End Sub