I have a file with a number of variables and wanted to know if there is any way to print out the list, from say the locals window or somewhere else. Empty values are fine, I'm just trying to compile a listing for our external documentation.
What i have at the moment is a module that contains code where i call a different module that i use as a procedure.
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?
Code: 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?
Is tere a way to reset all public variables in a module. I have a number of sub routines withing the same module and need to carry a variable from one to another so I have decalared tham as public variables.
This works fine except for an instance when I expect the variable to be empty (because nothing has yet been assigned to it) when in fact it contains the value that was assigned to last time another sub was run. So I suppose what I need is a way to reset all the variables in the module when a particular sub ends
I started plugging away at VB a little over a month a go and i feel i've learnt alot but can see just how limited my knowledge is. how to define all my variables in one module that then will feed into the code on other modules and forms in the workbook where required. For example, my spreadsheet has approx 30 different variables that are called upon by a variety of subs. How do i define all these in one place and then just call on the relevant ones when required rather then having to redefine them for every new sub.
I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).
And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.
I would like to be able to compare the "standards" in Column A with the Assessment status in Column B and the Assessment period in Column C in order to generate a compact list in another workbook (ideally) or tab (if not). A list might typically contain all the standards that have been M (mastered) during a given Assessment period. I realize that filters would achieve this to a large extent but I was hoping to automatically populate another worksheet or tab.
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?
I have 8 items in a drop down list that all have the same variables that i've listed on a separate worksheet. I want to select one item from the drop list and then have only its variables appear. And then when I change the item from the drop down list all of the variables will update for that item - and have them displayed always in the same cell.
how to store the variables selected in a list box for future use in the macro?
I've got code as follows:
This is in my main module
'These set up the global variables Public SelectedRegion As Variant 'I've also used string Public NewTabName As Variant 'I've also used string
'This links the list box to the values in a hidden tab named "Regions" and shows the form WSForm.RgList.RowSource = "Regions!A1:A10" WSForm.Show This is in my coding for the form itself (list box & buttons)
'Code for my "Cancel" button Private Sub cmdCancel_Click() Unload Me End Sub
I got three columns. The left one is the day number. The middle column is for indicator variables 1 or 0. This is suppose to be chosen by the solver function. I want the third column to show the DIFFERENCE between the "chosen" days. This can be better explained through an example:.......
(extra information: the indicator values are chosen by solver based on several criteria not stated here, but this particular part of the sheet is suppose to refrain the chosen days to have differences between them larger or smaller than a given value.
I am in the process of re-building a major project due to bugs. In the process, I plan to re-organize my sub-routine grouping within modules. In the source file, I have 18 standard modules, with 10-30 routines in each one.
What I would like is a printout of each routine name and which module it resides in, so I can check off each one as I copy it and paste it into the new file. I see that I can get a psuedo tree-view of my project in th eObject Browser, but can't figure out how to print that.
Can a Function give two or more output variables. e.g.
Sub a() x = 5 result = Y(x) End Sub
Function Y (x As Integer) As Integer Dim B B = ... * x Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that? I need this because function works with large matrix and I want to extract some values appeared in between.
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset) Dim fd As FileDialog Dim xl As New Excel.Application Dim wb As Excel.Workbook Dim ws As Worksheet Dim iFilePicked As Integer Dim strFilePath As String fd.Filters.clear fd.Filters.Add "Excel files", "*.xls" fd.ButtonName = "Select" iFilePicked = fd.Show If iFilePicked = -1 Then strFilePath = fd.SelectedItems(1) Else ..................
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.
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.
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: ....
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
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
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"
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.
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?
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):
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.
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?