Visual Basic for Applications (VBA) is an event-driven programming language first introduced by Microsoft in 1993 to give Excel 5.0 a more robust object-oriented language for writing macros and automating the use of Excel. It is now used for the entire Office suite.
-4
votes
0answers
12 views
User form look up using loops [on hold]
I am trying to set up my user form to do a loop or look up to reference my table which is on a sheet and is a large data base. I am wanting my user form to look up what I type and then auto fill in ...
3
votes
2answers
56 views
Sum hours worked depending on day type
A while ago I posted this question about finding the Date in Range, where I explained I have a spreadsheet to control my shift hours and was interesting in improving one of my functions.
I got ...
3
votes
1answer
57 views
Else if .value = 1 to 92 code check
I've written code that selects column D and checks it for codes 1 to 92 however I have to write out c4.Value = 1 Or c4.Value = 2 all the way up to c4.Value = 92, is there a quicker way of doing this? ...
6
votes
2answers
338 views
Find cells with specific formatting
I'm currently writing code so that when a red-colored cell appears in a column, a message box will appear letting me know that column has an error. I have written the following method:
...
2
votes
2answers
36 views
Efficient method to sort read-in text file in Excel
I've read in a text file to excel from a database and I've done it in such a way that it filters out unnecessary columns. My approach to filter rows was to use two subroutines and call the 2nd from ...
2
votes
0answers
36 views
Comparing two arrays and printing results as a collection of a class
This is really my first time using a class and I originally wrote it without the class and without refactoring.
This is to take two inputs (csv), place the data in arrays and then compare the arrays. ...
6
votes
1answer
67 views
Detailed Last Row Finder
For a project I created a more detailed last row finder which looks at more than one column.
Could you provide any feed back on the code below.
...
2
votes
2answers
71 views
Search through each file in a directory, list number of instances of searched string
I am trying to open each file listed in Sheet2 Column A, search for a string of text located in Sheet3 Cell B1, list the number of instances this string appears in each particular file in Sheet3, and ...
4
votes
1answer
45 views
Merging rows in excel using VBA
For the first time I've worked in Excel VBA to find rows in my dataset that contain the same adress as another entry in a cluster. These entries have to be merged and the row then is deleted. I've ...
3
votes
2answers
63 views
Posting accounting data to a report
I have some buttons on an Excel report. 2 in particular are for posting all Budget numbers to the report and posting the Actual numbers. I currently have 2 separate subs, one assigned to each button.
...
7
votes
1answer
112 views
A Moses's worthy string splitter
I am trying to perfect my string splitter's performance, to be more fast, more easy to maintain if someone else reads it and more readable code-wise.
Context ,Scope and Objectives
Where I work we ...
9
votes
3answers
97 views
Remove unwanted characters from specified column in all Worksheets
I need to remove some unwanted characters from a given column in all Worksheets from my Workbook. The code below works perfectly, but I'd like to know if there is a better/cleaner/faster way of doing ...
2
votes
0answers
61 views
Speed issue when controlling Excel from Access VBA
I have a very hard time figuring out how to proceed. I built an Access DataBase which I use to process data in order to build several weekly and monthly reports. I wrote VBA codes in Excel and in ...
8
votes
2answers
68 views
Worksheet that lists a person's name
I have a worksheet that lists a person's name (column A; many duplicate names are in this column) with associated data (columns B through G). I have code below that takes this list of a ~ 1000 rows ...
4
votes
2answers
72 views
Pairing all identifiers from 3 sheets and list them on a report worksheet
I have this, and I think it's working? It's been running nearly all day and still isn't finished, so I'm hoping there is a better way to code it.
Right now I have a series of loops that iterate ...
5
votes
2answers
47 views
Summary Data According to Machine No, Order No and Shifts
Here is the code that I write for my work on Excel-vba. I use that code to know shifts' and operators' performances.
Main purpose is to divide machines' work to orders and shifts.
...
4
votes
1answer
45 views
Transfer of data instead of copy.paste.clear used
I am using this code in a worksheet to check the condition, and based on the condition, copying the entire row of values in a different range of particular condition if true.
For copying I am ...
2
votes
1answer
58 views
Go to next visible cell without loop
I have an autofiltered table in Excel. I have to copy/paste values based on certain conditions and I also have to perform this on all visible cells in a particular column. I have written the code and ...
3
votes
1answer
89 views
Basic Password System in Excel VBA
I have created a basic login system that asks the user for a password. Here's a list of what it does:
When you run the code it will open an Inputbox asking you for a password.
There is one criteria: ...
0
votes
1answer
45 views
Copy/Paste of Range
I have gone through all steps to optimize the code, deactivating screenupdate, deactivating calculation, events, and pagebreaks removed the unnecessary selects, adding constants, and etc. However, I ...
0
votes
0answers
69 views
Mail merge with attachments and cc for MS Word
I am very new to coding and really started a few weeks ago for a new job I have. out of necessity, I am slowly learning VBA (which I can't say is very easy...) I started by recording macros in Excel ...
3
votes
1answer
37 views
Making an RBG chart on Excel
I was testing the RGB color mechanic on Excel, so I did the following:
...
0
votes
1answer
97 views
Splitting Excel workbook sheets
Running Office 2007, I have master workbook with data, which I:
Split master sheet data to new sheets for every salesmen
Create new workbook (with 1 sheet)
Put sheets of salesmen to an array ...
3
votes
0answers
76 views
Increasing speed of Excel VBA code used for order report
I'm on my second revision of this workbook and I've brought the runtime down from 37 seconds to 9 seconds on my computer and from 30-40 minutes to 8 minutes on my boss's old laptop. I'd like to know ...
1
vote
0answers
25 views
Too high complexity for a find and replace between Excel and Word docs
Here's my current objective :
I have an Excel document which contains n rows with 2 columns (C1 and C2) of data
I have a Word document containing n tables wich 1st row (R1) containing C2
I want to ...
-1
votes
1answer
25 views
Simplify toggle button change BackColor Code VBA
im new in VBA making, so all code below is still working tho but it takes a lot of line of codes. Even it is easier to maintain but if someone can simplify my noob-code to cut some lines and more eye-...
4
votes
1answer
61 views
Resizing the controls on an Access form dynamically
Below is a sub I wrote to dynamically resize all the controls on a form. It utilizes the Tag property of each control. In that ...
2
votes
1answer
52 views
Limit acceptable values for function parameter in VBA
I wrote a function in VBA for Excel that finds the highest row and highest column number in a range, which may include multiple, non-contiguous areas.
The function contains a parameter I call ...
3
votes
1answer
57 views
Optimizing large Excel report
I have a problem with generation of my report. Data is read from other sheet in report. Most time is wasted on execution of LineColor() and ...
4
votes
1answer
36 views
Tabulate schedule/calendar for database import
My company uses an Excel workbook to schedule our manufacturing jobs. I'm attempting to log job positions on the schedule and import them into Access on a semi-regular basis (twice a month?). This ...
3
votes
2answers
62 views
Deck shuffle and drawing cards
I developed a simple deck shuffle and drawing system, as a test on how to use collections and learn from it.
However, I think my code is a bit wonky, and I think here is the best place to get new ...
2
votes
1answer
63 views
Groups/colors/removes cells
Based on the value of what is in column B (column A is hidden) it groups/colors/removes the corresponding node(s). "R - " values are nodes that need to be grouped. The rows are 6 cells apart before a ...
1
vote
0answers
16 views
Insert Excel Graphs using Class to define parameters
I have a loop in my main sub routine which creates multiple graphs. This is run across a number of sheets, creating the same graphs for each sheet, but with different ranges and other variables.
I ...
4
votes
2answers
84 views
Handling many comboboxes and textboxes
I've designed a userform in Excel and it has many comboboxes and textbox inputs, once the user is done it puts ticks in appropriate columns depending on the ComboBox selection and also writes the ...
3
votes
1answer
34 views
Filling random values in two sheets from a single sheet via Excel VBA
I fill out random values in two sheets (Testfall-Input_Vorschlag) and (Testfall-Input_Antrag) out of another sheet (ADMIN_ARB11).
I have 371 rows in sheet (Testfall-Input_Vorschlag) and I have 488 ...
3
votes
1answer
78 views
Excel VBA - in a list, if cell equals specific value then
I started working on some code, and it works, but I feel like it could be done more effiently. Below is a portion of it to show what I'm doing. To simplify the idea, I've made it here so if the cell ...
4
votes
2answers
80 views
VBA copy paste loop
I have 2 Excel files: the first is the source file "Practice_New.xlsx" and the second is a mapping file "A_File.xlsx". A_File is a mapping file which contains cell reference of the source file ("...
0
votes
0answers
19 views
Updates values in master table based on conditions, then refreshes pivot table
There is significant screen updating going on with my code when I run it. The macro is being ran from a button where the textbox is. "JobCol_Master" is a sub-range of a column in the master table on ...
0
votes
0answers
23 views
UDFs with Header-Case selection is too slow
I am working at a project where we implemented 10 different UDFs which gave us high flexibility and a clean code but basically screwed the file up as the file got super slow in the end: The problem is,...
4
votes
2answers
206 views
Renaming form controls and underlying code
I was working on adjusting some variable naming on an Excel project and ran into an issue that the MSForm control names needed to be updated. When you change the properties of the control on the form, ...
4
votes
1answer
56 views
Comb Sort in VBA
Playing off Robust Bubble Sort in VBA and as suggested by @Henrik, I took a look at comb sort and tried to create an algorithm based on the documentation solely on Wikipedia.
Basically, the first ...
4
votes
1answer
104 views
Robust Bubble Sort in VBA
I'm creating a robust bubble sort for VBA when sorting stored arrays in VBA. Mostly this would be used when an array is stored in a single cell with a delimiter. Otherwise, one could just sort on the ...
3
votes
1answer
72 views
VBA - XMLHTTP web scraping
I navigate with IE, do various things, then select all results option from a list and fire on click event. Once all results have been listed, I loop through their URLs, using the following code to ...
3
votes
0answers
61 views
Decode HTML Entities in VBA using regex and dictionary
I receive HTML-encoded text that includes named and numbered entities, and I need the decoded HTML.
There were just too many bad solutions for this online.
I use regex pattern ...
2
votes
1answer
43 views
Prevent Variable Overflow from InputBox user error
I was writing some code that required the user to input a number, which was then stored in a variable. While testing the code I input a number larger than the variable type would allow and I got an ...
2
votes
1answer
48 views
VBA Macro - Speed decreasing with every loop iteration - substituting Word bookmarks from Excel
I previously asked this question but have since improved my code: Substituting bookmarks in a Word document with data combined from an Excel sheet
What happens in the code:
Information about a list ...
5
votes
2answers
90 views
VBA lookup to complete matrix
I have written the following the code to complete a matrix based on data provided in a second worksheet, but the code is really slow (note that s1=12,000, s2=40 and s3 = 200,000). Any suggestions on ...
3
votes
1answer
67 views
Copy and paste formatting is slow using VBA
The following code is a function that works. It's just slow and I don't know how to speed it up. It takes an Excel row number and the value of its headerval (string) and finds the same headerval on a ...
4
votes
1answer
44 views
Handling dozens of checkbox events in a Word document
I have a word document which has a lot of active x checkboxes on it and I have just been told the way it has been coded and done could be improved upon a lot, so I am asking for a bit of help.
To ...
2
votes
1answer
52 views
Calculating some worst-case values for angles in an interval
Sadly enough I have a bit of code that is really slow and being a newbie in programmation, I don't have the faintest clue on how to optimize. Is there any way to make those calculations faster?
This ...
