How Long / Big Should A Module Be

Jun 17, 2014

In my Personal Macro book, I have 2 modules, one for Functions and one for "macros".

Is there a point where it's more efficient to make a new module (or is the opposite true and it's more efficient if they are all in one)?

Does having a plethora of sub routines in a module slow down excel even though the routines are short? or would having them in different modules slow it down more?

View 4 Replies


Keeping Track Of Which Macro Is In Which Module Since You Can't Rename The Module?

Mar 27, 2009

After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?

View 2 Replies View Related

Copy Codes Of Module 1 And Transfer To Module 2?

Jan 24, 2013

Let's say i have 2 Modules on my VBA forms, is it possible to Copy all the Codes in Module 1 and Paste it to Module 2 by using a Command Button?

View 1 Replies View Related

Making Msgbox In One Module With (Yes) Linking To Different Module

Jan 19, 2012

i got a question how i can make a msgbox in one module with 'Yes' linking to a different module.

such as: this is located in module2

If MsgBox("Do you want to activate module1?" & vbCr & _
"" _
, vbYesNo, "Choose") = vbNo Then Exit Sub

i want that vbYesNo different

No as in Exit Sub

yes as in activate Module1

View 5 Replies View Related

Calling Module In Module With Variables

Oct 25, 2011

What i have at the moment is a module that contains code where i call a different module that i use as a procedure.


If Sheet1.Range("C4").Value < Sheet1.Range("A2").Value Then
If Sheet1.Range("K4") = "" Then
MsgBox "Please check 06:00 tasks not done yet!"
Cell = "Range(" & Chr(34) & "F4" & Chr(34) & ")"
If Sheet1.Range("C4") + 0.042 < Sheet1.Range("A2") Then
Run "EmailProSheet"
End If
End If
End If

EmailProSheet is what i call but now i want to use the variable "Cell" in the procedure as well?


MsgBox Cell
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next


As it is now everything is working fine but does not return a value in "Cell" if the procedure is called. Is there another way?

View 5 Replies View Related

Run-time Error '50289' :: Project Protection / Module Protection / Module Visibility

Aug 18, 2004

I recieve an error :

Run-time error '50289':
Can't perform operation since the project is protected.

When i try and run my code.

The code does add parts to modules and workbook events, but I need the code to be protected.

View 9 Replies View Related

Long IF(AND Statements)...

Apr 8, 2009

I've attached the workbook to make this easier. If you look at the file master.xls you will see 2 tabs. On the Tool tab I've highlighted some blue cells and some rose cells. If you look at the blue cell G68 there is a long formula that reads up to 3 cells from the Specs tab and then plugs in the correct data from the blue cells on the right side of the Tools tab page. I need the rose cell F69 to do the same thing only with the data from the rose cells to the right. When I try and duplicate the fromula from G68 into F69 I get errors. I hope I made this clear enough. The formula in G68 is long, is there a way to shorten it? Also sometimes in this workbook when I try to type a formula in a cell I get the text I typed instead of the formula. For example I might enter in A1, =B2. Instead of getting the data from B2, it reads =B2. I've looked at how the cell is formatted but can't get it to work.

View 3 Replies View Related

Formula Too Long To Sum?

Jul 3, 2013

I am using the sum function to sum the following cells and the formula is too long. How can I make this work?

'Raw Data'!B3,'Raw Data'!D3,'Raw Data'!F3,'Raw Data'!H3,'Raw Data'!J3,'Raw Data'!L3,'Raw Data'!N3,'Raw Data'!P3,'Raw Data'!R3,'Raw Data'!T3,'Raw Data'!V3,'Raw Data'!X3,'Raw Data'!Z3,'Raw Data'!AB3,'Raw Data'!AD3,'Raw Data'!AF3,'Raw Data'!AH3, 'Raw Data'!AJ3, 'Raw Data'!AL3, 'Raw Data'!AN3, 'Raw Data'!AP3, 'Raw Data'!AR3, 'Raw Data'!AT3, 'Raw Data'!AV3, 'Raw Data'!AX3, 'Raw Data'!AZ3, 'Raw Data'!BB3, 'Raw Data'!BD3, 'Raw Data'!BF3, 'Raw Data'!BH3, 'Raw Data'!BJ3, 'Raw Data'!BL3, 'Raw Data'!BN3, 'Raw Data'!BP3, 'Raw Data'!BR3, 'Raw Data'!BT3, 'Raw Data'!BV3, 'Raw Data'!BX3, 'Raw Data'!BZ3, 'Raw Data'!CB3, 'Raw Data'!CD3, 'Raw Data'!CF3, 'Raw Data'!CH3, 'Raw Data'!CJ3, 'Raw Data'!CL3, 'Raw Data'!CN3, 'Raw Data'!CP3, 'Raw Data'!CR3, 'Raw Data'!CT3, 'Raw Data'!CV3, 'Raw Data'!CX3, 'Raw Data'!CZ3,

View 7 Replies View Related

Reset Value Of Long?

Jun 2, 2009

I know that a variable of string type is equal to "" before it's assigned a value. So I reset the value of strings to empty (i.e. "") when I want them clear of a value. I also know that variables of Variant Type are equal to NULL when they are unassigned so I reset them to NULL when I want to clear them of a value.

But what about a Long? When you declare a variable of Long Type or Int Type or Double Type, what is it's inital value when empty and unassigned. I assume it's not 0 since 0 is an actual number.

I want to reset my lng variable and clear it of a value. So I'm wondering what should I set to... How can i do this?

View 3 Replies View Related

Command Bar Too Long

Dec 20, 2006

1. if I have a custom command Bar that is very long and I want it displayed in two rows how do I code it to start a new row at a certain point (button)?
- can I control the length of it?

2. How do I make the Formatting toolbar inactive on opening a particular workbook?

View 9 Replies View Related

UDF, For A Long If Stmt

Aug 10, 2008

I need to build a udf that simplifies a 10 nested if statement . i could achieve my task by using a if and statement, but it would exceed the 7 nested limit.

I have company names in column A, industry code in column b and four varibles in column C,D,E,F . The g column would be a weighted average of columns C,D,E,F. The weighted average score would have the logic of:

If the industry code is equal to "TE", then (C2*.7)+(D2*.1)+(E2*0)+(E2*0)
if the industry code is equal to "EN", then (C2*.3)+(D2*.3)+(E2*.3)+(E2*.1)
if the industry code is equal to "SF", then (C2*.4)+(D2*.6)+(E2*0)+(E2*0)
if the industry code is equal to "H", then (C2*.1)+(D2*.1)+(E2*.1)+(E2*.7)
if the industry code is equal to "MM", then (C2*.1)+(D2*.2)+(E2*.3)+(E2*.4)
if the industry code is equal to "EE", then (C2*0)+(D2*.0)+(E2*.8)+(E2*.1)
if the industry code is equal to "ENG", then (C2*0)+(D2*0)+(E2*.5)+(E2*.5)
if the industry code is equal to "ETT", then (C2*0)+(D2*.2)+(E2*.3)+(E2*.5)
if the industry code is equal to "HH", then (C2*0)+(D2*.9)+(E2*.1)+(E2*0)
if the industry code is equal to "HHW", then (C2*.7)+(D2*.3)+(E2*0)+(E2*0)

View 9 Replies View Related

Long Formula Div/0

Feb 8, 2010

im not really sure what div/0's are na dhow to counter them but i understand from the error that im dividing by something that is not valid yet.

below is my formula, among others like this. i need some assistance in getting the ISERROR code in as i am struggling; ....

View 9 Replies View Related

If Statement To Long - Keep Getting Error?

Mar 4, 2014

I'm working on a spreadsheet that i have a long if statement and it keeps till me i have a error. I stated reading and come to find out you can only have 7 statement.


View 14 Replies View Related

Compress The Formula Is Too Long

Nov 13, 2008

how can in compress the following formula

=IF(B3="Monthly",IF(B25="Windows 2003 Std",15,IF(B25="Windows 2003 Ent",30,IF(B25="Windows 2008 Data Center ",40,IF(B25="RHEL 5","30",IF(B25="Windows 2008 Web","15",0))))))+(IF(B3="Quarterly",IF(B25="Windows 2003 Std",40,IF(B25="Windows 2003 Ent",75,IF(B25="RHEL 5","89",IF(B25="Windows 2008 Data Center ",120,IF(B25="Windows 2008 Web","45",0))))))+(IF(B3="Semi-Annually",IF(B25="Windows 2003 Std",75,IF(B25="Windows 2003 Ent",140,IF(B25="Windows 2008 Data Center ",200,IF(B25="RHEL 5","149",IF(B25="Windows 2008 Web","90",0)))))))+(IF(B3="Annually",IF(B25="Windows 2003 Std",150,IF(B25="Windows 2003 Ent",300,IF(B25="RHEL 5","299",IF(B25="Windows 2008 Data Center ",400,IF(B25="Windows 2008 Web","180",0))))))))

View 5 Replies View Related

Countif - Formula Too Long

Oct 31, 2009

I need to add more arguments to a countif formula, but I have maxed out the 1024 character limit.

Is there another way of writing this formula or is there a way I can add more arguments to this formula.

Formula is:


I have a list of the arguments, can I do something like:


(with the list of arguments in columns B2 to B50)

View 13 Replies View Related

Avoid Long IF Statement.

Jan 5, 2010

I've attached an example of what I am trying to achieve and an explanation as well. I'm trying to avoid the use of a macro as I don't understand them and I will need to modify the sheet later on which could cause problems. However, if a macro is the only solution, then I will have to make due with one.

View 5 Replies View Related

Long List Won't Sort?

Jul 22, 2013

I am trying to sort a fairly long list of tasks into a custom calendar order. I have created the custom list as Sept, Oct, Nov, Dec, Jan etc through to Aug, with one additional heading of "Recurring". The list won't seem to sort by the column where these headings occur. See attachment.

View 7 Replies View Related

UserForm Won't Process Because VBA Is Too Long

May 9, 2014

I have a UserForm where the user can enter figures for 143 different items which they select via a combobox drop down.

When I tested it, I hit the submit button but it says the procedure is too large!

Here is the code which is repeated 143 times but with a different row number and range each time (in red):

[Code] .......

So the next bit of code would be exactly the same except the range would be C5 and the row would be 18.

The range is always going to increase nice and easily: C5 up to C146.

The row number is a bit hit and miss.

Is there a way of significantly decreasing the size of this or will I have the break the code down into different UserForms?

View 14 Replies View Related

How Long Do Variables Keep Their Values In VBA?

Jan 27, 2009

I noticed that sometimes if I stop a macro midway (completely stop, not just pause), and then re-run it from the beginning, my variables will still hold the value they were last set to right before i stopped the macro. But sometimes, this doesn't happen - the variables reset.

I'm wondering what defines how long the variable's value are held for? I'm considering writing a macro that can hold variables so that even after the macro is finished, the user can re-run and the variables will still be available. This is useful mostly for text boxes in userforms that will probably have the same values the next time the user runs, but the user can still change them if he wants.

View 3 Replies View Related

Integer Vs Long Vs Decimal

Feb 23, 2009

Take a look at the sample sheet. I get the information as an import that looks like the info in column A.

I want it to look like the data in Column B. If I try to format as a number it always turns the last number to a 0. I also dont want the 0's on front of the digits either.

I dont really care how its done. But I would like a routine to turn column A into and like the example in Column B. No zeros on the front and maintaining all numbers.

View 9 Replies View Related

Long Expression For For Sum Range

Jul 22, 2009

what the long expression for the range being summed below.

Reason: I need to exclude some cell values.

View 11 Replies View Related

Long Select Case With If/Then

Sep 29, 2009

If there is a different way, like to select the value in the selected range and cross reference it against a list. i'm not sure... trying to learn and be efficient.

View 3 Replies View Related

Long Calculating Time

Aug 26, 2008

I managed to put together an array formula to calculate the last date that a rep made a sale. It checks two other tabs in the workbook to find the date, and if none is found, it leaves the cell empty.

{=IF(MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A))=0,"",MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A)))}

But it takes sometimes up to 4-5 minutes to make the calculations. Is there possibly a way to simplify it so that it calculates faster, with the same results?

View 3 Replies View Related

Function VAL To Return Long Value?

Jun 30, 2013

The function "VAL" will return the value of a numerical string as a double. Is it possible to get a similar function that will return the value as a long.

View 5 Replies View Related

Print A Long Userform

Jul 4, 2007

with printing a long userform? Unfortunately, the me.printform does not print out the whole form. It gets cut off because my form is too long and slightly too wide. I also tried to do a print screen, but unfortunately, because my form is too long, it will only do a snapshot of a portion of the form. In my case, on the bottom as that is where I have placed my command button.

Does anyone know how to bring the whole form out to excel or word? I don't mind doing that as long as I can print out the whole form?

View 9 Replies View Related

For Loop Taking Too Long

Feb 26, 2009

I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.

The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:

Sub Prep2()
'Delete all blank data rows
Dim Rows As Double
Dim Rownum As Double
Application.ScreenUpdating = False
Rows = Selection.SpecialCells(xlLastCell).Row

For Rownum = 2 To Rows
If Cells(Rownum, 11) "" Then GoTo NxtRownum Else

Cells(Rownum, 11).EntireRow.Delete shift:=xlUp

Rows = Rows - 1
Next Rownum
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Formula Too Long For One Cell

May 23, 2006

Got a formula that goes like this: =IF((L1=1),"Product 1",IF((L2=2),"Product 2",IF((L2=3),"Product 3",IF((L2=4),"Product 4".... and so on for a further 100 or so products. Obviously, this formula is too long for one cell.

View 2 Replies View Related

Code Too Long For One VBA Line

Aug 21, 2006

I have a large formula written in my VBA code (a sumproduct with numerous variables) which exceeds the line length in VBA and therefore a proportion of the code is shown on the next line.

However, the VBA treats this second line as an error (since it sees it as a new line of code which on its own doesnt work) What do I need to do so that it treats the two lines as a single line of code?

View 7 Replies View Related

Add/Sum Each Digit In Long Number

Oct 17, 2006

I have a cell with a number which is a result of several previous sums. It's a number with 8 to 11 digits. For example: 878567663.

I need to add each digit of the number, and then add again the result until I end up with a single digit. In the above example it'd be (8+7+8+5+6+7+6+6+3=56), then (5+6=11), then (1+1=2) and put the end result (2) in a cell

My main doubt is that I don't know how to use a simple "do while" type structure in Excel to achieve the above.


1.) How do I create a formula to do that?. I have seen solutions in this board that let me add each digit but that's it... I need to "reduce" it until only one digit is left...

2.) What about if the lenght of the number is not always the same (fe. in one cell is 8, in another is 11, the next one is again 8...)?

View 6 Replies View Related

Run Module Through Userform

Dec 21, 2012

I have created an Excel Add-in to create the Text file. I want to create the text file through select sheet from userform.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved