class string array property cannot be read

I'm using Excel 2013.

I have a class called clsFund. It has one property of type string() called CompanyNames.

 private pCoName() as string

 Public Property Get CompanyNames() As String()
    CompanyNames = pCoName
 End Property

In a standard module I try to retrieve this string array but without any luck. I have the code below. The funny thing is the ubound tells me the correct answer of 10 but it doesn't like the line below clsData.PnL, the method is just expecting a string argument which I belive I have supplied however I get a compile error: wrong number of arguments or invalid property assignment - I do not understand?

 Private Sub PrintCompanyNameAndPnL()

 Dim i As Integer

 For i = 1 To UBound(Fund.BloombergIndices)
     Range("A" & i) = clsData.PnL(Fund.CompanyNames(i))                    
 Next i

End Sub

Best How To :

If you write it that way, you are passing i as a parameter to the CompanyNames property, which doesn't accept any arguments.

You need to access the array items like this:

Range("A" & i) = clsData.PnL(Fund.CompanyNames()(i))

so you are returning the array and then passing i as an index to that.

adding variables into another variable vba

excel,vba,excel-vba

I can't see anything wrong with the code, as long as your text is in column C, and the values are in column H I've also taken the liberty of rewriting the code to make it clearer: Sub test() Dim x As Long Dim y As Long Dim TotalValue As...

Interface Controls for DoEvent in Excel

excel,vba,excel-vba,loops,doevents

How about changing your 'do until' loop to a 'for next' loop? Something like?... Sub rowinput() Dim lngInputStartRow As Long Dim lngInputEndRow As Long Dim row_number As Long lngInputStartRow = Range("A1").Value 'specify your input cell here lngInputEndRow = Range("A2").Value For row_number = lngInputStartRow To lngInputEndRow DoEvents Next row_number 'Then a...

Creating a Range in VBA

vba,range

if you asking about Cells multiple range then you can use this: Sub test1() Dim nStart&, nEnd& Dim Rng As Range nStart = 5: nEnd = 9 Set Rng = Range("A" & nStart) While nStart <> nEnd Set Rng = Union(Rng, Range("A" & nStart + 1)) nStart = nStart +...

Range.offset propety

excel-vba

The .Offset property is used to move a certain position from a specified location. It is used like: ActiveSheet.Cells(1, 1).Offset(Row, Column) Where positive values move the position down (by the stated amount) for the Row value, and to the right (by the stated amount) for the column value. Negative values...

Copying a Range from Excel and Pasting it into Powerpoint NOT as a metafile but as a table which you can edit in PPP

vba,excel-vba,powerpoint-vba

There appears to be no corresponding method in the PowerPoint object model. The only way to do this is to call the ribbon button itself: ActiveSheet.Range("d51:d57").Copy newPowerPoint.CommandBars.ExecuteMso("PasteExcelTableSourceFormatting") BTW: To find the list of ribbon buttons, search for "Office 2010 Control IDs"....

Converting ADODB Loop into DAO

excel,vba,ms-access,ado,dao

DAO might be a little faster, but not materially. Instead, use an IN clause in your SQL Statement so you only have to do it once. Sub test() Dim vaIds As Variant Dim sSql As String vaIds = Split("1 2 4 7 200 205 654", Space(1)) sSql = "SELECT [Sales]...

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

excel,vba,excel-vba

If cell.Value2 = "FOUND THE CELL" Then cell.Offset(0, 1).Value2 = "changed the next right side cell" cell.Offset(0, 2).Value2 = "changed the second right side cell" End If ...

Userform is not unloading once command button is pressed

vba,excel-vba

There are a couple problems with the code you posted. After the If ComboBox1 = "ROW" Then ... Else block of code you've got an End Sub but no End If. You definitely need to add the End If and I suspect you should remove the End Sub. You've got...

Excel VBA Program Code by Using Randomize Timer

vba

Your second ElseIf statement can never become true. First you check if num1 is bigger or equal than 50: If num1 >= 50 Then grade = "B" Cells(1, 2).Value = grade Imagine if num1 equals 49, then the next ElseIf will get executed. This checks if num1 is smaller or...

Copying sheet to last row of a sheet from another workbook

excel,vba,excel-vba

This is what I mentioned in my comment Note: in future, you can using for loop to go through the column index. Option Explicit Dim WB1 As Workbook Dim ws1 As Worksheet Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long Dim i As Integer...

excel search engine using vba and filters?

excel,vba

In order to filter for "any" column, you could combine a Find result and Filter like this: Sub DateFilter() Dim nRow As Range Dim toSearch As Range 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 Set toSearch = Range("A1:C4") 'detect row that...

VBA how to initialize vCPath

vba,excel-vba,excel-2010

In your original code you've got this block: ' Open the file dialog With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show ' Display paths of each file selected For lngCount = 1 To .SelectedItems.Count Next lngCount For Each strFilename In .SelectedItems MsgBox strFilename Next End With Which already does what you want....

Replace reference with its value in Excel VBA workbook

vba,excel-vba

Re-assign a cell's value to itself in VBA to overwrite the formula/link with the actual value. If NameExists(newSheet, "DelAddress") Then With newSheet.Range("DelAddress") .Value = .Value End With End If ...

I need help setting the RecordSource of a Report within a VBA Function

vba,ms-access,access-vba

You need to shuffle the order: Function PrintMod() Dim Source As String Select Case Forms![Search Form]!Subform1.SourceObject Case "Query.SearchQuery" Source = "SELECT * FROM SearchQuery" Case "Query.Part Number Query" Source = "SELECT * FROM [Part Number Query]" Case "Query.Keyword Query" Source = "SELECT * FROM [Keyword Query]" Case "Query.ROP Query" Source...

Using a cell's number to insert that many rows (with that row's data)

excel,excel-vba

This will do what you want, it polls through from the bottom up, if it encounters a number in C and it is > 1 then it will insert the number of rows equal to column C number - 1 then copy the data from the host row. This will...

Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

excel,vba,excel-vba,user-defined-functions

You need to use Application.Caller. This will return the value in cell A1 of the sheet the function is entered to: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Cells(1, 1) End Function This will return the name of the calling sheet: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Name End...

VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010

You have: Dim RangeNOut as Double Dim RangeNOut as Integer While the IF statements in there are a nice idea, VBA will not allow you to do that. It doesn't do conditional 'compilation' since it isn't a compiled language. When VBA runs your code, all your variables are declared (no...

VBA - Unable to pass value from Private to Public Sub

excel,vba,excel-vba

In your calling code: ThinksCommerciallyInt = 1 should be ThinksCommerciallyInt := 1 similarly for the other parameters...

Removing Alert When Using DeleteFile API

vb.net,vba,api,delete

There are several SHFILEOPSTRUCT.fFlags options you'll want to consider. You are asking for FOF_NOCONFIRMATION, &H10. You probably want some more, like FOF_ALLOWUNDO, FOF_SILENT, FOF_NOERRORUI, it isn't clear from the question. Check the docs.

Using a stored integer as a cell reference

excel,excel-vba,reference

You have to find a suitable formula for entering in the target cell. Then you would build such formula with string concatenation, etc., for entering it via VBA. One option for the formula is to use OFFSET, as in =SUM(OFFSET($A$1,D3-1,COLUMN()-1):OFFSET($A$1,ROW()-3-1,COLUMN()-1)) This sums all values from Cell1 to Cell2, in the...

Excel VBA 2013 Print Image

image,excel-vba,printing

One option is to call Windows Print dialog via shell command. Example: (Full code) Option Explicit Private Declare Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _ ByVal hwnd As Long, _ ByVal lpOperation As String, _ ByVal lpFile As String, _ ByVal lpParameters As String, _ ByVal lpDirectory As String,...

ReplaceLine method in VBE only replacing part of line

excel-vba,vbe

When I run with the debugger, myLine changes value between the two calls. The DimAll becomes Dim on the second time through. This is because you are replacing the value of codeLine once you enter the main If conditional inside the ExpandDim Function. Create a new variable in that function...

Excel VBA Loop Delete row does not start with something

excel,excel-vba

It's because your moving forward through the rows - if you delete row 4 then row 5 becomes row 4 and the code will jump to the new row 5 - which is in fact row 6. Hope that made sense. :) The solution will be to use a For...

Vba changing directory to save

excel-vba

That's because you need to tell Excel if what you say to it should be read as a variable or as string of text. Using "" says it is string of text and should not be evaluated. Use this: Filename:="C:\Users\ee31264\Desktop\Mensile Automat\" & name_month & "\send\TESO1.xlsx" Also remember that in newer...

Excel-VBA: create named Range in row until end of cell content

vba,excel-vba,range

Sure you can use this snippet to find the last filled cell in a column and use that row number to set your range.name - just replace the "A" with whatever column you'd like. Sub test() Dim lastrow As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Range("A2:A" & lastrow).Name = "RangeColA" End...

Exit Sub And Call another Sub

vba,excel-vba,call

but you're so close! sub1 . . If x=y Then Call sub2 Exit Sub End If . . End Sub ...

Extract All Named Ranges Into A Class

vba,excel-vba

Get Named Range by String Why not a simple procedure like so: Function GetNR(namedRange as String) as Range Set GetNR = ActiveWorkbook.Names(namedRange).RefersToRange End Function Then simply get the named range like so: Sub Example() Debug.Print GetNR("NAME").Value End Sub Named Range Suggestion in VBA Project Alternatively if you want the names...

If cell value starts with a specific set of numbers, replace data

excel,vba,excel-vba

Use the LEFT() function, as shown below: lastRow = Range("A" & Rows.Count).End(xlUp).Row colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0) For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum)) If LEFT(c.Value,3) = "614" _ Or LEFT(c.Value,3) = "626" _ Or LEFT(c.Value,3) = "618" _ Or LEFT(c.Value,3) = "609" _ Or LEFT(c.Value,3) = "605" Then...

VBA for duplicate rows

excel-vba,duplicates

Try this code: . Option Explicit Public Sub showDuplicateRows() Const SHEET_NAME As String = "Sheet1" Const LAST_COL As Long = 3 ' <<<<<<<<<<<<<<<<<< Update last column Const FIRST_ROW As Long = 2 Const FIRST_COL As Long = 1 Const DUPE As String = "Duplicate" Const CASE_SENSITIVE As Byte = 1...

How do you delete favorite folders in outlook using VBA

vba,outlook-2007

You have to use the Remove method of the NavigationFolders collection. It takes a NavigationFolder as the argument. There is no Delete method. Sub RemoveAllFavorites() Dim favGroup As NavigationGroup Dim favFldrs As NavigationFolders Set favGroup = Application.ActiveExplorer.NavigationPane.Modules.GetNavigationModule(olModuleMail).NavigationGroups.GetDefaultNavigationGroup(olFavoriteFoldersGroup) Set favFldrs = favGroup.NavigationFolders Do While favFldrs.Count > 0 favFldrs.Remove favFldrs.Item(1) Loop End...

Activecell not in Array

vba

You have fallen victim to the odd behavior of WorksheetFunction.Match when it cannot find a match. Instead of returning the error, it throws a run time error which gums up the works. Since the premise of this question is searching for whether or not something is in a list, you...

How do I do to count rows in a sheets with filters? With a suppress lines

excel,vba,filter

Try the following which uses the SpecialCells() method to select only cells that are currently visible on screen (i.e. not filtered out). count = Application.WorksheetFunction.CountA(Range("A:A").SpecialCells(xlCellTypeVisible)) ...

Using VLOOKUP formula or other function to compare two columns

mysql,excel,vba,date

If data in your first table starts at A2, and your other column starts at D2, then use in E2 =VLOOKUP(D2,$A$2:$B$17,2,0) Copy down as needed....

Using date in CreateQueryDef

vba,date,ms-access

Dates in Access needs to be surrounded by the # tags so that it recognizes the date you have passed. The other important factor to consider is that JET requires the date format to be mm/dd/yyyy as opposed to the normal dd/mm/yyyy. So your problem is because you are using...

VBA data type to store Range().Characters()

vba

You need to set the variable and the index is 1 based not 0 Dim chars As Characters Set chars = Range("A2").Characters(1, 4) chars.Font.Color = vbRed ...