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.
2
votes
0answers
7 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 ...
0
votes
0answers
7 views
calling a module from another module [on hold]
I have a piece of code that works perfectly when I run it from it's own module.
When I call it from another module, however, I get the Argument not Optional error message. Can anyone spot what I'm ...
2
votes
1answer
27 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 ...
-5
votes
0answers
22 views
Spliting the pricing values based on the website, quantity, lead time, availability [on hold]
i have excel worksheet which has 9 columns in it, like Date, Website, Website_code, Part Numbers, Part number returned, Quantity, Lead time and Availability. Same part numbers have different pricing ...
1
vote
0answers
27 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 ...
0
votes
0answers
10 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
55 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
21 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 ...
0
votes
0answers
20 views
3
votes
1answer
34 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 ...
1
vote
1answer
39 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
17 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
19 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
197 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
43 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
76 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 ...
2
votes
1answer
36 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
37 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
32 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
38 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
81 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
52 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
39 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
50 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 ...
2
votes
1answer
71 views
Allowing paste in a protected Excel workbook
I have a workbook that some very non-technical people will be entering data into. In the past, I've had them type over formulas and otherwise screw up the data, so I've locked everything except where ...
3
votes
1answer
91 views
Passing multiple parameters to an SQL query
I am trying to use this VBA code to pass a SQL stored procedure multiple values from an excel sheet.
In order to have the procedure run multiple times and insert multiple sets of information into the ...
3
votes
1answer
40 views
Making repeated ADODB queries from Excel-SQL Server
I need to run 12 queries on a SQL Server DB. Currently I pass the "SELECT..." string to a function below.
Is it optimal to open and close the connection again and again (as my code is doing), or can ...
2
votes
1answer
65 views
Substituting bookmarks in a Word document with data combined from an Excel sheet
I have managed to write code that does something fairly complex. It's working as I need it to, but it is so slow.
Information about a list of suppliers needs to be consolidated into a word document ...
4
votes
2answers
72 views
Determining the number of days in a month
Overall logic: determine number of days in a month. Loop through table (wsUploadTable) and increment value if condition is met. Loop to next day in month and repeat....
3
votes
1answer
22 views
Comparing and pasting ranges of spreadsheet cells
I have a moderate size sub-procedure where I am calling multiple functions to feed values into other functions. I feel there may be a better or more concise way to present and run this module, but ...
3
votes
2answers
52 views
Find Date in Range
I have a spreadsheet where I control my shift hours. In such spreadsheet I input the date, the start time, time left for lunch, time back to work, end time and sometimes the overtime.
After that I ...
0
votes
1answer
30 views
Collect data from 4+ columns and insert data into another column
I have a macro which seriously needs to optimised. I have changed the nested loop, but it takes off only a few minutes. The entire macro runs for about 14 minutes. I think the logic needs to be ...
2
votes
1answer
30 views
Parse Live data feeds to translate product codes
This is the second review of these code blocks. I made major changes and updates to the functions during my refactoring and I would like a follow up review. The original review can be found here VBA ...
3
votes
1answer
32 views
Computing and Populating Excel Table With VBA
I am trying to populate a table like this in Excel. I came up with a bunch
of loops to do this. I feel like this is a brute-force approach and may not work well when populating bigger tables.
Any ...
1
vote
1answer
48 views
VBA performance with Early Bind vs. Late Bind on called Sub-procedure
I was using early bind to reference the dictionary and realised that some users might not be willing to manually add the reference to use it and just consider the macro to be 'broken'.
I looked at ...
4
votes
1answer
88 views
Web scraping VBA - Internet Explorer
The code below extracts data from one web page - I emulate search, select all results from the list and when the list appears (42000 items) I loop through these items.
I get an id value from their ...
4
votes
1answer
47 views
VBA Function Reads Active Live Data Feed and Translates Product Codes
The following Function is one of the main analysis functions of a larger subroutine. The subroutine is responsible for translating codes that look like this "1|G|XNYM:O:LO:201611:P:44:+1/XNYM:O:LO:...
3
votes
1answer
45 views
Sorting and summation of a spreadsheet
I am working on a sorted data file, so there does not need to be any sorting logic. I find unique values, feed them into an array, then use the ...
3
votes
1answer
52 views
ApplicationSettings Class for disabling/restoring Application State
This previous iteration of this question can be found here
A utility class to handle the state of the Application object.
Storing the application's initial state (...
4
votes
2answers
67 views
A 'flexible' VBA approach to lookups using arrays, scripting dictionary and user input
In my previous post Optimise compare and match method using scripting.dictionary in VBA I wanted to address optimising the scripting.dictionary approach I was using and I feel I have achieved that (...
1
vote
1answer
36 views
ApplicationSettings Class (Store, Restore, Disable, Reset)
Update: This question has a follow-up here
A utility class to handle the ubiquitous "turn off ScreenUpdating, Calculation, Events etc" for executing VBA code, and ...
4
votes
1answer
25 views
Populating a list of all .wtv files across 4 hard drives
I am using Vba excel to populate a list of all my .wtv files using the .GetDetailsOf method. There are some 3000 files across 4 hard drives filed in a b c... subdirectories.
This sends my memory ...
2
votes
1answer
71 views
Optimise compare and match method using scripting.dictionary in VBA
In my previous post I was looking for a better solution to write a single column of a 2D array into a worksheet besides looping between code and worksheet and finally managed to achieve that.
On ...
5
votes
1answer
71 views
Class to retrieve external data
I have recently come to use VBA class modules in my code.
Based in part on an answer on Stack Overflow, I bought VBA Developer's Handbook and Professional Excel Development, but it took me a long ...
2
votes
3answers
70 views
Writing a particular column of a two-dimension array to a worksheet
I had some great feedback on my code previously here and I was hoping that I could get some further help with writing a column of the array to the worksheet.
I have an example here where I write one ...
4
votes
4answers
122 views
Compare and match results in two two-dimensional arrays in Excel VBA
I have four loops to populate and compare two two-dimensional arrays then add the results to the first array before writing back to the active worksheet.
Wondering if anyone has a cleaner alternate ...
2
votes
1answer
84 views
VBA multiple sumifs and countifs groupings in large range using loop
I am looking for assistance with the code at the last section referenced as "Logic to calculate allocation". I'm looking to conduct various SumIfs divided by ...
1
vote
2answers
30 views
Test blank cell and out put phonetic (text)
I have this code which first checks for blank cell in Column B, and then blank cells in Column F. If the results is Yes-No, then it prints the phonetic into F.
Can someone review the code, please? I ...
6
votes
2answers
109 views
Excel display driver class for Tetris
This is the first of (hopefully) several posts as I reimplement an ancient (and unfortunately long lost) Excel VBA Tetris clone. The main workhorse class in the game engine is the display driver, ...
2
votes
1answer
69 views
Load txt to Scripting.Dictionary
This code load a 40MB txt into a dictionary. It runs in about 40 seconds (sometimes 20, no idea why). Is there a way to make it run under 4 seconds, or hopefully in 1 second?
...
