Visual Basic for Applications (VBA) is an event-driven programming language which was 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. If your question is ...
1
vote
0answers
30 views
Excel VBA - how to make this ping test with timer more efficient?
I wrote a small ducktaped vba script which pings servers every 15 mins or so. If a server's status is anything other than "Alive", the server and timestamp is written to another worksheet called ...
4
votes
1answer
48 views
My VBS Word macro is really slow, looking for advice!
So, I have a 100page long docx format document. I'm using a macro written in VBS to extract some information and then just generate a table from them.
I iterate through the paragraphs and store the ...
0
votes
2answers
44 views
speed up merging worksheets and using find/replace
Of all the macros that i put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but i'd like to know why code 16x as long ...
3
votes
2answers
38 views
better way to remove from collection in excel vba
In my vba code, I am trying to generate a specific list of cell row positions. Basically how i do that is I first fill a collection with the entire list of row positions pertaining to a specific ...
5
votes
4answers
148 views
Improve VBA code Excel
Im looking for someone who can improve my code, because at the moment it takes 4 minutes to execute. I'm new to VBA so it's probably bad coding. Normally if you are good in VBA you will understand the ...
1
vote
2answers
63 views
EXCEL VBA Populate ComboBox
Are there any shorter/fastest version of code below? Appreciate any help.
'Sheets("Summary(FG)") ComboBox1 items
For b = 3 To Sheets("CustomerList").Cells(3, 2).SpecialCells(xlLastCell).row
If ...
3
votes
0answers
52 views
How can I improve this 31-bit SuperFashHash implementation in VBA?
I'm implementing a variation of the SuperFastHash in VBA for use in Excel (32-bit version, so no LongLong available) to hash strings.
To get around the limitations of signed 32-bit Long values, I'm ...
4
votes
2answers
1k views
Multiple nested If checks in VBA
The below code is real and in use, but I've modified it to simplify the process/make it easier to explain.
The purpose of this code is to combine data from multiple data sources. All sources are ...
2
votes
1answer
849 views
Better method to loop through arrays in VBA
I supplied the following code as an answer to this question on SO, but I was wondering if there was a better way to write out all those array loops. (i.e. perhaps using a Collection/Dictionary?) It ...
2
votes
1answer
413 views
Powershell Script for Manipulating Excel Files
I am a newbie at scripting so be easy. I have a directory of xls workbooks with the following naming convention:
001.WIP Monthly Report
002.WIP Joes Custom Report
...
129.PUR Supplier Spend
The ...
1
vote
2answers
170 views
VBA Data Conversion (random printout -> CSV file)
I have reviewed the FAQ and I believed it was yes to all 5 q's of "What questions are on-topic for this site?"
Basically, the code below is used to perform the operation shown in the picture. I'm ...
5
votes
6answers
275 views
Looking for a tidy-up of code that gets the job done, but takes a long time to run
UPDATE Code below updated for improvements thus far. I think this part:
Sheets(i).Activate
Range("A6").EntireRow.Select
Range(Selection, Selection.Offset(1000, 0)).ClearContents
still needs ...
4
votes
3answers
689 views
Is it a bad programming practice to declare variable in a loop?
Today, I came across a code example like the following:
Option Explicit
Sub MySub()
Dim iCount As Integer
For iCount = 0 To 100
Debug.Print "Arbitrary Code Here"
If iCount ...
0
votes
1answer
208 views
what does the below excel-vba codes do [closed]
Public Sub delay(seconds As Long)
Dim endTime As Date
endTime = DateAdd("s", seconds, Now())
Do While Now() < endTime
DoEvents
Loop
End Sub
Function ...
