Tagged Questions
1
vote
2answers
34 views
Add or Delete Excel Sheets Based On Array Values
I am working on a piece of code that creates an array and populates it based on the contents of a column in an Excel Sheet. I would then like to use this array to add or delete Excel Sheets.
...
0
votes
1answer
12 views
VBA Excel - Returning an array of ListBoxes from a function
I am currently working on a module that is meant to populate three ActiveX ListBoxes. Each will be populated with the same values: choices for axes titles on a graph (X-axis, Primary Y-Axis and ...
3
votes
2answers
4k views
Dynamically appending an array in VBA
I want to append an array with a number depending on the condition of various variables. Here is the code I've come up with: I begin with an empty array.
Sub makeArr()
Dim myArr() As Integer
If box1 ...
0
votes
2answers
46 views
Identifying different dynamic array VBA Excel
Sorry for not been enough clear in my previous message!
Here is the situation
A user have the possibility to add new row in an Excel Array.
I would like then to stock the new parameter in the last ...
0
votes
0answers
28 views
Plotting multiple named arrays as single series
I am trying to create a radial grid on a dynamic plot for viewing polar data. I can create this using six circles and 12 equally spaced radial lines that extend from the inner circle to the outermost ...
2
votes
2answers
43 views
Expose an array in MS Access - equivalent of var_dump()
I have an array populated in VBA, and dont'cha know, it's really an array of arrays. So I want the equivalent of PHP's var_dump() function. Such a beautiful tool (and here, right now, I am ...
1
vote
2answers
29 views
Array not populating from For Loop VBA
I'm trying to create an array of cell entries (e.g. A6,B6, etc) that populates in a for loop.
However the array
MyArray
is always empty and I can't figure out why its not being populated ...
0
votes
1answer
22 views
VBA passing an array from a 2D array to a sub
I have a function which collects which months are ticked in a user form, containing checkboxes:
Function get_entries() As Boolean()
This returns a 2D boolean array(4, 11) representing 5 x 12 check ...
0
votes
1answer
24 views
Selecting a random string from a delimited text file
I'm trying to input all the parsed strings into a 2D array where the first 5 strings make up one column and then the next 5 make up the second column ect. Then I need to randomly select 3 of the ...
0
votes
2answers
61 views
Array subscripts in VBA- Can anyone explain this?
Please find attached a screenshot of a small test program that I wrote to illustrate a confusion I am having. The text in <> is inputted by me to explain the error I am getting.
The MsgBox was ...
14
votes
5answers
75k views
Populating VBA dynamic arrays
The following code gives me error 9 "subscript out of range". I meant to declare a dynamic array so that the dimension changes as I add elements to it. Do I have to create a "spot" on the array before ...
1
vote
2answers
22 views
Array of variables (not variant )
I'm struggling with arrays.
I have some double variables that need to be covert to in or mm according to an RB button. I don't have problems with these, but the amount of variables its quite high, I ...
0
votes
0answers
29 views
VBA macro to convert small, index, excel formula
I have written a formula that I need help converting to a VBA macro. The formula is as follows:
{=IF(ISERROR(INDEX('Application Database'!$D$4:$E$60,SMALL(IF('Application ...
0
votes
1answer
16 views
VBA Calculation Loop to Convergence
I am trying to create a loop in VBA considering two ranges or columns of data. It will look at the value in one range (Lets call Column A) and if that value is greater than 1 it will then increment ...
1
vote
1answer
59 views
VBA Array - addressing to it by its name
this is a general VBA Array issue, it is not for MS Office apps (no tables involved).
I'm looking to find out how to create multiple one-dimension arrays at runtime (maybe even public ones), using ...
0
votes
1answer
29 views
In VBA, how to split a string into an array, then pass it as an argument to a Sub or Function
MY QUESTION:
Is there a way to split a string into an array, then pass that as an argument to a Sub or Function that is expecting an array?
THE DETAILS:
I have two simple VBA Subs below. Sub ...
0
votes
1answer
32 views
Range and arrays in VBA converting whenever they want. What's going on?
The Range object in VBA is very strange.
Here's my practice code:
a = ActiveSheet.Range("A1:A3").Columns.Count
b = ActiveSheet.Range("A1:A3")
c = b.Columns.Count
a gives me 1, no problem.
b gives ...
2
votes
1answer
35 views
VB: Type Mismatch Error, Array Dimensions 1x1
I am trying to use a Variant datatype to store an array of strings. This is an example of the code I am using.
Sub Selecto()
Dim numRows As Integer
Dim Badger As Variant
numRows = ...
0
votes
1answer
27 views
Grab Value from Cell as a Format
I am using VBA to pull in a bunch of data from several workbooks into several worksheets and then process that data later.
WorkFlow: As I pull the data into my workbook, I first place all my data into ...
0
votes
1answer
39 views
Find and Count Unique Letters in Array List VBA
I need to be able count the number of unique letters in an array such as this:
[A01, B01, C01, A02, C02]
For this example the result should be 3. Thus I need to include the value only in the case that ...
0
votes
2answers
4k views
VBA count non empty elements of array
Noob question: I want to count the non empty elements of an array?
My attempt:
Dim Arr(1 To 15) As Double
'populating some of the elements of Arr
'...
Dim nonEmptyElements As Integer, i As Integer
...
0
votes
1answer
67 views
Maximum Number of arraylist is only 99999999
Dim totalAllArr(99999999) As Integer
Public Sub SynchNow()
ListOfBooksTableAdapter.Fill(OJT_Project_LSDataSet.ListOfBooks)
For i = 0 To all - 1
totalAllArr(i) = ...
0
votes
2answers
35 views
Pass all the Range Names in an Excel Workbook to an Array
I would like to pass the existing names in a Workbook to an Array and Redim the array to UBound limit, but I cannot even get to first base of returning the names.
I am preparing a very basic Monte ...
1
vote
4answers
8k 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?
-3
votes
0answers
94 views
VBA Excel copy and paste array if value is “TEXT”
I have a sheet, something like this.
TEST function1 function2 function3 function4
TEST function1 function2 function3 function4
""
""
And need to write a code in VBA, but I'm new at it.
The ...
0
votes
1answer
48 views
Cannot Return Array Within A VBA Select Case statement?
I have a function for splitting a string by it's first space (I know there may be other ways to do this) which seems to work fine:
Public Function SplitBySide(ByVal str As String) As String()
'Left ...
1
vote
2answers
41 views
Vba, select a dynamic matrix
I have a worksheet which contains a data matrix like the sample below:
This array is dynamically generated, so..I know the coordinates of the top left (non empty) cell, I want to find the ...
0
votes
1answer
33 views
How to find out length of an array in VBA? Ubound does not give actual length.
I basically want to write logic such that if certain element is already present in array i dont want to again put it into it. My array is one dimensional. I am not able to understand how filter ...
3
votes
1answer
59 views
Excel VBA: Function to Count Non Empty Element of an Array
I am looking for a way to count non-empty element in a 2 dimensions array.
I have read a solution here VBA count non empty elements of array. However I think there may be a better way or function to ...
0
votes
3answers
63 views
VBA - Create Array from Named Range
I am ultimately trying to export certain sheets in my excel workbook as pdf's.
I have all the names of the sheets I want to export in a named range (in a column). As an example I have "Total" in A1 ...
1
vote
1answer
52 views
Efficient way to create an inverted index in VBA
I am creating an inverted index to get a dictionary of words with an associated list of the line numbers that the word appears on (starting the line numbers and a list of words that appear in a given ...
0
votes
0answers
28 views
Ideas on how to get a reference to an array in VBA
I'm coding a module that reads workshifts for a group of 100 persons. I've made a custom type called Employee that contains information about which days that person is working and the number of ...
0
votes
0answers
26 views
Working with two sheets in a loop
I have searched this site but couldn't find an answer to my question.
I need to compare the text of a cell in "Monthly Processed Data" sheet to another cell in another sheet: "INSTRUCTIONS".
I am ...
1
vote
2answers
38 views
Updating an array stored in a VBA dictionary
I'm creating a data structure that uses nested dictionaries and a list at the lowest level. Here's a sample of my data:
Country, Customer, Purchased
US, Alan, Lawnmower
US, Alan, Hammer
US, Karen, ...
0
votes
0answers
20 views
VBA Watch Window does not update upon running code
I have an array that I want to watch change as it goes through a loop, to make compare manually with the data i'm pulling from.
when I add the array to the way initially it looks fine.
However, ...
1
vote
1answer
206 views
Array formula to VBA
I have an Excel spreadsheet with two worksheets titled “Cities” and “Data”.
The "Data" page contains 108264 rows of data, and columns progress all the way up to column AT.
Under the Cities ...
0
votes
0answers
105 views
VBA Excel Checking if cell contains array members
I've made a workbook that automates the filling of reports and chart generation. On the final workbook I need to update one column, lets call it Class, depending on the wording of other column, ...
0
votes
0answers
27 views
Keep original message format in Outlook
My code works fine to gather some email bodies and send them to me.
However, when I test the code, the message that I receive does not have the same format.
Is there a way to keep the original ...
-1
votes
1answer
40 views
All Array elements into Outlook mailitem
I have an array with a unknown number of elements.
I am trying to find out how can I insert all the array elements into the body of the e-mail that I'll send.
Is there a way I can reference all ...
-1
votes
1answer
60 views
VBA Array equalled to Range
Just a quick question regarding VBA. I have this block of code
Dim colEmployees As New Collection
Dim recEmployee As New clsEmployee
Dim LastRow As Integer, myCount As Integer
Dim EmpArray As Variant
...
0
votes
1answer
25 views
vba using cells inside range
I have the following vba code:
Sheets("results").Range("C2:M6").Value = arr
where arr is 5,11 dimension.
the above formula works and paste the value of arr in the excel range specified.
but when I ...
-2
votes
1answer
75 views
excel and vba - arrays, variables, and range - changing on the fly
thank all of you that helped me! i got it going with YOUR help! **
:)
I am a 'old school' basic and vb programmer. i am trying to help a friend with an excel vba app. It concerns an array. i ...
0
votes
1answer
78 views
Type Error When Extracting Range to Array Variable in VBA
I'm trying to extract a worksheet range to be used in an array variable. However, whenever I try to run the code I get a "Run Time Error '13': Type Mismatch" message. I think that my problem is that ...
0
votes
1answer
64 views
Populating an array with named range on a specific worksheet
I am new to VBA and trying to automate populating an array using a named range on a specific worksheet. The array in question is in bold in below code. The named range is "SheetNames", on a worksheet ...
2
votes
1answer
35 views
Array of worksheet variable
As per documentation, I can define array of worksheets like below
Dim sheetsArray As Sheets
Set sheetsArray = ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2"))
But what happens if my worksheets are ...
0
votes
3answers
68 views
Delete Row from Array
I am trying to go through an array to find duplicate entries in a single column of that array and delete the entire row.
I am getting figuring out rangeStart, rangeEnd, and lastrow above this and ...
0
votes
2answers
56 views
Outlook VBA Sort by Date
I am building a code that does a search in a Outlook folder and put the body of the items together in just one item (to build a history for that case).
I am performing the search using the Find ...
0
votes
4answers
100 views
Build Array in Excel VBA
Desired result: The result would be a function that builds an array of values from column B. The values are restricted by having the same value in column A. 'E.g. Column A value = 1 myArray = (0,1,2)' ...
0
votes
2answers
32 views
Excal macro loop for each element in array
I've got a relatively big amount of data which I want to put in graphs.
Basically, what I have is multiple components (in total 30-ish but in the example below I'll limit the code to 5 components), ...
1
vote
2answers
149 views
Comparing two large lists with multiple columns (same number in each list) in excel VBA and do…more stuff
I've searched far and wide and I can't quite find anything to fit my needs.
The situation:
I have two lists of data with the same type data in each column (10 columns but the last 2 are useless), but ...
