Can Have More Than One For Statement In A Module
Jan 8, 2012
I have 3 modules that all use the same Excel Workbooks, but just perform different actions in each module. It's such a waste of space! I was wondering if there was a way that I could combine all 3 modules into one module.
For example Module 1 is:
Code:
Dim Varbooks
Dim varBook
Dim wb as Excel.Workbook
Varbooks = Array("Fire", "Ice", "Alpha")
For Each varbook in Varbooks
'Run the below code
Next Varbook
Module 2 reads:
Code:
'declare all variables
CurrentPath = ActiveWorkbook.Path
varBook = Array (same varbooks as above)
'Run different code
Next varbook
[code]....
Varsheets (I use varsheets instead of varbooks this time only difference, but they are sheets inside a masterworkbook, as opposed to being workbooks themselves, but exactly the same names)
'Run a totaly different code here
Next varSheet
Is it possible to somehow combine all 3 of those into one module? I have tried, but I keep getting a compile error of "For Control Variable Already in Use"
View 1 Replies
ADVERTISEMENT
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
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
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
Code:
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
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.
Module1
Code:
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?
Module3
Code:
MsgBox Cell
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
[Code]........
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
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
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 Replies
View Related
Oct 1, 2008
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies
View Related
Feb 14, 2012
I am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.
My purpose is trying to look up
Code:
tb_SelJobID.Value
from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if
Code:
TbSelYr.Value
matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.
Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.
Code:
Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False
[code]....
Windows 7 with Excel 2010
View 9 Replies
View Related
May 5, 2014
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code:
Sub testInsert()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code:
Sub testSelect()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim mrs As New ADODB.Recordset
Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this:
9
8
7
6
5
4
3
2
15
14
13
12
11
10
1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
View 5 Replies
View Related
Jan 6, 2009
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
View 9 Replies
View Related
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
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
View Related
Aug 24, 2008
I have been making and so i thought saving a module however every time i close the spreadsheet that module is associated with it deletes. is there any way to prevent this from happening.
Also when trying to edit on PERSONAL.XLSB!Module1 i get a particular error stating
"cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command."
View 12 Replies
View Related
May 25, 2009
I inserted a second module into a project. Now I want to delete it. Only i can't figure out how to do this. I highlite it and right click or click edit from the toolbar but i'm provided no options. I can't drag it to the trash either.
View 2 Replies
View Related
Jun 9, 2009
I have a Form that when the 'SUBMIT' button is clicked, I would like to validate the information and then write it to a 'MASTER SHEET'. However, the validation has gotten lengthy and I have opted to put the validation code in a separate Module to keep my general code a little cleaner.
PROBLEM:
The original code would kick you back to the form if something was wrong via a 'END SUB', but as now the SUB is on a different Module, it isn't working.
Here is the pertinent code on the general code sheet: ....
View 13 Replies
View Related
Dec 1, 2009
I would like to have an email generated when a cell changes to a particular value. I've developed the macro to send the email with a saved attachment.
When I try to OJT-Engineer the text for the module, I can't get it to recognize the macro. It's a syntax issue, it seems.
What I need is for my module to initiate a macro when a cell changes to a value.
This is the code EXACTLY as it appears:
View 9 Replies
View Related
Oct 10, 2007
I have code broken out between two different modules due to size limitations in a single module. I'm not able to figure out how to move to the second module after running the first module. I've tried something like this:
Sub FirstModule()
'Code
'Code
'Code
SecondModule
End Sub
Sub SecondModule()
'Code
'Code
'Code
End Sub
I'm getting an error that says "Compile error: Expected variable or procedure, not module". I understand that it's not allowing me to use the name of a module as a procedure, but I can't find any information on how to call the second module.
View 9 Replies
View Related
May 20, 2008
I defined function ConcatenateIf() in Module 1, that would take a range and combine the cell contents:
Function ConcatenateIf(iRange As Range, iLook As String, iNum As Integer)
For Each cell In iRange
If cell.Value iLook Then
ConcatenateIf = ConcatenateIf & Chr$(10) & cell.Offset(0, iNum).Value
End If
Next cell
End Function
Works beautifully. Then I save, and close. When I re-open, all the cells that reference this function display #NAME? error, like it can't find the ConcatenateIf() function. Does this have something to do with macro security? If so, why would it work at first, and then stop working?
View 9 Replies
View Related
Aug 6, 2008
I've got some code in workbook A that reformats another workbook B. Basically, it moves all the sheets across to A, creates a pivot table and chart, then saves itself as a variant of the original filename B. The last things it does is to delete the one sheet that was originally in A, and then delete the module that the code is running from (using some code from Chip Pearson):
ActiveWorkbook.VBProject.VBComponents.Remove ActiveWorkbook.VBProject.VBComponents("Module1")
The fact that a module can delete itself blows my mind - but there are a couple of things I'm not sure about:
1. Presumably anything that's in my sub after the delete module line won't get executed?
2. If the saved-as workbook (Bvariant) is closed without saving, the module is still there when it is reopened. Is there a way to prevent this without putting new code into Bvariant?
View 9 Replies
View Related
Dec 17, 2008
i am going to ask is for my curiosity only and to be sure that no one will break my password.
If the VBA module is locked for viewing and password protected, it can't be cracked??I asking this, because i made an excel application with macros, and i don't want the user of it to crack my password.
I have to mention that the password is 17 letter+spaces long.
I repeat again....i asked this ONLY for my application safety.
View 9 Replies
View Related
Sep 24, 2009
This module consists of app. 25 sub routines. Every sub routine is a For-Next Loop, with 70 For-Nexts to work through. The loops check certain input cells and if these cells are empty, the row is hidden. I have the reverse also where I can unhide these rows.
When I had about 8 of these loops in this module, it would work quite fast and it took about a second to run one of these subs. Now that I have 25 if I run one of these subs it takes about 20-30 seconds.
Before I break them all down into individual modules just to find out it doesn't work, is there anything else I should be doing or could try to speed these subs up? If breaking them down into more than one module is the solution, how do I do this best?
View 9 Replies
View Related
Oct 15, 2009
I declared a constant like so:
Public Const iFirstRow As Integer = 6
I figured that any time I used this, at least in the same module, its value would be 6, but it was 0.
I changed it to ModuleName.iFirstRow, which seemed to set it straight.
Is this just how it is, or am I doing something wrong that is preventing me from using it without the module name?
View 9 Replies
View Related
Oct 24, 2009
I have been trying many different commands to run a module from a userform, but nothing is working. I'm sure it's possible, right?
Basically, I'm trying to run modules based on a selection made via a listbox.
View 9 Replies
View Related
Feb 15, 2010
I would like to create a class module that it's the same as a connector shape (for example), but with additional custom properties defined by me. For example, I would like it to have a temperature property.
So, for example if I write in a Sub procedure:
Dim Test1 as EnhancedConnector
Set Test1 = New EnhancedConnector
A new connector appears in the activesheet, but this connector must have my custom defined properties available to VBA Code
View 9 Replies
View Related
Apr 20, 2006
I have a module (a) that calls another module (b) to delete module (a). I don't think i can delete module (a) while it's still running. How do i get module (b) to stop module (a) ?
View 6 Replies
View Related
Sep 30, 2006
I am writing a Sub2, where I need to extract the value of another variable, which is calculated in some other Sub1. I declared the first Sub1 (where the variable is being calculated) as Public Sub1,
View 3 Replies
View Related
Mar 30, 2007
I have an existing module that queries a SQL database and populates a worksheet using VBA. I would like to add to this module to include a sum of columns, but as this sheet is always dynamic, i am not sure how to sum this appropriately. for example, I have column B, I would like to add the rows from a certain point in the worksheet, but this is always dynamic, is there a way to accomodate for this so that I am always summing the column in the correct place?
View 2 Replies
View Related
May 7, 2007
Is it possible to do 2 loops in one procedure? I want to copy and paste formula the cells in one column and again in another column so I did two Do Loops. It only ran for the first loop but not on the second, although they have almost the same code (I just edited the column from C to I). Please see below
Dim RowCount As Integer
Sub Format()
Columns("C:C").Select
Selection.Insert Shift:=x1ToRight
Columns("I:I").Select
Selection.Insert Shift:=x1ToRight
Columns("K:L").Select
Selection.Cut
Columns("R:R").Select
ActiveSheet.Paste
Columns("K:L").Select
Selection.Delete Shift:=x1ToLeft
Columns("L:L").Select
Selection.Insert Shift:=x1ToRight
View 5 Replies
View Related
Feb 1, 2008
Is there any way to name a module other than module1, module2, ect...So they are easier to identify in the project window? When I tried to change the name in the properties box, they were no longer called correctly and I get the "expected variable or procedure. Not module error.
View 2 Replies
View Related