Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.
0
votes
0answers
16 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
16 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
14 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
194 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, ...
3
votes
1answer
38 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
71 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 ...
-1
votes
0answers
40 views
Excel VBA macro that deletes every row with a 0 in Column A
I'm using this macro to delete every row with a 0 in column A. The problem is that it is too slow. It took about thirty seconds to do the job for two thousand rows, but I need a macro to work on 300,...
2
votes
1answer
35 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
80 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
47 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
42 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
63 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
51 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
29 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
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
50 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
66 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
35 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
66 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
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 ...
2
votes
1answer
78 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
108 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
60 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
68 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
71 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
66 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
88 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
74 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
31 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
34 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
134 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 ...
5
votes
1answer
75 views
Sorting a 2D array on 1 dimension
Inspired by a previous question, I was looking into converting a table to a sorted list (ascending) -
Resulting in this -
I used a very simple bubble-sort, but I don't know how efficient that would ...
2
votes
1answer
46 views
Making a reporting from payroll details Part III
Thank you for all the help so far! This is in continuation from the project posted here: Making a report from payroll details part II
Basically, based on the user input in columns F, G, H, the macro ...
2
votes
1answer
35 views
Making a report from payroll details part II
Thanks for the help so far! Especially all of the people who helped out on part I - seen here. Making a report from payroll details
Here's the revisions to the code based on feedback from that thread....
6
votes
2answers
76 views
Binary search a 2D array for multiple matching criteria fields
Here is what I came up with for a binary search of an array. It allows for searching one or many columns to match. It assumes that array being searched is sorted with the same sort priorities as the ...
4
votes
2answers
89 views
Getting NTFS permissions of all shared folders on the local machine
How can I improve the speed of the script?
...
3
votes
2answers
43 views
Importing a report through the enabler4excel add-in
This connects to Salesforce to import a report through the enabler4excel add-in. The report overwrites what is currently there on the sheet (2016 Incidents Dashboard). The report contains about 55,000 ...
7
votes
2answers
166 views
Converting Data in Rows and Columns to Rows in VBA for Excel
I have a working VBA script for Excel that converts a matrix of data with multiple records in per row to multiple rows with one record per row.
A StackOverflow user told me that the code could use ...
6
votes
1answer
100 views
PowerShell script to create Excel spreadsheet from HTML file
This script is designed to create an Excel spreadsheet from each HTML table from auto-generated index.html file.
It needs to be very robust but unfortunately I ...
