How Much Vba Code Can A Workbook Hold
May 13, 2008
Does anybody know the memory limitations that Excel (2003) has with regard to the amount of vba code that a workbook can contain?
I have automated one of my spreadsheets to load a bunch of modules containing code (a few hundred thousand lines of code altogether). Before it finishes loading all of the modules I get a "Run-time error '7': Out of memory" error. When I press the Help button on the dialog I get a help page which says:
"More memory was required than is available, or a 64K segment boundary was encountered."
The help page goes on to suggest (among a few other things) that I might have a module or procedure that is too large. After reducing the sizes of individual procedures and modules, I am starting to think that there may be a limit to the absolute number of lines of code that can be stored within a single workbook.
View 9 Replies
ADVERTISEMENT
Jan 27, 2009
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies
View Related
Apr 12, 2008
very simple, I would like to know how the match function could be used example:
125
457
706
match 457 = 1
View 13 Replies
View Related
Apr 11, 2013
I am creating a spreadsheet that has various form controls, etc... in it and I wanted to create a "help" macro. I have a small Question Mark icon and I want the user to be able to click the icon and have little text boxes appear on the excel sheet with instructions.
I was wondering instead of making a toggle event where the user clicks once to turn on and again to turn off, if there was a way that the user could click and hold on the icon to make my text boxes visible....and when the user releases they disappear.
View 4 Replies
View Related
Mar 11, 2007
I was toying w/ the idea of being able to view two items in the same cell. A wholesale cost and a retail cost, the wholesale would be in the cell and the retail price would be in the comment. When mouse is placed over a particular cell, a comment would pop up and show the second view, the retail price.
This to be effective would need to be copied down like a typical formula would be. If not, it would take too long to enter each item into a comment for each cell.
I really doubt this is possible, but if not in Excel, could this be accomplished in VBA?
View 9 Replies
View Related
Apr 3, 2014
If I populate a cell from a cell on another sheet but plan on deleting the data on the other sheet is there any way to hold the result on the new sheet even if I delete where it came from?
View 3 Replies
View Related
Oct 14, 2009
i found a macro that im trying to get working from the site below to generate a number everytime my template is opened.
ive put the code in the thisworkbook module as directed and changed the relevant path but it doesnt do anything i think im missing something but cant see what. this is the code ive placed in thisworkbook module of my template
View 9 Replies
View Related
Jan 17, 2007
I have attached a reduced example spreadsheet.
I have thousands of IDs each of which store a column called IM Limit. The ID is repeated on many rows - for each date that they have come into the system. For each ID over a period of time the IM Limit may change or it may stay the same. If it stays the same I would like all the other rows deleted, if it changes once during the period then I would like only two rows left behind each containing the unique value. So in the end I will have a list of codes where a unique combination of id and the im limit remains behind.
Does anyone know or have a VBA script that would be able to loop through the spreadsheet and delete the unwanted records.
View 3 Replies
View Related
Dec 6, 2013
I have been creating a simple database to hold a record of attendance.
I'm now at the stage where I want to create a worksheet that cannot be modified; but rather is something the user can look at in a glance.
I need forming a SUMIFS formula that will look for monthly periods of the times late, sick, absent etc and add them up to return to one place.
So the dependants will be the month, the employee and the type of record (late, sick, holiday)
I have created a userform that I can use to add in the raw data, so I'm not sure what range I can create, as every time new data is added it finds the next available row.
Here is what I'm using so far: [Code] ........
What the Overview will look like : Overview.PNG
What the raw data looks like : Raw Data to use.PNG
Do I need to introduce a matched up column that has the employee name and date?
View 1 Replies
View Related
Jun 9, 2006
I'm trying to find the max value in range of cells that hold text equivalents?
e.g. cells contain 001, 002, 003, 004.
therefore max value is 004
Dim lMaxVal As Long
lMaxVal = Application.WorksheetFunction.Max(Val(rangeHazRoomNum))
View 3 Replies
View Related
Feb 11, 2009
1. I can't remember it, but I know there is a command so that the view hold and that the selected tabs and cells does not show and the same view remains until the "unhold" is called in the macro. THEN the view is updated. What is this command again? Could not find it...
2. The macro needs to delete tabs at the click of a button (easy part) but for each deletion, a message appear:
Data may exist in the sheet selected for deletion. To permanently delete this data, press Delete. Is there a way to prevent the message from popping out when the macro is executed?
View 2 Replies
View Related
Nov 25, 2008
I have an excel sheet and I want a Msg pop up to show a certain amount from a specific cell. So if the cell shows $100. I want to a do a macro that if I hold control + shift + a I will get a pop up saying "Your amount is (value of the cell).
I can only get it to work if I want the vale from an active cell.
MsgBox "The value in the active cell is " & ActiveCell.Value & "."
View 9 Replies
View Related
Nov 21, 2007
I want to show 8 members of staff's names, with their sales closing percentages. I want then to produce a chart showing their percentages as a column with a line showing 16% going across the chart showing who is above and who is below the target.
Ideally i would also like to change the colour of the columns or background of those below the line to show who is under target.
View 4 Replies
View Related
Aug 1, 2013
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True
[Code].....
View 1 Replies
View Related
Jun 25, 2014
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
View 2 Replies
View Related
Jan 14, 2014
The amazing code I saw was from this thread Auto lock cells after data entry when file saved... where the code was made by Jafaar Tribak.
It basically locks cells that have had data inputted into them when the file is saved (with a nice prompt warning of the used cells being locked.
This is Jafaar's file [URL]
And this is my file where I'm trying to make it work in the "Employee data entry" Sheet:
[URL]
And finally this is the file after I tried to copy the code over : [URL]
Trying to make it so that in the employee data entry sheet locks cells after an employee inputs some data into said cells.
I thought that by setting an "inputrange" on the employee data entry sheet the code would track it down automatically but maybe I'm completely wrong. I cant see anything wrong with it (probably because I"m not that good at vba) but I fear I may have left out something really simple!
I seem to disable my other macros when i try to insert this code in (I have a macro that when you click on a cell a calendar pops up so you can click on a day from the calendar rather than typing out the date)
View 9 Replies
View Related
Sep 12, 2006
How is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.
View 5 Replies
View Related
Apr 18, 2013
I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.
For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).
I am also going to upload a data sheet, and an expected results sheet.
unique list.xlsx
View 1 Replies
View Related
Feb 12, 2009
I wrote an excel program in Excel'03 for a dental office to manage state assistance patients and one of the table columns is the state assistance number. The problem is that the program defaults to a number format when it is entered instead of a text format, which is what I want. The issue is that state assistance numbers are always 8 digits and when it defaults to a number field the program drops the required preceeding zeros.
For example the number 00123456 will sometimes show up as 123456, which is wrong. It's like sometimes it will show up right and other times it gets a butterfly in its brain and deletes those zeros irritating the receptionist here. So, how do I get the table to either stay in text format or set up a number format that keeps the preceeding zeros?
View 3 Replies
View Related
Jan 29, 2008
I have a workbook with four worksheets named
FilteredData
FilteredDataFormat
array
arrayFormat
The two sheets named FilteredDataFormat and arrayFormat exist strictly to preserve headers , and column widths.
So that for convenience sake I can clear my data without specifying a range that excludes the headers.
I simple clear the contents of the 'FilteredData' and 'array' then copy and paste 'arrayFormat' and 'FilteredDataFormat'.
Sort of an unconventional and lazy approach but I see no reason why this will not work.
Here is my original code and what happens is that excel creates another workbook for some reason unknown to me
View 10 Replies
View Related
Feb 19, 2009
This code, supplied through this forum, works perfectly in one workbook but not another. I have created a range called ColourRange, one called ColourIndex but I am getting a '400' error message when I attempt to run it. Can anone explain to me (in very simple terms) why it won't work?
Sub CopyFormatMMT()
'Colour code Owners
Dim r As Range
Dim f As Range
Dim c As Range
Dim j As Range
Set r = Range("ColourRangeMMT")
Set f = Range("ColourIndex")
Range("ColourRangeMMT").Select
Selection.Interior.ColorIndex = xlNone
For Each c In r.Cells
For Each j In f.Cells
If c = j Then
c.Interior.ColorIndex = j.Interior.ColorIndex
End If
Next j
Next c
Range("C9").Select
End Sub
View 2 Replies
View Related
Apr 7, 2009
I want to put the vba code into each "ThisWorkbook" in each workbook that is in a folder. I want the code that is placed in each "ThisWorkbook" to run whenever that workbook is opened.
How would I go about doing that? I have this code so far to open the workbooks that are in the folder.
View 6 Replies
View Related
Apr 28, 2009
I need to error check to see if a worbook is open or not. if it is not open i need it to open it. i supplied the code i am trying but it is not working. I keep all my workbooks in XLSTART.
View 2 Replies
View Related
Jul 7, 2009
I currently have the following code that allows grouping when protected and also puts the user into design mode:
View 5 Replies
View Related
Jul 11, 2009
I know this is the code to close a workbook
View 5 Replies
View Related
Nov 8, 2012
Why can't I get this code to work? The first line seems to work ok but not yet sure as the rest doen't work.
I have tried NEXT_YEAR as a string and as a number but I keep getting an error.
Sub test()
NEXT_YEAR = "2013"
ChDir "J:Maxtor backupFire DeptVacations " & NEXT_YEAR
Workbooks.Open(Filename:= _
"J:Maxtor backupFire DeptVacations " & NEXT_YEAR "VacGrp - 1 - " & NEXT_YEAR & ".xls""", UpdateLinks _
:=3).RunAutoMacros Which:=xlAutoOpen
End sub
Below is the code I am trying to replace and it works fine.
ChDir "J:Maxtor backupFire DeptVacations 2013"
Workbooks.Open(Filename:= _
"J:Maxtor backupFire DeptVacations 2013VacGrp - 1 - 2013.xls", UpdateLinks _
:=3).RunAutoMacros Which:=xlAutoOpen
View 5 Replies
View Related
Sep 29, 2013
I have two workbooks the first named "number" which just contains the number 50000 on sheet 1 cell A1. This "number" workbook is always closed and saved in c:me"number". The second named "sales" has a form control button with code as follows and is always opened and saved in c:me
Sub next invoice()
range ("A1"). Value= range ("A1"). value+1
End sub
When I press the button this code does not work because I am missing the vba code to find the close file.
View 9 Replies
View Related
Dec 31, 2013
I need a code in VBA which I have to use the Hyperlink within the workbook e.g. "Place in This Document.
Assuming I have two Worksheets WS1 = Main, and WS2 = Reference respectively
In WS1=Main, I have a list in Column from E3 to E1000. let say E3=1, E4=2, E5=3, E6=4, E7=5..., so on and so forth.
In WS2=Reference, I have a list in Column from F5 to F1002. let say F5=1, F6=2, F7=3, F8=4, F9=5..., so on and so forth.
What I want is if I click the E3=1, E4=2, E5=3, E6=4, E7=5..., in WS1=Main if will jump to specific cell in WS2=Reference e.g. F5=1, F6=2, F7=3, F8=4, F9=5...,
View 1 Replies
View Related
Feb 24, 2009
I have a code that saves my worksheet once it closed. At the moment it saves the Active worksheet which is Sheet1
The problem i have is i have a few vlookup formulas that reference hidden worksheets Sheet2 & Sheet3
Would it be possible to modify this code so it saves the entire workbook?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Dim bk1 As Workbook
Dim bk As Workbook
Dim myfilename As String
myfilename = "C:files" & Range("B10") & " - " & Range("J10") & ".xls"
Set bk = ActiveWorkbook
Set bk1 = Workbooks.Add(Template:=xlWBATWorksheet)
bk1.Worksheets(1).Name = "Sheet1"
bk.Worksheets("Sheet1").Copy _
bk1.Worksheets("Sheet1")
On Error GoTo 0
bk1.SaveAs Filename:=myfilename, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End Sub
View 9 Replies
View Related
May 9, 2006
Haven't been here in a long time ...
Anyway, I got a HUGE (in terms of code size) wbk that is sort of a management report writer that connects to external data bases and does lots of other stuff.
Due to the way this wbk was designed the actual reports the client requested are in it as well. So now when i need to upgrade the code i hafta use a small proc that kills all the code in the prev version of the wbk but keeps the sheets (ie reports themselves) intact, and then recreates new code modules classes etc from the upgraded version of the wbk.
Although this works for now i really want to improve the code maintenance so i want to implement dll's into this project.
The wbk itself needs to have only basic code to call dll's and some other stuff and that's it. The code itself should come in dll's into a certain folder and wait for the next upgrade . How can i do this?...
View 5 Replies
View Related
