A macro to count the number of macros in a woorkbook

Visual Basic for Applications (VBA)

‹ Microsoft ISV
- Archive
  - Dotnet
  - Visual Studio
  - Visual C#
  - Smart Device
  - Game
  - VB
  - VS Express
  - Windows Live
  - Office
  - SharePoint
  - Windows Forms
  - IE
  - SQL Server
  - ISV
  - VS 2008
  - VFP
  - VC++
  - Windows Vista
  - Architecture

- Previous
  - 1
    - Using VBA macro to locate next free column on worksheet I need a macro that locates the next free column (i.e. has no data in cells) and then places the cursor in a cell on a designated row. Would like search to start from column B and to place the cursor on row 4 on the free column. <> My VBA code thus far: Function NewColNumber(Range1 As Range) As Integer Dim j As Integer For j = 1 To Range1.Columns.Count If Range1.Columns(j) = "" Then NewColNumber = Range1.Columns(j).Column Exit Function End If Next End Function Sub SaveCOLComments() On Error GoTo Err_Part Dim Range1 As Range Dim intNewColNumber As Integer ' Go to Sheet1 and COPY field range content the go to Sheet2 Sheets("Sheet1").Select Range("F2:F6").Select Selection.Copy Sheets("Sheet2").Select ' Obtain the next free column using range set in range1 Set Range1 = Worksheets("Sheet2").Range("c4:z4") ' Obtain the next available column number and place cursor in row 4 intNewColNumber = NewColNumber(Range1) Worksheets("Sheet2").Cells(intNewColNumber, 3).Select ' PASTE the field range content obtained from Sheet1 into row 4 of column ActiveSheet.Paste MsgBox ("The field content has been copied."), vbOKOnly, " Test Macro", a, a Exit_Point: Exit Sub Err_Part: MsgBox Error$() Resume Exit_Point End Sub Tag: A macro to count the number of macros in a woorkbook
  - 2
    - Setting a Bookmark to insert a Page Break in MS Word Hi! I know how to use setBookmarkString, but I need to insert a Page Break with a Bookmark. What Function do I use thanks in advance Pieka Tag: A macro to count the number of macros in a woorkbook
  - 3
    - Macro - Replacing Contents of Cells When dealing with the a spreadsheet, is there a macro code that would replace the contents of a cell with a "0" for all of the cells in each column in the Questions worksheet that do not match the criteria from a specific cell in the Solutions worksheet I received wonderful help for a similar question on the same worksheet with the following original question: I am trying to write a macro that will use one sheet, Solutions (Sheet 1), with a specific cell range, B2, and find and replace the reference from cell B2 in a specific column, G, in Sheet 2, Questions, and replace with the value 1. Right now I am using B2 from Solutions Sheet 1 that contains the cell named Financial (Revenue) reporting, and using that as a specific search criteria in column G, but is there a way to use the reference B2 and find and replace cells in column G that are also labeled Financial (Revenue) reporting without using the actual name "Financial (Revenue) reporting" but if the cell in column G, Sheet 2 Questions, matches B2, Sheet 1 Solutions, then replace that specific value in column G, Sheet 2 Questions, with a value of "1" The following is the outcome from that original question that I am building my spreadsheet from: Code Snippet Sub Macro1() ' ' Macro1 Macro ' Macro recorded 5/8/2007 by Rachel ' ' Sheets("Questions").Range("G:G").Replace What:=Sheets("Solutions").Range("B2"), Replacement:="1", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False End Sub Thank you! Tag: A macro to count the number of macros in a woorkbook
  - 4
    - Getting data from other workbooks I have a master Excel file named (master.xls) and other slave files called ( slave1.xls, slave2.xls, slave3.xls ....salve5.xls and so on. ) master's structure is exactly the same as other of other salves where they all contain a number in cell A1 master will first select the slaves. And master wants to calculate the sum of A1(s) in slaves and have the result put in B1 of master.xls getting A1(s) from slaves may open and close slaves, however I want to have the open and close done in background. How can I do it Tag: A macro to count the number of macros in a woorkbook
  - 5
    - Run Time Error 9 when Calling a Userform Hi folks, I am getting a run time error # 9 when I run a macro that calls a Userform or when I try to run code in a Userform module. The code performs beautifully on my computer, but it did not work on a coworker's computer. It ended up working on 3 out of the 5 computers I have tried it on. I have tried changing security settings to low, and a bunch of other stuff, but I cannot get the code to run on the computers that get the run time error on them when I try running the code on them. I get the run time error when I try to load or show any userform in the workbook and I get it if I try to run code that is in the userform module. However, if I paste the code into a regular module and run it, the code runs fine. Does anyone know what could be causing this I don't my code is causing the problem since it runs on some machines, I am guessing there is a setting that is preventing Excel from calling Userforms. Any ideas Thanks, Joe Tag: A macro to count the number of macros in a woorkbook
  - 6
    - Excel CheckBox - Enabled "False" but I don't want it grayed out Is there a way with VBA to have a CheckBox with the enabled property set to false but defeat the "grayed out" quality. I have checkboxes that take their value from other objects and I do not want the users to be able to select them, but I don't want them to look grayed out either. Tag: A macro to count the number of macros in a woorkbook
  - 7
    - iexplore.exe - Application Error I get the following error message when I close my internet explorer. The instruction at "0x62304320" referenced memory at "0x62304320", The memory could not be "read". Click on OK to terminate that program. Any ideas what is causing this Tag: A macro to count the number of macros in a woorkbook
  - 8
    - Parse AIM Buddy List Export file and Import to LCS 2005 Client I'm working on a migration project from the wide use of AOL AIM to Office Communicator 2005 and Live Communication Server 2005. I can create a text file export like the following: Config { version 1 } User { screenName thereScreenName } Buddy { list { "GroupA" { "buddy1" "buddy2" } "GroupB" { "buddy3" "buddy4" } } } There could be any number of groups. I will provide variable inputs for the input file and output file locations. I need to be able to make a new file like: buddy1 sip:buddy1@aol.com buddy2 sip:buddy2@aol.com buddy3 sip:buddy3@aol.com buddy4 sip:buddy4@aol.com I am completely a newbie in C#, but I was requested to use it. Any assistance would be great. I will request that the resulting tool be open source and publicly available, so please keep that in mind. Tag: A macro to count the number of macros in a woorkbook
  - 9
    - How to make a macro to copy some parts of a table to an email that it will create? Hi, I'm quite new to VBA but had some experience coding for Excel. I now require a macro to help me solve a problem in Word and email. I hope someone can help me here. Thanks in advance! The issue is that I need to copy some parts of a table in Word to an email that it will create too. For eg. There are two columns to the table, with the 1st row, column 1 being "Name", then 2nd row, column 1 being "Role", the 3rd row, column 1 being "Unit" and the 4th row, column 1 being "Title". On column 2 of the 4 rows are spaces for people to type in. When one presses a macro button, the macro should create an email with the To field filled out, attaching this Word document to it, with some words on the email. In addition, I would like the macro to copy for example, the whole of row 1, 2 and 4 and then paste it in the email. That will include all the columns of row 1,2 and 4 but not row 3. I have the part of creating the email and attaching the document worked out. Now I lack this copying of data. Once again, I thank you guys for the help! Tag: A macro to count the number of macros in a woorkbook
  - 10
    - Automatically send attachment in email with a submit command button in word I have a huge problem if anyone out there is able to help; I have tried to create a submit command button to email off a document as soon as the user hits submit; however i am new to this and my macro does not seem to be working. Does anyone know where i have gone wrong and if possible can you please help rectify the problem. Thank you Sub CommandButton1_Click() Dim Email As Object Dim Attachment As String Set Email = CreateObject("Outlook.Application") 'Assign Outlook application to variable With Email.CreateItem(olMailItem) .to = "<>" 'email address .Subject = "Incident Report" 'Something for the subject Field .body = ActiveDocument .Display = Attachment Options.SendMailAttach = True End With Tag: A macro to count the number of macros in a woorkbook
  - 11
    - Excel VBA Hi everbody! I am using Excel and I wrote a code using VBA. I have subroutine which open and read text file. I call this subroutine in the master program. The object of the program is to open read a text file(numeric) and make average of the data , make a table and fill the table with the data. However, I want to modify the program so that it can open 5 text file at the same time and process the data. Below I have a code that open and read one file. Could you please give me an advice how to open and read multiply files at the same time Thanks in advance Filename = Application.GetOpenFilename() Close FileNumber = 1 Open Filename For Input As #FileNumber ' Get data, here I call the subrutine that read the data Close If DataCount = 0 Then MsgBox "No data" Exit Sub End If Tag: A macro to count the number of macros in a woorkbook
  - 12
    - .mdmp files not being created Hello, Under what conditions is the .mdmp file created or not I have written an app that (deliberately) dereferences a NULL pointer, but the generated error report does not have the "Files that help describe the problem" section when listing the report from the Control Panel "Problem Reports and Solutions". Viewing other reports that had been put there, some of them have the files and some don't. DrWtsn32 on WinXP does create a minidump when my program crashes, and I would like the same behavior to occur on Vista. How can I configure WER to create a minidump in this instance Thanks, David (MVP, Visual C++) Tag: A macro to count the number of macros in a woorkbook
  - 13
    - How do I accomplish this in VBA using Office 97 and later? I am trying to come up with a system that would improve efficiency in creating Request for Quote and Purchase Orders. As an example, here is what I am trying to do with the Request for Quote. 1.) A new document from a template called "Request for Quote" is created. 2.) The system asks for an Excel file to import that has bid item information in specific columns and from a set row down to the end. 3.) The system uses an Outlook address book to pull up a list of vendors. You can then select a checkbox control next to each vendor to include the vendor. Vendors are displayed one by one from top to bottom. 4.) The same thing done in step 3 is performed for the bid items using the Excel file imported earlier. 5.) The information is appropriately placed in the template. I can apply the Request for Quote concepts to Purchase Orders. Any idea on the most efficient way to accomplish this type of system Tag: A macro to count the number of macros in a woorkbook
  - 14
    - ASP .NET 1.1 fails at install, but not .NET Framework 1.1 Hi there, My Scorecard Manager 2005 installation finds that I do not have ASP .NET 1.1 installed, but it finds .NET Framework 1.1 installed. I could not find a download for ASP .NET 1.1 outside of the .NET Framework 1.1. Can anyone explain in a few words what is the difference between the framework and the asp .net To note that when I try to install the .NET Framework 1.1, which I cannot see installed in my programs, the installation is failing due to product already installed. I am a little confused. The OS is Server 2003 SP1. Thank much. Tag: A macro to count the number of macros in a woorkbook
  - 15
    - How to allow cookies for Windows Live ID Help me! Tag: A macro to count the number of macros in a woorkbook
  - 16
    - Projects, objects & modules I started learning VBA in MS Word yesterday. I have some programming experience in C++ and Java. I am trying to understand how the environment is structured and what Projects, Objects and Modules represent. For example I recorded a few macros using the macro recorder and then opened the VB Editor to see what was created. I notice that under "Project" are 3 folders. One is named "Microsoft Word Objects". Within this folder is what looks like a file named "This Document". What does "Project" represent What does "This Document" represent What is the difference between "This Document" and the "Project" Also under "Project" is a folder named "Modules". Within this folder is what looks like a file named "New Macros". This "file" seems to hold the code that represents the macros I recorded. Right clicking on the "Modules" folder allows me to insert another module into which I can add code for a Sub/s (short for subroutine/s I assume) via the VB Editor to do whatever. Why would you put code for different Subs into different modules The simple tutorials I have been studying seem to put all Subs into the one module. Why is the code for the recorded macros put into a separate module I am trying to work out exactly what Projects, Objects, and Modules are and how they interact. It seems like some sort of hierarchy. This environment is very new and quite confusing to me at the moment. Any help or references appreciated. Tag: A macro to count the number of macros in a woorkbook
- Next
  - 1
    - ComboBox listing on Startup The ComboBox1 doesn't initialize the list automatically. It only puts up a blank ComboBox . Here is the code I am using: Private Sub Form1_Load() ComboBox1.Items.Add ("Open App1") ComboBox1.Items.Add ("Open App2") ComboBox1.Items.Add ("Open App3") ComboBox1.Items.Add ("Open App4") End Sub Tag: A macro to count the number of macros in a woorkbook
  - 2
    - PASSING MS OUTLOOK DATA TO EXCEL Is it possible to pass Outlook information such as dates and durations of meeting to an Excel spreadsheet I would need to do this via a macro. I need this to create a time card that tracks how much time is being spent in meetings. Thanks! Tag: A macro to count the number of macros in a woorkbook
  - 3
    - MDI on old Office but not on new Office 2007? I had regularly emailed myself in the past from my older version office {2003} {work laptop with no printer attached} to my newer office XP on my personal system to print but now that I have upgraded to Office 2007 the MDI is not recognized. How can I install the MDI drivers so I can print again Tag: A macro to count the number of macros in a woorkbook
  - 4
    - referenced memory error hello people. can someone help me. My computer displays an error saying instruction at "0x269e7875" refernced memory at "0x269e7875". The memory could not be "read". Click ok to terminate the program. And I cannot switch from one user to another. I can log-off from windows and switch user. It needs to be restarted every time I want to switch from one user to another. Tag: A macro to count the number of macros in a woorkbook
  - 5
    - Visual Basic Code Corrupted? Hi, I have been having several problems on a complicated excel spreadheet that I have been developing, that have led me to believe that the file i am working on has been corrupted. The problems started when I switched PC's that I used for developing (i.e. was given a slower PC to work on). Since then, I have been having all sorts of problems with my macros. Now, even when I open the file on other, faster machines, the results are still unstable, with features that were previously working normally still not working. Is it possible that the PC that I switched to has somehow corrupted the compiled VBA code stored in the excel file For example, a simple procedure that hides some columns on a sheet when a value is changed is not working. However, when I duplicate the exact same code, and trigger it when a different value on the sheet changes, the new procedure works. (The old procedure has a problem with activating the sheet on which to hide the columns). As the code is exactly the ame, could it be that the already complied VBA code in the excel file is corrupt Many thanks for any help! Tag: A macro to count the number of macros in a woorkbook
  - 6
    - Removing "Trial Version" from Web Components on SharePoint web sites Hello, I would like to remove the "Trial Version" as stated above. I used CSS but then other part disappear as well so this is not an option. Then I tried JavaScript but wasn't working either. Question: is it somehow possible to do it with JavaScript (I have only limited scripting capabilities). Or can I directly access the template that creates the different sharepoint web sites for my purposes I would appreciate advice on this matter and thank you for your attention. Regards Christian Tag: A macro to count the number of macros in a woorkbook
  - 7
    - Multithreading with VBA6 and Access Hello, I'm not so familiar with VBA/Access. I have to maintain an existing application. Now I need that a user can use the application and in the background a function must at certain times (these times are in a table param) lookup and export a XML file from a query. IS IT POSSIBLE TO CREATE MULTITHREADING WITH VBA AND ACCESS AND IF YES HOW I read at some forums it should be possible but I have never read how Thanks in advance. Nico Tag: A macro to count the number of macros in a woorkbook
  - 8
    - Validation with List drop down? Hi I want to add some simple validation to my Excel sheet but can not for the life of me remember how to do it! Basically i have a drop down list as in the image below and want so that when 'No' is selected, the user is forced to enter the reason in the next cell http://i102.photobucket.com/albums/m82/sc0ttb_2006/excel_list.jpg I can remember doing this before and used some VBA code but can not remember how i did it :( Please put me out of my misery! Thanks... Tag: A macro to count the number of macros in a woorkbook
  - 9
    - How can I show the formula result in my cells? I assigned a formula to Range(D2 10), but I cann't see the formula result in the spreadsheet, what I can do is only show the result by using msgbox. So how can I show my formula result in the spreadsheet Thanks so much! Private Sub CommandButton1_Click() Worksheets("MMS_RSP_Tonnes").Range("D2 10").FormulaR1C1 = "=MMS_RSP_GSV!R[0]C[0]*1000/GSVperTonne!R[0]C[0]" Dim A As Variant A = Worksheets("MMS_RSP_Tonnes").Range("D4").Value MsgBox A End Sub Tag: A macro to count the number of macros in a woorkbook
  - 10
    - Windows media files have changes to Nero Hi just wondering how I change my music files back to windows media player, they changed to nero flies last week after burning a CD. Hate the nero player, but love windows media player. Hope someone can help me Thanks Shihad P.S check out Shihad single ALIVE Tag: A macro to count the number of macros in a woorkbook
  - 11
    - Icons In ListBox Hi dears I want to add icon to the items of VB.NET 2005 ListBox , Could i do ... Tag: A macro to count the number of macros in a woorkbook
  - 12
    - Finding Quartiles From 2 Dimensional Array Using Excel's Quartile Function Greetings Everyone: I am trying to use Excel's Quartile Function in VBE to find the quartiles in the Columns of a 2 dimensional array. I am having trouble getting my mind around this, I think due to the fact that I do not know how to define each column as a range so that it can be used in the Excel Quartile Function in the form =Quartile(Range, 1), where 1 is the first quartile in the range. Is there a way to find the quartiles in a 2 dimensional array using this function on the range in the array of say (1,1:5,1) Or more broadly, how do I find the quartiles in the columns of a 2 dimensional array Thanks if advance for any help or ideas. Glenn Tag: A macro to count the number of macros in a woorkbook
  - 13
    - Assigning text box input to cells I have two problems. here is the first; In Excel I have a userform that prompts the user to enter values in text boxes. I then assign the values entered in the text boxes to various cells as follows; Range("B8").Value = TextBox1.Value Is there a more correct way of doing this I am not sure if I should be using Range. OK, here is my next problem; The userform is presented by a Do While loop so the user can enter a second, third, fourth etc etc set of values. Each time they enter set of values I want to increment the row number where they are copied to. Thus I need a counter variable to represent the row number. How do I incorporate this into the statement that copies the value from the text box to the cell I am guessing it is something like; Range("rowCounter8").Value = TextBox1.Value rowCounter = rowCounter + 1 Any ideas Tag: A macro to count the number of macros in a woorkbook
  - 14
    - VBA excel outlook / A program is trying to send mail using Item.Send hi, i am trying to send an automatic mail from excel VBA to the outlook outbox, when i send the mail i obtain this security warning: A progam is trying to automatically send e-mail on your behalf. the help buttom says: A program is trying to send mail using Item.Send A program is trying to automatically send e-mail using a Microsoft Outlook Visual Basic Application command, Item.Send. If you want this program to send this e-mail, click Yes. To stop the program, click No. If you are unsure which program is sending the e-mail or why, you may want to click No to avoid any possible spread of viruses. Note When this message is displayed, the Yes button is not available for 5 seconds. how can i disable this protection may i use SendKeys to "press OK" after 5 seconds how can i do it tks in advance Tag: A macro to count the number of macros in a woorkbook
  - 15
    - *** How can I add module code to a specific module? *** NEED HELP !!!! Hi, I am currently working on an Excel Workbook where I have added a brand new code module called "Calculations2007". This module (*.bas) file contains several new functions. I have been searching on the web on how to deploy this new module in my workbook to a group of production workbooks. Basically I need this new code to get to production. I have gotten to a point where I know how to write the functions out. But it always creates a module called "Module1" and with my functions in it. How do I tell it to create these new functions (or macros) to a module called "Calculations2007" By the way, I am doing this in VB.NET. Thanks for your help! -Sydney Tag: A macro to count the number of macros in a woorkbook
  - 16
    - ListBox.AddItem drops value for last column if semicolon is in data Hi, I am using a ListBox in MS Access 2003 with multiple columns that may have commas or semicolons are part of the data. I understand that when using the AddItem method that I need to use double quotes to surround the data before delimiting the values with semicolons. The problem is that when a semicolon exists inside a value, the ListBox drops the last column. Example: Code Block Dim strRowItem As String strRowItem = """" & "one;one" & """;""" & "two" & """;""" & "three" & """" List1.RowSourceType = "Value List" List1.ColumnCount = 3 List1.RowSource = "" List1.AddItem strRowItem In this case, the example row in the ListBox List1 will have "one;one" in the first column, "two" in the second column, and the third column is blank (and any subsequent AddItems will be off by one because it will start with the first empty column). I played with it a little bit and discovered that this behavior does not happen with commas, just semicolons. Is there a way to work around this, or am I doing it wrong Thanks, KW Tag: A macro to count the number of macros in a woorkbook

slimshamus

Hello all,

I need to loop through a bunch of excel workbooks, open them, and determine if there are any macros in the workbooks, and if there are, how many.

I know how to loop through the workbooks and open them. But how can I display the names of the macros that are defined in the workbook This must be possible.

Thanks!

Re: A macro to count the number of macros in a woorkbook

Shasur

Hi here is the crude code to get the procedures from a workbook

Code Snippet

Sub Count_Program()

Dim VBP As VBProject
Dim VBModule As CodeModule
Dim VBProc As VBComponent
Dim sLastProcName As String

Set VBP = ThisWorkbook.VBProject

For Each VBM In VBP.VBComponents

Set VBModule = VBM.CodeModule

i = 1
Do Until i >= VBModule.CountOfLines

procname = VBModule.ProcOfLine(i, vbext_pk_Proc)
i = i + 1
If LenB(procname) <> 0 Then
If procname <> sLastProcName Then
MsgBox procname
sLastProcName = procname
End If
End If
Loop
'MsgBox VBModule.Name & vbCrLf & VBModule.CodeModule.CountOfDeclarationLines
Next

End Sub

You need to add the Visual Basic for Extensibility reference in the References.

Please fine tune it to loop through open workbooks

Cheers

Shasur

Re: A macro to count the number of macros in a woorkbook

slimshamus

Thanks,

I tried it and I I get an error that says "Programmatic access to Visual Basic program is not trusted" in this line "Set VBP = ThisWorkbook.VBProject". Any thoughts on that

Re: A macro to count the number of macros in a woorkbook

slimshamus

Never mind, I just had to check the box in the Macro Security settings to trust access from VB programs. Thanks again

Re: A macro to count the number of macros in a woorkbook

tracycan

Can you send me the code to open the workbooks I want to open workbooks from a list embedded in the file. Can you help Is this how you did it