Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Need help displaying result of custom VBA function as label

Hello,

I am trying to display the result of a custom VBA function as a label on a userform. The function is called and takes arguments from three text boxes on the userform. I attached it to the exit parameter. (This may be part of the problem; I'm not sure).

There seem to be two problems, first all the text boxes don't always contain data, but because the arguments are not optional, I get a type mismatch error when the code runs (I tried switching some of them to optional, but it didn't help as you can see from my 'commented code).

The other problem is that even when all the arguments seem to be met (i.e. the text boxes all have data), I still get a compile error that the argument is not optional. This only happens if I try and pass the result to some aspect of the userform. As you can see from the last coded line in the custom function, if I assign it to a range on the worksheet, it functions fine.

Below are the codes. Any help you can provide would be appreciated. I use excel 03 and windows XP.

Note the exit procedure also contains a code that formats the text box to a specified date format, and this does work.

Code:
Private Sub txtStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim StartDate As Date
Dim EndDate As Date

    If IsDate(Me.txtStartDate.Value) Then
        StartDate = Me.txtStartDate.Value
        Me.txtStartDate.Value = Format(StartDate, "m/d/yyyy")
    Else:  MsgBox "Please enter a date"
    End If

Call CalcDate(txtStartDate, txtYears, txtMonths, txtDays)

EndDate = CalcDate

lblProjectPeriod.Caption = EndDate

End Sub
and the custom function:

Code:
Function CalcDate(StartDate As Date, Optional Years As Variant, Optional Months As Variant, _
    Optional Days As Variant) As Date
          
    'If IsMissing(Years) Then Years = False
       ' If Years = False Then Years = 0
       ' End If
    'End If
    'If IsMissing(Months) Then Months = False
       ' If Months = False Then Years = 0
     '   End If
   ' End If
   ' If IsMissing(Days) Then Days = False
       ' If Days = False Then Years = 0
       ' End If
    'End If
        
    CalcDate = DateAdd("yyyy", Years, StartDate)
    CalcDate = DateAdd("m", Months, CalcDate)
    CalcDate = DateAdd("d", Days, CalcDate)
    CalcDate = DateAdd("d", -1, CalcDate)
   
   'Range("L7") = CalcDate

Thanks for your help


Post your answer or comment

comments powered by Disqus
Hello,

I am trying to display the result of a custom VBA function as a label on a userform. The function is called and takes arguments from three text boxes on the userform. I attached it to the exit parameter. (This may be part of the problem; I'm not sure).

There seem to be two problems, first all the text boxes don't always contain data, but because the arguments are not optional, I get a type mismatch error when the code runs (I tried switching some of them to optional, but it didn't help as you can see from my 'commented code).

The other problem is that even when all the arguments seem to be met (i.e. the text boxes all have data), I still get a compile error that the argument is not optional. This only happens if I try and pass the result to some aspect of the userform. As you can see from the last coded line in the custom function, if I assign it to a range on the worksheet, it functions fine.

Below are the codes. Any help you can provide would be appreciated. I use excel 03 and windows XP.

Note the exit procedure also contains a code that formats the text box to a specified date format, and this does work.


	VB:
	
 MSForms.ReturnBoolean) 
    Dim StartDate As Date 
    Dim EndDate As Date 
     
    If IsDate(Me.txtStartDate.Value) Then 
        StartDate = Me.txtStartDate.Value 
        Me.txtStartDate.Value = Format(StartDate, "m/d/yyyy") 
    Else:  MsgBox "Please enter a date" 
    End If 
     
    Call CalcDate(txtStartDate, txtYears, txtMonths, txtDays) 
     
    EndDate = CalcDate 
     
    lblProjectPeriod.Caption = EndDate 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and the custom function:


	VB:
	
, _ 
    Optional Days As Variant) As Date 
     
     'If IsMissing(Years) Then Years = False
     ' If Years = False Then Years = 0
     ' End If
     'End If
     'If IsMissing(Months) Then Months = False
     ' If Months = False Then Years = 0
     '   End If
     ' End If
     ' If IsMissing(Days) Then Days = False
     ' If Days = False Then Years = 0
     ' End If
     'End If
     
    CalcDate = DateAdd("yyyy", Years, StartDate) 
    CalcDate = DateAdd("m", Months, CalcDate) 
    CalcDate = DateAdd("d", Days, CalcDate) 
    CalcDate = DateAdd("d", -1, CalcDate) 
     
     'Range("L7") = CalcDate

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks for your help

My task is to create a custom VBA function to calculate the geometric mean(i know there is already a worksheet function for this but we need to make out own) with the following assumptions:

Write a custom function to calculate the geometric average of the returns listed in cells B6:B15.
Below is the formula for a geometric average:
[(1 + R1) * (1 + R2) * ... * (1 + Rt)]^(1/t) - 1
There are three requirements for your function:
i. Your function should have a single input the range of cells that contain the return series.
ii. Use a For/Next loop to cycle through the returns in the range provided by the user.
iii. Use an If/Then statement to determine if the returns are entered in decimal or percentage point form. You may assume that values above 1 are in percentage
---------------------------------------------------------------------------------------------------------------
This is my function so far and I keep getting a #NAME error. ANY HELP WOULD BE GREATLY APPRECIATED
Function MyGEO(Returns As Range)
Dim Varcount As Integer
Dim t As Integer
Dim mean As Double
Dim geomean As Double

If Returns >= 1 Then Returns = Returns / 100

Varcount = Returns.Count

mean = 1
For t = 1 To Varcount
mean = mean * (1 + Returns(t))

Next t
geomean = mean ^ (1 / Varcount) - 1
MyGEO = geomean
End Function

I have a somewhat complex formula I need to translate into custom vba function. There are four variables that I need to replace with indivdually selected cells. I would like to be able to click the cells needed, like when doing any other formula while on the sheet.

Here goes..

Variables that are taken from cells

Dir1
Dir2
Spd1
Spd2

Other variables:

Sin1, Sin2, Cos1, Cos2, Sum of Sines, Sum of Cosines, Avg of Sines, Avg of Cosines, RWD, WD

Formulas:

Sin1 = Spd1*(SIN(RADIANS(CONCATENATE(Dir1, 0))))
Sin2 = Spd2*(SIN(RADIANS(CONCATENATE(Dir2, 0))))
Cos1 = Spd1*(COS(RADIANS(CONCATENATE(Dir1, 0))))
Cos2 = Spd2*(COS(RADIANS(CONCATENATE(Dir2, 0))))

Sum of Sines = Sin1 + Sin2
Sum of Cosines = Cos1 + Cos2

Avg of Sines = (Sin1 + Sin2)/2
Avg of Cosines = (Cos1 + Cos2)/2

RWD = ATAN2(Avg of Sines, Avg of Cosines)

WD = MROUND(DEGREES(RWD), 10) Would like to Calculate WD with cell values

Stipulations:

If Sum of Sines and Sum of Cosines both = 0 Then
WD = 0

If RWD

in excel how to display results of a function and text in the same cell, for
example:
The total is

My task is to create a custom VBA function that calculates the geometric mean. I know there is a worksheet function already but this is for a VBA course. The following assumptions are:
Write a custom function to calculate the geometric average of the returns listed in cells B6:B15. Below is the formula for a geometric average:
[(1+R1 ) * (1+R2) *...*(1+Rt)]^(1/t)-1
There are three requirements for your function:
i. Your function should have a single input – the range of cells that contain the return series.
ii. Use a For/Next loop to cycle through the returns in the range provided by the user.
iii. Use an If/Then statement to determine if the returns are entered in decimal or percentage point form. You may assume that values above 1 are in percentage point form.
ANY HELP PLEASE!!!
============================================================================================================================
Function MyGEO(Returns As Range)
Dim Varcount As Integer
Dim t As Integer
Dim mean As Double
Dim geomean As Double

If Returns >= 1 Then Returns = Returns / 100

Varcount = Returns.Count

mean = 1
For t = 1 To Varcount
mean = mean * (1 + Returns(t))

Next t
geomean = mean ^ (1 / Varcount) - 1
MyGEO = geomean
End Function


I have a macro that takes values from spreadsheets sent to our office and
posts the values to a consolidated spreadsheet.
I need a value that is the result of a sum function (would look like this if
in a cell ***(c106/c92). I have to perform the function and then assign it to
a variable named iValueCMGM. The value of this variable is then pasted into
the consolidated workbook.
I tried this but it didn't work:
iValueCMGM = .Range("C106" / "C92")

Can anyone help?
Thanks!

Hi there!!! I was wondering if it's possible to use custom vba functions in pivot tables calculated fields formulas, I don't know if I was clear enough because English is not my first language, but what I need is to use a custom function I created in vba in a pivot table calculated field formula.

Thanks in advance

I have written a custom VBA function as per my requirements.

The code of function is as follows.
Function Due(Flatcost) As Single
Const Stage1 As Double = 0.1
Const Stage2 As Double = 0.46
Const Stage3 As Double = 0.69
Const Stage4 As Double = 0.87
Const Stage5 As Double = 1
Select Case Range("A1").Value
Case Is = "PLINTH"
Due = (Flatcost * Stage1)
Case Is = "RCC"
Due = (Flatcost * Stage2)
Case Is = "MESONARY"
Due = (Flatcost * Stage3)
Case Is = "FINISHING"
Due = (Flatcost * Stage4)
Case Is = "POSSESSION"
Due = (Flatcost * Stage5)
Case Else****
MsgBox "No valid stage"
End Select
End Function

I used to write the FLATCOST in cell “B4”
And enter the Formula =Due(B4) in cell “C4”
It works well, but as I change the Value of cell “A1”, the Value of cell “C4” containing the Formula doesn’t change!....... To get the change (Refresh) I have to edit it (pressing F2 or Double Click the cell “C4”)
What is the matter ?
In option the Calculation is set to Automatic.!
Please Help.

I have developed a custom VBA function (foo). In a cell on the worksheet, I
add
=Foo() . When Foo begins to run, I want to know what is the activeCell.

Greetings! I'm a newer VBA programmer, and don't understand why the results of my VBA function don't match the result the same equation in a cell. Any thoughts?

My equation: =(E8/((1/CD8)*(CI8-(D8^2)*CF8)^0.5))

My function, where:
B = CD8
P = D8
D = E8
BSE2 = CI8
ASE2 = CF8

Function Zscore (B, P, D, BSE2, ASE2)
Zscore = D / ((1 / B) * (BSE2 - (P ^ 2) * ASE2) ^ 0.5)
End Function

Hello,

How do you use the result of the "Address" function as ranges for other formulas?

I have 2 cells A1 and A2, each containing an "Address" function to find the start cell and end cell of a range.

A1 shows the result "$J$6" and A2 "$AB$6". These are working fine:

A1: =ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$2,pivot03!$2:$2,1,FALSE),$2:$2,0),1)

A2: =ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$5,pivot03!$2:$2,1,FALSE),$2:$2,0),1)

I want to get the average of this range using cells A1 and A2, but Excel treats the formula as an error:
=Average(A1:A2) shows the error #DIV/0!

Going back one step and putting the formulas within cells A1 and A2 into the average formula doesn't work at all with a colon:
=Average(ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$2,pivot03!$2:$2,1,FALSE),$2:$2,0),1):ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$5,pivot03!$2:$2,1,FALSE),$2:$2,0),1)

and with a comma shows the error #VALUE!:
=Average(ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$2,pivot03!$2:$2,1,FALSE),$2:$2,0),1),ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$5,pivot03!$2:$2,1,FALSE),$2:$2,0),1)

Any ideas?

Thanks

Hi,

Can someone explain me how can I go through all the results of a find function (by code, in a cycle) if they are not in the same column or in the same row? If I have a lot of tables with a diferent number of rows placed side by side, how would I check all the results of a find function in this sheet?

Regards,

I have written an Automation Add-In in C# (2005). On my spreadhseet, I
have the result of one volatile function as one of the input params for
another volatile function (i.e. MyFunc1 is in cell B2, and B2 is an
input param to MyFunc2). The behavior I have noticed is that when I hit
F9, The 2nd Function (MyFunc2) tries to execute first and fails since
the 1st function (MyFunc1) has not yet been calced and only then does
Excel calc the 1st function followed by the 2nd function giving the
correct result. This is dissapointing since Excel knows that one is fed
to the other and should know to calc that one first. The consequence is
that Excel makes an expensive call into the Add-In. It begins to
evaluate the second function and only when it hits a line of code in C#
which tests if the input is valid does it throw a .NET exception which
is expensive. Is there some way to teach Excel the priority, or to
force it to be smarter?

Hi Guys,
I am new to this forum and I need some help. I appreciate if someone would render me some help regarding my excel application. I need help in 2 areas:

1) I had an Excel Sheet 1 with cell A1 containing 4 number eg. 1234. And In sheet 2 in the same workbook I have a column of 23 datas (they are 4 numbers data (5678,4532,...) in each cell from A1,A2,A3 till A23).

- I need an application to compare sheet1 cell A1 data (1234) with the data in sheet 2. If 1234 or 1234 permutation (eg 3421) is found, display the result (3421) in sheet 1 cell B1.
-If no permutation of 1234 is found, search the 23 datas again for results matching any 3digits (for eg if the list contains 2435 (3 digit the same,1 different then this should also be displayed in the next available row, in cell C1 of sheet 1 and so on)) ( eg if lists have 9423 then 9423 should also be displayed on the next available cell, D1,..)

2) Secondly, I would need help to copy some external data from websites eg www.abc.com/3011. I would need to create a textbox (for user to type the web address) and 2 buttons (1 for copying the data in the website listed on the chatbox and another to copy the next data) The web address (eg www.abc.com/3011) is to be keyed into the text box and when i click on button 1, the data from the web www.abc.com/3011 will be copied into the excel sheet and then when i click on the 2nd button, www.abc.com/3012 (previously is /3011 and when i click on button 2 will copy /3012 and click again will copy /3013, always incremental of 1 when i click button 2)will be copied into my excel program.

Any kind soul please help. If you had a better idea to implement what i am doing please also share with me please. Thank you for reading!

Hello all

I need some help with excel 2007 please.

How do I assign the result of the countif function to a variable in VBA instead of showing the result in a cell?

something like

loopcounter = "=COUNTIF(C[-6],""?*"")"

cheers

I have a formula that is working for the question specified earlier which is stated below

Question: i am designing a model that can be used to calculate payment/usage patterns of customer.
Each customer has a unigue ID and has access to credit facility which runs a monthly cycle.

Payment: If the value on the preceding cell is less than the current cell then a payment has been made.
Usage: If the value in the preceding cell is greater than current cell then usage has occurred.

Please values in a cell could be negative or positive.

Negative implies customer is using credit facility while positive means using his own resource(payment made is more than credit owed).

I need a macro/formula that can be used to calculate both payment and usage over a monthly cycle for each unigue customer.

Here is the formula i used =IF(AND((Data!B20)),Data!C2-Data!B2,0) I need a macro script that can be used to design an interface/form that will link with specific sheets in the workbook.

I need a macro script that can be used to display the result in a userform such that the unigue ID can be picked and the start and end days can be specified from a dropdown menu or any other form feature after which the usage or payment is calculated and displayed.

Thanks

Hello,

I need help rounding the result of my function. I'd like to round to the nearest 0.1. The cell that I'm entering the function into is E16. Can anyone please help? Here is my current function:

=IFERROR(VLOOKUP(B16,PriceListMM,6,FALSE)*D16,"")

Thank you!! I'm desperate!

I seem to being having trouble copying the results I obtain from the Kickbutt VBA function to a new sheet. The code I have used should work but it gives me a runtime error '1004' The information cannot be pasted because the COpy area and the paste area are not the same size and shape. try one of the following: Click a single cell, and then paste. Or select a rectangle that's the same size an shape, and then paste.

Below is the code I have used. The results of the function call return an unknown amount of rows with 10 columns.


	VB:
	
 
     
    mon = month(DateValue("1-" & Target.Value & "-2000")) 
     
    Call Find_Range(mon, Sheets(6).Columns("A")).EntireRow.Copy(Sheets(3).Range("F10")) 
     
Else: Exit Sub 
     
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As you can see the destination I use is only one cell so it should paste with no problem but it doesn't. The code runs fine when I paste to any row in column A but once I paste to a different column it bugs out. I don't understand why it is creating these errors.

Hi,

I'm trying to simplify some of my VBA function codes. For example:
Function func1(x,y)

zzz = x + y

func1 = zzz * 2

End Function

Function func2(x,y)

zzz = x+y

func2 = zzz * 3

End Function
Is it possible to not keep typing in the "zzz = x+y" line in each function that needs the "zzz" value?

Thanks,

Jay

I have column A that has a quantity in it. Column B has a nested function that gives a resulting number.

I have attempted multiple functions trying to sum column B. From research it appears the result is a number stored as a text. The formula in column B is: =IF(F9

Hi. I've run into what I thought would be a simple problem but has turned into a really big one. Let me explain.

What I need to do:
I want to display a number, with it's appropriate currency symbol, from a table into a different cell on a different sheet.

My problem:
a =vlookup function only returns the number and it loses any currency formatting.

What I need help with: Either of the following.

1. A away to make =vlookup return the value as well as the currency formatting, or

2. A way to extract the currency symbol (which is part of the format) into a new cell so I can then combine it with the values of the cells and esentially turn
$105.22 (with a number Value of 105.22) into
$105.22 (with a text Value of $105.22).

Any help would be greatly appreciated as I am completely stummped.
Thank you.

Hi All,

I want to read the result of COUNTA(A:A) into a variable (Say MyCounta). At the moment, I'm making the computation on the sheet itself. That is, Cell B5 contains the formula =COUNTA(A:A). I then assign the value of cell B5 to the variable by setting

MyCounta = Range("B5").Value

Is there a way to avoid cell B5 and make the assignment more directly?

(I'm interested in a general method for assigning the value of a worksheet function to a variable rather than a specific solution to the COUNTA example given above.)

Thanks,

m

OFFSET function refers to one range and give results to another. I always find it difficult to see explicitly which cells are involved in the result of this function.
I would appreciate a simple- practical or even visual way to see those cells that are "covered" as the result of OFFSET setting function.

I got a problem using Cell range as a variable in a VBA function

Code shows like this:

-------------------------------------------------------------------------------
Function AssignData(myRange As Range) As Double()
Dim ColNums As Integer, RowNums As Integer
ColNums = 1
RowNums = 1
If IsArray(myRange.Cells.Value) Then
ColNums = myRange.Columns.Count
RowNums = myRange.Rows.Count
End If

Dim tmp() As Double
ReDim tmp(1 To RowNums, 1 To ColNums)
Dim ColIdx As Integer, RowIdx As Integer
For RowIdx = 1 To RowNums
For ColIdx = 1 To ColNums
tmp(RowIdx, ColIdx) = myRange.Cells(RowIdx, ColIdx).Value
tmp(RowIdx, ColIdx) = myRange.Cells(RowIdx, ColIdx).Value
Next ColIdx
Next RowIdx
AssignData = tmp
End Function
-------------------------------------------------------------------------------

If the cells contain pure numerical numbers in selected cell range, it works fine. Problems come out when the data in selected cell range are obtained from formulas: Data won't assign to the array correctly during debugging.

It is really funny thing. If you use another subroutine to extract one element from this array and show it in a cell, it always give you the correct data. But if you watch the variable during debugging, it may give you zero for the same element.

for example, I want to store the cell range "B1:D25" in an array variable MyArray. If you output the element (20,3) in this array, you get it correctly. However, during debugging, the corresponding data may not assign to MyArray(20,3) when you watch it.

So I don't know what the problem is. Please help how i can pass the values in the cell range exactly to the array variable. Thanks


No luck finding an answer? You could always try Google.