Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.
1
vote
0answers
15 views
Optimizing large excel report
I got problem with generation of my report. It's done by VBA code. Data is read from other sheet in report.
Most time is wasted on execution of LineColor() and <...
3
votes
0answers
19 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 ...
1
vote
0answers
29 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 ...
2
votes
4answers
72 views
Extracting data from .txt files and writing to Excel - follow-up
After I got this answer, I edited my code after that answer, but the code still seems kind of slow. Can I make any other improvements? And why does the code seem to get slower with every minute? It ...
3
votes
1answer
70 views
Extracting data from .txt files and writing to Excel
I am extracting data from .txt files and writing needed data to Excel. My problem is that my code is very very slow and at one point even slower (+35k txt files). How should I rethink this problem?
<...
4
votes
2answers
59 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
23 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
36 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
1answer
73 views
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,...
3
votes
0answers
20 views
Creating an Excel sheet and appending namings for a matrix
I'm working on some VB.Net code to generate data sheets for giving user rights on a per folder level. I came up with this code but it's very slow and I feel like it could be improved.
...
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
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 ...
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 ...
4
votes
0answers
44 views
Counting instances of a string in a binary / unicode file using regex
(Originally posted on Stack Overflow)
I need to run through a number of bespoke files (I'm unsure if they are unicode / ascii or binary so have attached a snapshot through notepad++). I'd like to ...
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
53 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
49 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
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
72 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 ...
2
votes
3answers
71 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 ...
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
111 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, ...
3
votes
2answers
62 views
Performance of using objects with scripting dictionary
The code below is a working excerpt from a larger application that I am re-factoring. My question focuses on the performance aspect of using (or not using) objects in this case.
I have a medium-ish ...
4
votes
2answers
72 views
Updating access records
I spent an hour or so this morning compiling this, and since I have started reading Clean Code and The Pragmatic Programmer I figured I would let you help me get slightly better at this.
Due to some ...
3
votes
2answers
72 views
Populating a ComboBox from a Range
I recently had quite a lot of fun answering a Stack Overflow question, and I think I've gone a bit overboard and ended up with a fairly decent way of populating a ...
4
votes
2answers
68 views
Comparing dates from sheets
I am looking to increase the speed of this Excel VBA Nested Loop. The loop compares dates from one sheet to a secondary sheet. If they match, I change the border around the cell to highlight it. It ...
2
votes
0answers
92 views
Multithreaded download of images from a spreadsheet
I've been wanting to go async with my HTTP calls but all the methods I tried have not worked, so I resolved to implement this as a task and then improve upon it.
This is what I've come up with so far....
3
votes
1answer
76 views
Comparing two lists under 6 columns with 32000 rows in excel vba using dictionary
I need to import 2 .s19 files to excel and then Compare across 6 Columns in Excel and store the output to a txt file. I have my data from column A to F. My current program doesn't store the data using ...
1
vote
0answers
32 views
Server side formula computation of an Excel like client side view
One of the projects I am currently involved in requires to have a view that is similar to one in the Excel, except that most of the cells are read-only and the area is much more limited (the maximum ...
1
vote
2answers
35 views
Generate Orders from Matrix and save as CSV
I had a task to create a VBA macros which will take a matrix data set with products in rows and persons as a columns (on intersection we have an ordered qty for each material for each person) and ...
3
votes
0answers
35 views
Making a report from Payroll Details Part IV
This is probably my final submission to Code Review regarding this making a report from payroll details series. Basically, working in the human services sector, the funding agency requires A) Weekly ...
6
votes
2answers
136 views
Find and copy pasting optimisation
I am trying to apply this simple macro to merge two big sized sheets (around 30000 rows each), but the process is too slow and never ends. The macro works perfectly with smaller sheets. Could you give ...
