Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 29th, 2010, 12:56 PM
Registered User
 
Join Date: Nov 2009
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Red face Excel too slow due to formula

Hi,

I have a workbook template that contains contains 5 spreadsheets, each spreadsheet has formulas down to row 10,000 and across 11 columns, it also

first problem My workbook is toooo slow! It has no values and it is set to manual calculation. What can I do to increase the speed.



second problem I want to add macros to help me with the mundane repeative steps of copy from my data sources into my workbook. should I erase the formulas and create macros to paste the formlas to each row that contains data values? or what options do I have.


I am not even sure if I am asking the question right. but I need help please.
 
Old October 29th, 2010, 09:46 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default more info

where are you copying your data from?

second, are those formulas there just in case you have 10,000 rows of data? that's a lot of formulas. most of the excel funtions are supported in VBA, and the ones that aren't have corresponding functions in VBA.

There's several different ways to skin this cat, and I'm gonna guess you have no experience with programming in VBA.

Since i have no idea what you're doing as far as data calculations, here's a sample spreadsheet:

Item Date ordered Qty Price Anticpated Item total
delivery date

Extenze 6/9/69 5 10


The anticipated delivery date and the item total will be calculated fields.


So here's the code:

Code:
Option Explicit

Sub GoDownRows()
Dim Rg As Range 'Declare an object variable that is a range object
Dim lRowOffset As Long 'this is a variable to keep track of which row we're in.

Set Rg = Sheets("Sheet1").Range("A1") 'Change "Sheet1" to whatever the name of your sheet is

With Rg

'____________________________________________________________________________________________________________________________________
    'Use the offset property of the Range object to refer to the cell you want to put a calculated result in. The Offset property is
        'zero-based, so since we've set our range to be cell "A1", then ".Offset(0, 0)" refers to "A1". The first zero is the
        'row offset, the second is the column offset, so ".Offset(1,0)" refers to cell "A2", ".Offset(0,1)" refers to cell "B1", and
        'so on.
    'Because we are within a "With/End With" block, precede the offset property with a period.
    'I'm going to assume you paste your data into cell "A2" of your worksheet, so as we go down the columns, we'll check the "A"
        'column to see if we should enter formulas for this row.
'___________________________________________________________________________________________________________________________________


    lRowOffset = 1 'Set the row number to 2, the one directly under your column headers. Keep in mind that because the offset is
        'zero-based, it's gonna be one less the the actual row we're looking at.
    
    Do Until IsEmpty(.Offset(lRowOffset, 0)) 'if there's data pasted into this row in the "A" column, do it, otherwise exit this loop.
        .Offset(lRowOffset, 4) = .Offset(lRowOffset, 1) + 6 'Add six days to the order date to come up with the delivery date
        
        'multiply the quantity ordered by the price and enter it into column "F"
        .Offset(lRowOffset, 5) = .Offset(lRowOffset, 2) * .Offset(lRowOffset, 3)
        
        lRowOffset = lRowOffset + 1 'Go to the next row
        
    Loop 'go back to the "Do Until" line
End With

Set Rg = Nothing 'Free up your computer's memory
End Sub
Assuming you know nothing of coding, there's a lot i've left out. Don't want to write a book, lol. If you need to use an Excel function, the code would look something like this:

Code:
.Offset(lRowOffset, 6) = WorksheetFunction.VLookup("Extenze", Range("Prices"), 4, False) 'Where "Prices" is a named range that has Item names and prices
Hope that helps, if not, post again.
 
Old October 29th, 2010, 10:19 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default Just re-read your post

To increase the speed....
Get rid of all those formulas!! Replace them with VBA, or 'macros'. It's gonna get really complicated for you really fast if you don't know VBA, but the fastest most efficient way is to assign the pasted data to an array, compute formulas, then post them back to the spreadsheet. My way, working on each row of data, might give you adequate speed. If not, you're either going to have to take the plunge into learning the VBA programming language, find a pro in VBA who can set it up for you, or find someone who can set up a custom application for you.
You know the particulars of your situation. VBA (macros), is a difficult programming language to work with becuase of it's poor syntax checking. Miss a period, a quotation mark, a parenthesis, it will give you a retarded message that gives you no clue what you did wrong. Having said that, if you have an aptitude for programming, an extreme aptitude for attention to detail, a love of making a ton of work done at the press of a button, no desire to learn or pay for a programming language outside of Microsoft Office, then VBA is for you!!! Lol. VB.Net express is another programming language, it's free, but i'm not sure it interfaces with Microsoft Office, I don't know much of programming outside of VBA myself. I know (or believe I know) VB.net 2008 didn't interface with Office, maybe 2010 does, it shows up in my references anyway.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Very Slow Excel Files vikashtiwari Excel VBA 9 April 22nd, 2008 01:56 PM
excel freezes due to Application.DisplayAlerts paul20091968 Excel VBA 1 July 22nd, 2006 02:41 AM
XSLT to Excel transfer too slow JMnet ASP.NET 2.0 Basics 0 June 28th, 2006 02:40 PM
Excel Formula zachtom Excel VBA 4 May 6th, 2006 08:02 PM
VB-Excel Slow Sometimes Brian149 Excel VBA 4 January 7th, 2005 03:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.