Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [excel]

Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.

Filter by
Sorted by
Tagged with
0
votes
0answers
8 views

Dynamically create ordered list in outlook from Excel

I have working code for this project, but I am now in need of some help to create a dynamic ordered list within Outlook from Excel. I am trying to format comments that a user will enter into a ...
3
votes
0answers
37 views

Mapping Excel rows to model

I have an Excel file and each row within the sheet represents an employee's data. I am trying to find a cleaner way to map each row to a model object: ...
1
vote
1answer
51 views

excel vba discrete random variable udf

I am working on writing a discrete random variable generator function in excel vba. I've pulled this off a number of ways but here is the one I like the most. Imagine in excel we have the following ...
2
votes
1answer
86 views

Excel VBA code that writes to a Table - much slower than writing to a simple range

I have a piece of code (below) that checks a data table (45,000 rows) to see if the row matches any entries in a range elsewhere on the workbook (using custom functions). The routine then creates an ...
3
votes
1answer
77 views

VBA and SQL to return SQL results to Excel

Now that I finally have time to look back at this code I wrote over a year ago. I need help with streamlining the code I have written. I had to write a significant amount of ...
2
votes
0answers
33 views

E-Mail Automatisation Project with smtplib but did not use MIMEMultipart

The goal of the project is to automate sending the same e-mail to a list of people with the same text (except the salutation "Dear Name of Person") with an HTML signature and a PDF attachment. For ...
2
votes
3answers
62 views

excel vba udf: entire column as fn parameter

I have a function that accepts a range as an input. It works fine when I supply the function with a range like a1:a5, but not when I use an entire column like a:a. I could always loop over the whole ...
3
votes
1answer
66 views

OOP Dependency inversion principle VBA

I've reproduced the example of this video where dependency inversion principle is explained by Tim Corey based on C# I had some trouble understanding the scope of the Logger and MessageSender ...
3
votes
0answers
17 views

Speeding up a complex array function in excel

I have a complex array function that takes way too much time to calculate, which is why I'm looking for ways to make it more efficient. Basically, I have a transaction sheet in which I list all goods ...
1
vote
1answer
55 views

Remove row when everything in the row is same

I have a code that will delete the row if the entire row is exactly the same. So if in row 1 I have 100,200,300,400,500,600 in column A,B,C,D,E,F respectively, and in row 3 I have the same thing, it ...
5
votes
1answer
62 views

Manage Excel Styles with VBA OOP Approach (Follow up)

This is a follow up to this question Code incorporates Mathieu's comments and it works. As some parts of the review left some code to my knowledge, I ask for another review to see if I implemented ...
5
votes
1answer
52 views

Manage Excel Styles with VBA OOP Approach

I want to manage the Excel Styles in a more flexible way with bulk actions and at the same time improve my newly acquired OOP concepts. Objectives: Load the current Styles list (name and type=...
1
vote
1answer
63 views

Changing all comments' size in range to autosize

I have a code that turns all comments size to autosize, however as I have quite a few cells with comments, this code runs a little slow, to the point where it will become non responsive for awhile. <...
2
votes
0answers
65 views

Intersect Method Slows Down Code In Large Selection Of Cells

I have picture in certain cells. When I run following code (With Intersect Method) in smaller number of selected cells, it runs correctly but when the number of selected cells increase - say above ...
2
votes
1answer
63 views

Sluggish Performance when using ranges from two workbooks to update the one

I'm hoping someone can help me here, I'm working with a file which has about 38000 rows and 180 columns. I'm using a macro to update the fields in the one workbook with the values in the other ...
5
votes
1answer
75 views

Fetch, plot, and send security incident statistics with Excel

TLDR: I don't develop for a living. If someone could point me in the right direction to make my script more readable/pythonic I would appreciate the assistance. In particular I'm struggling with the ...
1
vote
0answers
20 views

Spreadsheet to increment two levels of data indices

I'm posting this after reviewing the Meta Q&A, Non-code formula spreadsheet, and I believe I've got something here that meets the criteria there. Edit: I've added documentation of the formulas ...
3
votes
1answer
54 views

Excel Import Data from Different Workbook Dynamic Headers

I have this code below that copies data into a master workbook, in sheets "Inputs1", by matching the column headers from the master workbook with the source workbook. If a column header matches, it ...
3
votes
2answers
91 views

Persisting Variables Over A State Loss

In an application that I built recently, I needed to persist several variables, without loosing their state, for duration that the application was in use. After some research, I found this post on ...
11
votes
1answer
112 views

Recursive code to generate a parametric sweep of input values

I generate a set of data based off every combination of inputs. The source range that's inputs requires headers to identify the parameters. In its present state I'm not content as it's not as ...
0
votes
1answer
70 views

VBA Excel Looping through recordset and storing the data to use later

I have a loop to add Recordset items to a Dictionary so I can do comparisons and retrieve specific data later in the code (not ...
7
votes
0answers
137 views

Tick. Tick. *breathe* BOOM! - Taming the SetTimer API

WinAPI Timers can be quite tricky to work with, as anyone who's tried to use them and fallen foul of one of the many pitfalls probably knows. Problems such as screen freezing, crashes, uncontrolled ...
2
votes
0answers
62 views

VBA code to account for multiple users too heavy on save actions

I have a worksheet I use with a small handful of colleagues on a daily basis to share information with about orders we place. To make sure we always have the same up to date info we use a shared excel ...
1
vote
0answers
50 views

Copy Non-continuous Cell Values

Backstory: I'm creating department level workbooks that contains director-level information, so I can't use a single workbook (as easy as it would make this). I need to split (and combine) 4 workbooks ...
0
votes
0answers
29 views

Performance of ID's checking between Access and Excel

I want to increase the performance of this code: ...
3
votes
1answer
123 views

Python List of Dictionaries to XLS

How may I optimize the following function to write a xls file from a list of dictionaries using the openpyxl library in Python? The current logic behind feels very very wrong The dict list: ...
0
votes
1answer
64 views

Excel VBA Sub Routine is extremely Slow deleting a Range [closed]

The following script is very slow and I'm not sure why. The script works so if you copy and paste its a typo in the question. ...
1
vote
0answers
78 views

Using arrays instead of regular loops

I have a working code and it works fine for small amounts of data ...
2
votes
1answer
43 views

Populate list with Excel range and keep formatting

I have written a Python programme that takes data from Excel sheets and adds this data to new .DAT files. I use win32com.client because the Excel sheets will ...
1
vote
1answer
60 views

Look for existing pivottables and change date/year colums based off the “Value” filter

We have a macro that updates all dates/years in all our pivottables (around 100 of them pulling out data from our OLAP cube) based off of a "Master Date" at the top of our sheet (which is being ...
3
votes
1answer
39 views

Import Excel sheet into MySQL

This is the first project I have coded using Python. Therefore, I am looking for someone to give me some comments to further improve my code. Purpose of the application This application help the ...
6
votes
1answer
204 views

Excel's Dynamic Array Functions…In VBA (No Win 32)

In recent months I have been trying to figure out how in the world one can mimic the functionality of Excel's New Dynamic Arrays exclusively in ...
2
votes
1answer
353 views

VBA loop optimization

Hi folks: I posted a SO (https://stackoverflow.com/questions/57541246/optimizing-vba-function-loop) and was told to ask here. I am starting to think that rather than relay on excel as my data ...
4
votes
1answer
261 views

Compare two Excel sheets by Cell Content

This code is a result of a lot of help from this community. What does it do? It compares two excel sheets cell by cell and copies the differences into another excel file. I’m trying to modify the ...
4
votes
4answers
159 views

Find differences between two spreadsheets

I have this code that is supposed to compare two Excel sheets. The code is working fine for small comparisons. I did a test run with 7 rows and 2 columns. The code itself works as follows: it ...
1
vote
1answer
37 views

Clean up code that transfers data from DB2 Server to Excel

The code below is functional and works as expected, but I imagine there is a better way to test for the error that I am testing for . The scenario is that data is transferred between two different ...
4
votes
5answers
125 views

Turn On/Off Functions to speed things up

I have these two macros that are like my Yin & Yang. They turn off and on functions in excel so my macros calculate quicker. I call on in the beginning and one at the end. Is there anything I ...
0
votes
1answer
81 views

Excel VBA - How to optimize this code with a lot of inserts

Can you please suggest how below code can be optimize? It takes around 2 minutes in Windows and 4 plus minutes in Excel 2016 for Mac. I think I will have to use write once method, accumulating the ...
0
votes
0answers
50 views

Can Excel Vba code be updated via an Excel Add In for multiple users

I have created an XLAM file called "MyCustomAddIn" from an XLSB file called "Call Tracker" which contains all my Subroutines, Functions and UserForms. I have referenced "MyCustomAddIn" inside of "Call ...
3
votes
1answer
75 views

Excel VBA code that copies data from one sheet to another

I have written a VBA code that copies data from source sheets into destination sheets. The destination sheets are formatted as a table so that formulas can be calculated automatically to update a ...
4
votes
1answer
48 views

Exporting array of tabs to new workbooks, save them based on tab name

Brief: This is a question geared towards optimization and general better coding practices. I'm self-taught in VBA so I'm cautious of bad practices, but they do come up. My Reasoning: I break the ...
6
votes
2answers
98 views

Statistical User-Defined Functions (UDF)

Problem The following statistical functions were created to calculate statistics of experiments: Mean Peak to Peak Standard Deviation Variance Mean Absolute Error (MAE) Mean Square Error (MSE) Root ...
0
votes
0answers
81 views

Excel: read data into ADODB recordset

I wrote the function below, but I feel it could be improved and so I am open to your wise suggestions. The use case for this is to manipulate Excel data. As a freelance dev, I cannot always choose the ...
2
votes
1answer
41 views

Reads files into 12 different sheets & cross compares files with audits and charts it out

I was wondering if anyone has any improvement ideas for my code. At the moment it is somewhat sluggish though it does work. This is my first project in VBA; I come from a background in PHP/Python. <...
12
votes
1answer
123 views

Not so Excellent Knight's Tour

Note: This is part of a set of demo applications I'm writing for colleagues to show we're not limited to VBA when dealing with Excel. Considering our IT challenges (no admin on-site & no local-...
1
vote
2answers
64 views

Macro for transforming 2 way matrix into 1 way matrix

I have written some code that transforms 2 way matrices into 1 way matrices. Although it's comfortable to use, it's very sluggish and slow in execution. Does anyone have any idea how to improve the ...
4
votes
3answers
143 views

Brute force looping & formatting Or Create Union range & Format? Which is efficient and when?

May be my question is not up to the standard for Code Review, but of upmost importance for reassessing methods used in my VBA coding . While preparing/testing answer for a SO post (thought to simplest ...
3
votes
1answer
90 views

Updating yearly reporting templates, ideally before the next year starts

Work flow is: Copy Previous Year Reports Folder Update Following Templates Save Close There are 19 workbooks in the Source Folder and in the NewYear Folder. Each Workbook has to save the final data ...
3
votes
1answer
64 views

Matching and Merging CPU info between worksheets

Goal: Transfer Sheet Test1 2nd Column to Sheet TestS 2nd Column. Where: For each CPU in Test1. If CPU name in Sheet Test1 ...
0
votes
1answer
55 views

loop code through each slicer value selection

I have a PivotTable with a slicer that has 5 filters. I'm looking to have these filters to each be selected individually one by one and run a copy/paste code for every filter selected. I've set up ...