Questions tagged [vba]
The vba tag has no usage guidance.
27
questions
1
vote
1answer
136 views
Data Matching In VBA - Best way to deal with dynamic data and user entry?
Background
I am currently building this project with VBA, just to keep in the back of your mind when thinking about my question.
Imagine 2 adjacent blocks, in Excel. The first block is made up of ...
-2
votes
2answers
154 views
Excel/VBA to Application
In an engineering company, there are many engineers that edit in Excel/VBA Spreadsheets to perform some engineering calculations.They all do more or less similar things: calculate, manage engineering ...
14
votes
4answers
11k views
Why does Microsoft still ship VBA within Office products, instead of integrating .NET directly? [closed]
I know that it's possible to call .NET code from your VBA code, but why does VBA continue to exist? The only reason I can think about is legacy.
I just had to sort a Scripting.Dictionary and the ...
-2
votes
1answer
116 views
Trouble Designing Programs in VBA
I am new to VBA and am struggling with overall program design:
Toy Example
Input: Spreadsheet with 20000 rows and 50 columns. Also, order of rows matters.
Task: Create 3 different reports on a new ...
4
votes
1answer
105 views
PowerShell performance when running Excel macros
I run on a daily basis a set of VBA-rich Excel files. Most of them include MS Office application cross-talk, but also employ third-party applications and MySQL. Due to the fact of running those files ...
2
votes
4answers
396 views
Conditional vs Logical Testing
I would like to get your code thought and views on using conditional vs logical testing.
For example:
To test the conditions of truthness of all of the following variables, their currect status is ...
-2
votes
1answer
62 views
Strategy for VLOOKUP in VBA
I need to fill a Products table in file A with their manufacturing costs that are in another Costs table in file B.
Currently, I read Products and Costs tables into their respective arrays and for ...
2
votes
2answers
4k views
Can I really make python work with VBA
I'm a heavy VBA user that when has the chance to do a little project in python it's like going from hell to haven when it comes to programming itself. Still, I know that my users want reports in excel ...
0
votes
1answer
194 views
Sequence matching problem is complicated, how can I break it down into easier smaller pieces?
I have two lists:
1:
1) A abc
2) A def
3) A ghi
4) B jkl
5) B mno
6) C fzy
7) C xxa
2:
1) vsf
2) jkl
3) fzy
4) xxa
5) xyz
6) mno
7) def
8) abc
I am trying to match each of the continuous ...
1
vote
1answer
412 views
What are the key concepts in CPM/Gantt Chart algorithms?
having a bit of trouble getting started with this problem, and not much luck googling solutions.
I can't use MS Project to display gantt charts, so am wanting to use VBA to draw a pretty gantt chart ...
4
votes
1answer
1k views
Block Scoped and Function Scoped Languages
I've noticed that some languages like C, C++, Java, Perl, and .NET Visual Basic have "block" scoping which means that a variable will only be defined within the specific code block it was declared in.
...
2
votes
2answers
3k views
Managing sets of configuration data for an Excel/VBA program
I have two versions of an "Excel-Tool", i.e. a spreadsheet with a lot of vba code. Lets say, this tool is used to monitor two facilities. Both facilities differ in minor aspects.
That means, I have ...
3
votes
1answer
960 views
VBA Outlook: quickly find subfolder
I have the following structure in my Outlook Public Folders.
-Public Folders
--1001_RandomProject
--1002_AnotherProject
--1003_Yetanotherproject
...
and so on, basically there's a couple of thousand(...
1
vote
0answers
4k views
Excel: Vba Copy and paste efficiency with 000's of rows and columns
I'm sure this is probably out there but I can't seem to find it.
Updates
I have tested some different code, and reduced my time to complete the task from 50/60 seconds to 10 seconds. This is what I ...
9
votes
5answers
863 views
How to refer to ByRef and ByVal in a dropdown label? [closed]
The MSDN page on ByRef and ByVal keywords isn't very helpful; unless I somehow skipped it, they're simply being referred to as "keywords" everywhere.
One of the main contributors of the Rubberduck ...
2
votes
1answer
508 views
Why would a program allow an object to be initialized when it doesn't support all methods/properties of the interface type it was defined as?
I'm under the impression that an object MUST support all methods/properties upon being initialized or else the program will create an error. However, if you see below I am defining 2 variables (doc ...
8
votes
6answers
2k views
performance versus reusability
How can I write functions that are reusable without sacrificing performance? I am repeatedly coming up against the situation where I want to write a function in a way that makes it reusable (e.g. it ...
1
vote
1answer
675 views
Range or Row and Column Indices in VBA
I would like to know what the pros and cons are for storing a range variable instead of four integers or long variables (representing the first and last row and column) to define that range. One ...
20
votes
7answers
10k views
Team member questioning moving from VBA to C#
Background
Last year, I was asked to create a tool to be used for business planning for around 10 users. This was done on behalf of another IT team who "sub-contracted" the work to me, and due to the ...
2
votes
1answer
704 views
Should I use Strategy Pattern for this task?
I'm setting up a data validator that will iterate through the rows of a spreadsheet, and for each column, perform a validation check. I was thinking that this might be an appropriate task for the ...
3
votes
1answer
381 views
Implementing a Username Class
I am attempting to encapsulate several features about a user in a single class. Although the main use for this class would be to initialize it once and never have to call set any variables again, I ...
-3
votes
1answer
635 views
Automating daily procedures [closed]
Is there a way to automate running VBA in Excel to clean up the file from inside an application?
Everyday my office has to confirm that 50 people have shown up for work on time. The scheduling ...
8
votes
2answers
3k views
Why does VBA / VBScript require "Set" prefix
When I set a variable to an object in VBA / VBScript, I always need to prefix the statement with "Set" such as: Set Dict = CreateObject("Scripting.Dictionary")
If I set a variable to a primitive or ...
2
votes
1answer
3k views
Guidance on Excel VBA resource scheduling algorithm?
This was posted originally at StackOverflow though suggested to post here instead.
I am looking to create an Excel VBA solution that will create a rota/schedule allocating staff to service users ...
9
votes
0answers
758 views
Implement FBP / Dataflow programming in VBA?
Intrigued by Flow Based Programming a la J Paul Morrison. I wonder if one can implement FBP in Excel/VBA. I searched quite a bit for a Basic-Lang FBP implementation to no avail. I've looked at some ...
2
votes
1answer
254 views
Enterprise application with lots of SQL queries
Context:
I'm working on a fairly large Access database (which I inherited) that has its interface and tables separated. In order to make this work together, we have a lot of SQL queries (dozens). ...
-1
votes
3answers
318 views
Which programming designs leads to more problems?
As a fresh programmer, one of the first thing for me to learn was to learn language and its syntax. Now my next training issue is to design my code heirarchy in such a way it is simple to maintain ...
