Excel-VBA (Visual Basic for Applications for Microsoft Excel) is the dominant programming language for Microsoft Office Excel. It is an event-driven and object-oriented programming language for writing macros for Microsoft Office applications like Excel. DO NOT USE THIS TAG for VB.NET questions.
0
votes
0answers
16 views
Macro working but Add-in shows 'Subscript out of range'
I have a huge code in 3 modules, which is working absolutely fine.
But when I'm trying to run this macro after saving as MS Add-In, it shows :
Subscript Out of Range
The basic work of my code is ...
0
votes
1answer
11 views
save the results of a macro without opening the file
I have a macro in excel which refreshs some data from external sources. I want to create a code which basically run this macro without opening the excel file every day and save the result.
I have ...
1
vote
1answer
34 views
Set “Move and Size With Cells” for checkbox created in VBA
I've created a spreadsheet which has a lot of checkboxes in it within columns. Occasionally I am finished with a column, and wish to hide it from view.
However if I hide the column, it does not hide ...
0
votes
0answers
12 views
how to get data from password protected and closed workbook with in excel 2007
I use an ADO on Excel 2007 to get data from closed workbook, the code (regardless of file and sheet names) is:
Sub TransferData()
Dim sourceFile As Variant
Application.ScreenUpdating = False
...
5
votes
3answers
3k views
Excel-Access ADO Update Values
I am trying to update a table in Access from the values in excel, however every time i run the code it creates new rows instead of updating the already existing ones, any ideas why? I am new to ADO, ...
0
votes
0answers
19 views
Using Userform for determine pivot fields in VBA
I would like to use userform for creating pivot table. I already have code for pivot and my purpose whit the user form is to determine the pivotfields. Such as the number of Row-, Column-, Pagefields ...
0
votes
2answers
17 views
How to merge the results of a number of cells in excel, so that a yes in any one of the input cells will evaluate to yes in a single output cell?
I have a spreadsheet that looks something like this:
Col1 Col2 Col3 Col4
yes no no no
no yes no no
no no no no
no no no no
How can ...
1
vote
4answers
36 views
updating summary sheet with values from multiple sheets in difference cells
I am quite new to VBA, I have attempted to write a code below, but I'm getting a type mismatch error (Highlighted below). What I am trying to achieve is the following:
I have a list of properties on ...
1
vote
0answers
10 views
Is it possible to change the format of the numbers in the msoElementDataTableWithLegendKeys?
Is it possible to change the format of the numbers in the msoElementDataTableWithLegendKeys?
That's the legend that appears under the graphs, with numbers.
But I can not change the format. I just ...
-1
votes
0answers
8 views
Importing .bas file in to excel
Application.VBE.ActiveVBProject.VBComponents.Import filename
This line throws error
Run time error 76 Path Not Found
Can anyone help me resolve this...
1
vote
2answers
26 views
Wrong Result after Yes/No Button
I'm currently adding a relatively simple user decision Box to my excel file, but there seems to be some issue that I can't find.
The basic idea is that if a user Clicks Yes everything is ok and a ...
-1
votes
0answers
13 views
Custom ribbon grays out quick access toolbar
I used Ron de Bruin's website and examples to create a custom ribbon. That worked great but now my quick access toolbar (QAT) is grayed out.
Is there a way to keep the default QAT while using a ...
0
votes
1answer
20 views
Adding a column to a named range based on a cell value
I'm trying to create a macro which will be adding a column to a named range provided on the value in a column next to a named range.
To be more specific, the range B:G is named "Furniture". ...
-1
votes
2answers
16 views
Option Button User for m to chnage Column Font Colour
I have two Option Buttons in a user form and I need to do the following.
If Option Button 1 is clicked, then make the ENTIRE column F in white font (the text only)
If Option Button 2 is clicked, ...
-1
votes
0answers
17 views
Excel charts block merging qurey
I have made this chart but I need to place the block 1.58 into 9.00 value block.
What is the quick and easy way to do that?
Note: I don’t want any other value to be changed.
Inshort How to merge two ...
0
votes
1answer
43 views
Finding punctuation within VBA string from the right side
In VBA, how do I find the first instance of a punctuation symbol, from the right hand side? For example, from "!", I should be able to get the term "Security" two times in the following string:
INDEX(...
0
votes
1answer
33 views
dynamic reference in VBA Index function
I would like to dynamically apply below INDEX function for two ranges where one range should be dependent on an iterator i.
Could anyone help how to write that down instead of my code example (only ...
10
votes
14answers
96k views
Excel VBA Compile throws a “User-defined type not defined” error but does not goto offending line of code
Symptoms
This is a symptom specifically when compiling an Excel VBA project. The following error occurs:
User-defined type not defined
However, the code that produces this error is not highlighted ...
0
votes
2answers
18 views
Changing the value of an integer according to the result of an if statment
I have this function:
Public Price As Integer
Public Function MergeSize(r As Range) As Long
MergeSize = r(1).MergeArea.Cells.Count
If MergeSize <= 10 Then
Price = 70
MergeSize = ...
0
votes
2answers
25 views
How can I write this iferror function in VBA?
I'm trying to clean my data in a column and this is a function i wrote that can be applied to the whole column A.
Can anyone help me how to write a macro for this?
-3
votes
0answers
38 views
Excel VBA start date
Need to check the start Date, if the start date column is continuous as example in image 24/01/2017,25/01/2017,26/01/2017, then pick the first date and last date and paste as shown in image.
If the ...
0
votes
0answers
20 views
Excel vba userform not working if there is another excel file is opened
I have an userform in a.xlsm and it is working fine.
But if I open another excel workbook for example b.xlsx, my userform not working.
My userform try to find or select in b.xlsx.
how can I ...
-1
votes
1answer
27 views
VBA-Excel: Read Data from XML File
I have following XML data which is coming from REST call ,
<?xml version="1.0" encoding="UTF-8"?>
<response>
<result>
<cs>1</cs> ...
0
votes
2answers
67 views
Why is there an invalid reference in a recorded Pivot creating macro (VBA)?
I created this code with macro recorder to get Pivot table automatically.
But when I run this code again an error message appears:
Run-time error 1004: Invalid reference
at this line Workbooks("...
4
votes
4answers
9k views
Whats the best way to display a message box with a timeout value from VBA?
This question is regarding the best workaround available from those listed below, or perhaps another one that you know of.
This is the question's background it comes from code like this...
Set ...
0
votes
1answer
22 views
Filter and Fill visible cells with formula VBA
I was wondering if there is a way of going through a filter list. for each filtered list I will perform a formula. i.e
Company Name Invoice Number Voucher Number
CompanyA ...
2
votes
0answers
15 views
Export data to Excel Macro-enabled through Open XML
I have an Excel Sheet which I am using as a template file for exporting data.
The Excel Sheet is XLSM file and has few codes written in it in VBA.
Each time the file is copied and renamed with ...
5
votes
6answers
20k views
Decimal to binary conversion for large numbers in Excel
I have some large number in an excel sheet and i want to convert them to binary eg
12345678
965321458
-12457896
Could someone help me how to do this.
Thanks.
0
votes
0answers
15 views
VBA script freeze
'initializing ready state'
Enum READYSTATE
READYSTATE_UNINITIALIZED = 0
READYSTATE_LOADING = 1
READYSTATE_LOADED = 2
READYSTATE_INTERACTIVE = 3
...
0
votes
2answers
14 views
VBA - USERFORM - find value and fill row without activete or select
Is possible to do something like this without select sheet, or activate ? I need change values in cells with userform based on find key value.
Dim sonsat As Long
Sheets("DATA").Range("A:A").Find(...
0
votes
1answer
59 views
Download data from hyperlinks into creating new folders using vba
Image of data in excelI am downloading some data from net using hyperlinks and to put downloaded data into folders created with names listed in A column.
Right now data is successfully downloaded ...
0
votes
1answer
17 views
VBA Code to Actively Find Range for Excel Graph
I'm currently building a VBA code to actively define the range for a graph.
Sub ABC()
Dim count As Integer
Dim countAll As Integer
Dim i As Integer
Dim j As Integer
Dim filter As Variant
Dim ...
0
votes
1answer
22 views
Excel VBA return uppercase section of string
I am trying to create a function that will return an uppercase section from a longer mixed case string.
I have addresses that are entered as a string with the Suburb as uppercase and want to extract ...
0
votes
0answers
13 views
search replace multiple words in word doc with excel data
I am trying to populate a word document with data from an excel spreadsheet. However when I run the code below, "sitename" is replaced with with data from cell "B2", "B3" and a number of other cells ...
-1
votes
0answers
22 views
exporting data of url with required criteria's [on hold]
Respected experts, I want to export one data from our URL with different filter and criteria with the help of VBA. My current steps is to do it manually is like this---> http://myurl/123 --> login ...
0
votes
1answer
29 views
Excel VBA run time error when working with multiple workbooks
I have the following code
Sub ImportSolarData()
Dim Year As Integer
Dim DataBookName As Variant
Dim SourceSheet As Worksheet
Dim SourceBook As Workbook
Dim SourceSheetName As String
Dim TargetBook ...
0
votes
1answer
22 views
activeX combo boxes
I have a complex workbook that has a number of ActiveX Combo boxes which I choose over form controls for their customization options. Inexplicably, ALL of the boxes stopped working this morning. ...
0
votes
0answers
37 views
How To Use Excel VBA to Modify Access Database (accdb) Table that's synced with Sharepoint?
Hopefully that title was precise enough. Since I cannot directly update a sharepoint list from excel using VBA I'm trying to do some Insert/Delete/Update commands in an Access Database (accdb) that's ...
0
votes
1answer
41 views
Oracle import from excel [closed]
I have a project where I have to send data from an Excel spreadsheet to an Oracle database.
Here is the code I am using but it is not working, I am getting errors in the strSQL statements at the ...
0
votes
1answer
27 views
How to copy a picture from one comment to another?
So I have a comment on a cell that conatains a picture setted with
ActiveCell.Comment.Shape.UserPi�?ture "C:\path\img.png"
But I want it to be copied to another cell. How do I do that?
0
votes
0answers
19 views
Microsoft Visual Basic for Applications, clearSCADA, SCADA
Developing an interface in excel to access a database in a SCADA application (clearSCADA by Schneider)
Require the ability to list all properties of an object in Visual Basic behind excel.
It is ...
0
votes
1answer
22 views
How to use a user written function in VBA language macro editor
i want to ask something about using a user made function in Excel sheet. For example, if someone write x^2+2 in the worksheet. How to use that function in the macro, so that I can use it (for example: ...
1
vote
1answer
30 views
Make looping dynamic for more than 1000 data
The function code I have developed below is working fine, and the formula I am using is =adj(cell1,cell2).
Now I would like to make it a dynamic function for any j value so that I can use it for any ...
0
votes
0answers
14 views
Trying to set font for Excel Workbook through VSTO Add-In - workbook.cells issue
I'm making an Excel Add-In using C# and Visual Studio. I want to get the range of the current workbook, and set the font through the method set already. The method is currently setup to work on a ...
3
votes
1answer
22k views
Pasting an Excel range into an email as a picture
I'm creating an Outlook email from Excel (Office 2013). I want to paste a range of cells (C3:S52) into the email as a picture.
Below is the code I have so far. Where am I going wrong?
Sub ...
0
votes
0answers
35 views
Excel - extracting values from data table for list validation
Background:
I have a workbook/tool that performs exactly as I wish.
The tool includes two main components:
A list of ingredients grouped by category (i.e., dairy, produce, etc.)
a sheet with ...
0
votes
0answers
13 views
What is wrong with this VBA Code to make Pivot Table using VBA
This code is suppose to automatically make pivot table in different sheet and with each element of pivot filter field as filter. The error is happening when I am trying to make pivot table from the ...
-1
votes
0answers
1k views
Determine addresses, copy range from Excel file to mail body, then attach Excl file
Software Version: Excel 2010 and Outlook 2010
I would like to send email to a predetermined list of email addresses. Send or copy to (column A, B, with Yes = send to, NO = do not send) and Email ...
0
votes
3answers
48 views
VBA Color coding the entire column according to drop down list value
I have the code in Sheet 1 VBA window. The excel sheet 1 in the workbook is with the drop down list in Column C. The 4 options of the drop down list are: Complete, Pending, Missed Deadline, and ...
-1
votes
0answers
24 views
VBA to extract Tables from Word to Excel - Source date format not restored
I am using below VBA script(Found on Stack Overflow) to extract all the tables from a Word file into a Excel. It works like a charm. It taken input from which table I need to copy from Word file to ...
