Questions tagged [vba]
Visual Basic for Applications (VBA) is an event-driven, object-oriented programming language for writing macros, used for the entire Office suite as well as other applications. VBA is not equivalent to VB.NET, or VBS; if you're working in Visual Studio use [vb.net]. If your question is specifically about programming any MS Office application, also use the appropriate tag: [excel], [ms-access], [ms-word], [outlook], [visio], or [ms-project].
215,143
questions
756
votes
9
answers
1.3m
views
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation?
In-cell function to return a matched pattern or replaced value in a string.
...
- 31.2k
643
votes
15
answers
401k
views
How to avoid using Select in Excel VBA
I've heard much about the understandable abhorrence of using .Select in Excel VBA, but I am unsure of how to avoid using it. I am finding that my code would be more re-usable if I were able to use ...
- 7,194
586
votes
25
answers
1.7m
views
Is there a way to crack the password on an Excel VBA Project?
I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords.
Is there a way of removing ...
- 9,519
348
votes
3
answers
1.1m
views
How to return a result from a VBA function
How do I return a result from a function?
For example:
Public Function test() As Integer
return 1
End Function
This gives a compile error.
How do I make this function return an integer?
- 60.1k
343
votes
8
answers
763k
views
How to comment and uncomment blocks of code in the Office VBA Editor
In the VBA editor of Office (ALT + F11), how do you comment or uncomment a block of code?
- 11.9k
333
votes
2
answers
513k
views
291
votes
11
answers
340k
views
Does VBA have Dictionary Structure?
Does VBA have dictionary structure? Like key<>value array?
fessGUID
274
votes
6
answers
1.0m
views
Check if a string contains another string
I want to find if a string contains a ","(comma) in it. Do we have any other option other than reading char-by-char?
- 3,228
274
votes
7
answers
802k
views
Loop through files in a folder using VBA?
I would like to loop through the files of a directory using vba in Excel 2010.
In the loop, I will need:
the filename, and
the date at which the file was formatted.
I have coded the following ...
- 8,653
238
votes
7
answers
307k
views
What is the difference between .text, .value, and .value2?
What is the difference between .text, .value, and .value2? Such as when should target.text, target.value, and target.value2 be used?
- 2,759
223
votes
9
answers
192k
views
Difference between Visual Basic 6.0 and VBA
What is the difference between the two. I always thought VBA is somewhat 'crippled' version of VB, but when a friend asked me the other day I had no idea what the actual differences are.
Also, when ...
- 6,527
211
votes
4
answers
955k
views
Excel VBA - exit for loop
I would like to exit my for loop when a condition inside is met. How could I exit my for loop when the if condition has been met? I think some kind of exit at the end of my if statement, but don't ...
- 10.1k
202
votes
14
answers
197k
views
Find last used cell in Excel VBA
When I want to find the last used cell value, I use:
Dim LastRow As Long
LastRow = Range("E4:E48").End(xlDown).Row
Debug.Print LastRow
I'm getting the wrong output when I put a single ...
- 4,045
200
votes
5
answers
218k
views
Can I simultaneously declare and assign a variable in VBA?
Can I convert the following declaration and assignment into one line:
Dim clientToTest As String
clientToTest = clientsToTest(i)
or
Dim clientString As Variant
clientString = Split(clientToTest)
- 6,852
188
votes
11
answers
1.1m
views
How Do I Convert an Integer to a String in Excel VBA?
How do I convert the integer value "45" into the string value "45" in Excel VBA?
- 13.9k
184
votes
28
answers
529k
views
Function to convert column number to letter?
Does anyone have an Excel VBA function which can return the column letter(s) from a number?
For example, entering 100 should return CV.
- 16.9k
181
votes
13
answers
176k
views
Excel VBA App stops spontaneously with message "Code execution has been halted"
From what I can see on the web, this is a fairly common complaint, but answers seem to be rarer. The problem is this:
We have a number of Excel VBA apps which work perfectly on a number of users' ...
- 2,154
181
votes
5
answers
956k
views
How to get the path of current worksheet in VBA?
I wrote a macro as an add-in, and I need to get the path of the current worksheet on which it is being executed. How do I do this? How do I get the file path (just the directory)?
- 59.3k
169
votes
7
answers
222k
views
What does the keyword Set actually do in VBA? [duplicate]
Hopefully an easy question, but I'd quite like a technical answer to this!
What's the difference between:
i = 4
and
Set i = 4
in VBA? I know that the latter will throw an error, but I don't fully ...
- 6,358
168
votes
6
answers
376k
views
How can I send an HTTP POST request to a server from Excel using VBA?
What VBA code is required to perform an HTTP POST from an Excel spreadsheet?
- 31.2k
167
votes
8
answers
865k
views
Declare and Initialize String Array in VBA
This should work according to another stack overflow post but its not:
Dim arrWsNames As String() = {"Value1", "Value2"}
Can anyone let me know what is wrong?
- 5,472
160
votes
2
answers
68k
views
When editing Microsoft Office VBA, how can I disable the popup "Compile error" messages?
When you're editing a Microsoft Office VBA macro or function, you will often move your cursor from a line that you haven't finished. For example, to go copy something you want to paste into that line....
- 2,673
159
votes
9
answers
1.2m
views
How do I declare a global variable in VBA?
I wrote the following code:
Function find_results_idle()
Public iRaw As Integer
Public iColumn As Integer
iRaw = 1
iColumn = 1
And I get the error message:
"invalid attribute in ...
- 2,413
158
votes
5
answers
636k
views
How do I put double quotes in a string in vba?
I want to insert an if statement in a cell through vba which includes double quotes.
Here is my code:
Worksheets("Sheet1").Range("A1").Value = "=IF(Sheet1!B1=0,"",Sheet1!B1)"
Due to double quotes I ...
- 4,926
153
votes
24
answers
733k
views
Test or check if sheet exists
Dim wkbkdestination As Workbook
Dim destsheet As Worksheet
For Each ThisWorkSheet In wkbkorigin.Worksheets
'this throws subscript out of range if there is not a sheet in the destination
'...
- 1,541
144
votes
10
answers
164k
views
Parsing an ISO8601 date/time (including TimeZone) in Excel
I need to parse an ISO8601 date/time format with an included timezone (from an external source) in Excel/VBA, to a normal Excel Date. As far as I can tell, Excel XP (which is what we're using) doesn't ...
- 10.1k
143
votes
6
answers
658k
views
How to create and write to a txt file using VBA
I have a file which is manually added or modified based on the inputs. Since most of the contents are repetitive in that file, only the hex values are changing, I want to make it a tool generated file....
- 1,617
136
votes
9
answers
586k
views
Deleting a file in VBA
Using VBA, how can I:
test whether a file exists, and if so,
delete it?
133
votes
4
answers
534k
views
Loop through each row of a range in Excel
This is one of those things that I'm sure there's a built-in function for (and I may well have been told it in the past), but I'm scratching my head to remember it.
How do I loop through each row of ...
- 5,839
133
votes
4
answers
263k
views
Display milliseconds in Excel
I am trying to display milliseconds in an Excel macro. I have a column of integers which are timestamps in milliseconds (e.g. 28095200 is 7:48:15.200 am), and I want to make a new column next to it ...
- 2,636
130
votes
9
answers
410k
views
How to suppress Update Links warning?
I'm trying to write a script that opens many Excel files. I keep getting the prompt:
This workbook contains links to other data sources.
I want to keep this message from appearing, so that my ...
- 9,448
128
votes
13
answers
61k
views
Is VB really case insensitive?
I'm not trying to start an argument here, but for whatever reason, it's typically stated that Visual Basic is case insensitive and C languages aren't (and somehow that is a good thing).
But here's my ...
- 29.1k
126
votes
4
answers
464k
views
Break out of a While...Wend loop
I am using a While...Wend loop of VBA.
Dim count as Integer
While True
count=count+1
If count = 10 Then
''What should be the statement to break the While...Wend loop?
''...
- 4,822
126
votes
5
answers
408k
views
How do I use FileSystemObject in VBA?
Is there something that I need to reference? How do I use this:
Dim fso As New FileSystemObject
Dim fld As Folder
Dim ts As TextStream
I am getting an error because it does not recognize these ...
- 59.3k
121
votes
7
answers
435k
views
VBA - how to conditionally skip a for loop iteration
I have a for loop over an array. What I want to do is test for a certain condition in the loop and skip to the next iteration if true:
For i = LBound(Schedule, 1) To UBound(Schedule, 1)
If (...
- 38.8k
120
votes
4
answers
272k
views
What is the difference between "Form Controls" and "ActiveX Control" in Excel 2010?
Using Microsoft Excel 2010, I noticed two kind of controls that can be inserted into a document: Form Controls and ActiveX Controls.
What is the difference between them?
- 29k
118
votes
11
answers
256k
views
Are ActiveX Controls Disabled?
I have Excel worksheets that use ActiveX checkboxes to control certain activity.
Today they started to give errors. I was alerted to this by a colleague who was running a later version of Excel, but ...
- 1,303
117
votes
16
answers
949k
views
How to pause for specific amount of time? (Excel/VBA)
I have an Excel worksheet that has the following macro. I'd like to loop it every second but danged if I can find the function to do that. Isn't it possible?
Sub Macro1()
'
' Macro1 Macro
'
Do
...
- 52.7k
115
votes
8
answers
282k
views
How to add a custom Ribbon tab using VBA?
I am looking for a way to add a custom tab in the Excel ribbon which would carry a few buttons. I chanced on some resources addressing it via Google but all look dodgy and outrageously complicated.
...
- 17.1k
108
votes
8
answers
141k
views
Pass arguments to Constructor in VBA
How can you construct objects passing arguments directly to your own classes?
Something like this:
Dim this_employee as Employee
Set this_employee = new Employee(name:="Johnny", age:=69)
Not being ...
- 15k
107
votes
9
answers
168k
views
How do you test running time of VBA code?
Is there code in VBA I can wrap a function with that will let me know the time it took to run, so that I can compare the different running times of functions?
- 22.8k
107
votes
4
answers
266k
views
How to add a reference programmatically using VBA
I've written a program that runs and messages Skype with information when if finishes. I need to add a reference for Skype4COM.dll in order to send a message through Skype. We have a dozen or so ...
- 1,974
106
votes
4
answers
429k
views
What is the equivalent of "!=" in Excel VBA?
The problem is that != does not work as a function in excel vba.
I want to be able to use
If strTest != "" Then instead of If strTest = "" Then
Is there another approach to do this besides !=?
My ...
- 13.9k
106
votes
3
answers
188k
views
VBA check if object is set
I have a global variable that is an instance of my custom class.
How do I check if the object is set or if I need to initialize it?
- 8,602
104
votes
7
answers
218k
views
What is the difference between dim and set in vba
Pardon me as am a newbie in VBA.
Sometimes I use
Dim r as Range
r = Range("A1")
Other times I use
Set r = Range("A1")
What is the difference? And when should I use what?
103
votes
14
answers
371k
views
VBA array sort function?
I'm looking for a decent sort implementation for arrays in VBA. A Quicksort would be preferred. Or any other sort algorithm other than bubble or merge would suffice.
Please note that this is to work ...
- 5,678
100
votes
4
answers
148k
views
Hash Table/Associative Array in VBA
I can't seem to find the documentation explaining how to create a hash table or associative array in VBA. Is it even possible?
Can you link to an article or better yet post the code?
- 4,699
99
votes
5
answers
553k
views
automatically execute an Excel macro on a cell change
How can I automatically execute an Excel macro each time a value in a particular cell changes?
Right now, my working code is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(...
- 37.8k
98
votes
3
answers
198k
views
Test if string begins with a string?
In VBA, what's the most straight forward way to test if a string begins with a substring? Java has startsWith. Is there a VBA equivalent?
- 4,134
95
votes
11
answers
514k
views
"Can't find Project or Library" for standard VBA functions
So I'm having to run someone else's excel app on my PC, and I'm getting "Can't find Project or Library" on standard functions such as date, format, hex, mid, etc.
Some research indicates that if I ...
- 93k