Wrox Programmer Forums
|
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 11th, 2017, 01:09 PM
Registered User
 
Join Date: Oct 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Default Procedure too large

I am trying to write a macro to audit account data. I will have headers for all of my fields in each column, and each account data in the rows below, and I am writing my macro to go through each row individually.
Essentially, depending on the type of account marked in a specific column (B in my case), I am writing a set of "If statement" type validations, and userform pop up boxes, and i believe i have a function for one of the validations, for the remainder of the columns (C through CK).

I am having it simply mark the cell with a color if it does not pass my validation rules. I will have a different set of validation rules for each account type, which there are about 12 different types. I was trying to use the select case for the different account types, and putting the field validation statements within the case scenarios, but with 12 account types and about 84 simple/complex combined validation rules for each account type, my macro is having an error saying that the procedure is too large.

Can anyone help me understand a better way to write this for organization and function? I am thinking of making each account type into it's own sub, but I am not sure how to write subs within subs, or if it would be better or not to make each sub for each acct type have it's own module? I am concerned with each of my columns being called out as a variable, and not having to define them repeatedly as well.

Any input is appreciated!!!
 
Old October 11th, 2017, 02:51 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Sub testall()
For each myCell in myRow.columns
If myCell(1,1)= 5 And myCell(1,5) = 15
Validation1 myRow
my cell.interiorIndex.color = 16
Else If myCell(1,1)= 5 And myCell(1,4) = 3
ValidationX myRow
End If
Next
End Sub

Validate1(myCell())
ElseIf
Inter
Next
End Sub

Sub Validation1(myRow as range)
If myCell(1,1)= 5 And myCell(1,5) = 15 And myCell(1,20)= "k" then
my cell.interiorIndex.color = 16
End If
Next
End Sub

Sub Validation2(myRow as range)
If myCell(1,1)= 5 And myCell(1,6) = 15 And myCell(1,80)= "d" then
my cell.interiorIndex.color = 16
End If
Next
End Sub

Sub ValidationX(myRow as range)
If myCell(1,1)= 5 And myCell(1,5) = 15 And myCell(1,84)= "7" then
my cell.interiorIndex.color = 16
End If
Next
End Sub
__________________
Nostalgia 4 Infinity
 
Old October 16th, 2017, 02:41 PM
Registered User
 
Join Date: Oct 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Default

I am following that code for the most part...
I think I am still going to run into the issue of my variables not being defined in all Subs/Modules no matter how I do it. Can you define things outside of a sub? Or can anyone explain better to me how/if I should try to use Public or Global definitions?
 
Old October 16th, 2017, 03:20 PM
Registered User
 
Join Date: Oct 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
Default

I think I figured it out! I am so happy!
I used different subs in the same module like you had outlined, but I moved my Dim statements for my variables to the beginning after Option Explicit, but before my first Sub. This makes it so that all of my subs within the module will use the same variables! And the different subs break it up into small enough pieces for VBA to process!!!! I think this will also clean up how quickly my macro will run as well. *fingers crossed*
Thank you for your feedback!
 
Old October 16th, 2017, 06:25 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Remember the only actual variable is the current cell you are testing.

The function takes the presented cell and does its classification job, release resources after.

Maybe you should send me the spreadsheet to see what can be done?
__________________
Nostalgia 4 Infinity
 
Old October 18th, 2017, 06:14 AM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Happy you are sorted.

About variables in the module, it is a good practice to have everything under one main Sub, kind of like a wrapper, then call your worker functions within this wrapper.
Your variables will now be local within this wrapper and global to the worker functios, not global to the module.
__________________
Nostalgia 4 Infinity





Similar Threads
Thread Thread Starter Forum Replies Last Post
Large Label? jhughes36 BOOK: Stephens' Visual Basic Programming 24-Hour Trainer 1 November 30th, 2011 02:12 PM
Procedure too large - Urgent devireddybalu Beginning VB 6 26 April 4th, 2008 02:06 AM
Large Project ken killeen All Other Wrox Books 0 October 27th, 2004 07:43 AM
Too large query - what to do? janise MySQL 14 June 14th, 2004 11:50 AM





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